DATETIME FORMATE（时间格式）

青旅半醒 2022-01-10 01:11 307阅读 0赞

来源于Oracle官方文档，为了工作中方便使用，这里截取下来供参考：

[http://docs.oracle.com/cd/E11882\_01/server.112/e41084/sql\_elements004.htm\#i34924][http_docs.oracle.com_cd_E11882_01_server.112_e41084_sql_elements004.htm_i34924]

Table 3-15 Datetime Format Elements

<table title="Datetime Format Elements"> 
 <thead> 
 <tr> 
 <th>Element</th> 
 <th>TO_* datetime functions?</th> 
 <th>Description</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <th><pre>-
/
,
.
;
:
"text"</pre></th> 
 <td>Yes</td> 
 <td>Punctuation and quoted text is reproduced in the result.</td> 
 </tr> 
 <tr> 
 <th><pre>AD
A.D.</pre></th> 
 <td>Yes</td> 
 <td>AD indicator with or without periods.</td> 
 </tr> 
 <tr> 
 <th><pre>AM
A.M.</pre></th> 
 <td>Yes</td> 
 <td>Meridian indicator with or without periods.</td> 
 </tr> 
 <tr> 
 <th><pre>BC
B.C.</pre></th> 
 <td>Yes</td> 
 <td>BC indicator with or without periods.</td> 
 </tr> 
 <tr> 
 <th><pre>CC
SCC</pre></th> 
 <td></td> 
 <td>Century. 
 <ul> 
 <li>If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.</li> 
 <li>If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.</li> 
 </ul>For example, 2002 returns 21; 2000 returns 20.</td> 
 </tr> 
 <tr> 
 <th><pre>D</pre></th> 
 <td>Yes</td> 
 <td>Day of week (1-7). This element depends on the NLS territory of the session.</td> 
 </tr> 
 <tr> 
 <th><pre>DAY</pre></th> 
 <td>Yes</td> 
 <td>Name of day.</td> 
 </tr> 
 <tr> 
 <th><pre>DD</pre></th> 
 <td>Yes</td> 
 <td>Day of month (1-31).</td> 
 </tr> 
 <tr> 
 <th><pre>DDD</pre></th> 
 <td>Yes</td> 
 <td>Day of year (1-366).</td> 
 </tr> 
 <tr> 
 <th><pre>DL</pre></th> 
 <td>Yes</td> 
 <td>Returns a value in the long date format, which is an extension of the Oracle Database <code>DATE</code> format, determined by the current value of the <code>NLS_DATE_FORMAT</code> parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the <code>NLS_TERRITORY</code> and <code>NLS_LANGUAGE</code> parameters. For example, in the <code>AMERICAN_AMERICA</code> locale, this is equivalent to specifying the format <code>'fmDay,</code> <code>Month</code> <code>dd,</code> <code>yyyy'</code>. In the <code>GERMAN_GERMANY</code> locale, it is equivalent to specifying the format '<code>fmDay, dd.</code> <code>Month yyyy</code>'.Restriction: You can specify this format only with the <code>TS</code> element, separated by white space.</td> 
 </tr> 
 <tr> 
 <th><pre>DS</pre></th> 
 <td>Yes</td> 
 <td>Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the <code>NLS_TERRITORY</code> and <code>NLS_LANGUAGE</code> parameters. For example, in the <code>AMERICAN_AMERICA</code> locale, this is equivalent to specifying the format '<code>MM/DD/RRRR</code>'. In the <code>ENGLISH_UNITED_KINGDOM</code> locale, it is equivalent to specifying the format '<code>DD/MM/RRRR</code>'.Restriction: You can specify this format only with the <code>TS</code> element, separated by white space.</td> 
 </tr> 
 <tr> 
 <th><pre>DY</pre></th> 
 <td>Yes</td> 
 <td>Abbreviated name of day.</td> 
 </tr> 
 <tr> 
 <th><pre>E</pre></th> 
 <td>Yes</td> 
 <td>Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).</td> 
 </tr> 
 <tr> 
 <th><pre>EE</pre></th> 
 <td>Yes</td> 
 <td>Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).</td> 
 </tr> 
 <tr> 
 <th><pre>FF&nbsp;[1..9]</pre></th> 
 <td>Yes</td> 
 <td>Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in <code>DATE</code> formats.Examples: <code>'HH:MI:SS.FF'</code><code>SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;</code></td> 
 </tr> 
 <tr> 
 <th><pre>FM</pre></th> 
 <td>Yes</td> 
 <td>Returns a value with no leading or trailing blanks.See Also: Additional discussion on this <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00216" rel="nofollow">format model modifier</a> in the <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF30003" rel="nofollow">Oracle Database SQL Language Reference</a></td> 
 </tr> 
 <tr> 
 <th><pre>FX</pre></th> 
 <td>Yes</td> 
 <td>Requires exact matching between the character data and the format model.See Also: Additional discussion on this <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00216" rel="nofollow">format model modifier</a> in the <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF30004" rel="nofollow">Oracle Database SQL Language Reference</a></td> 
 </tr> 
 <tr> 
 <th><pre>HH
HH12</pre></th> 
 <td>Yes</td> 
 <td>Hour of day (1-12).</td> 
 </tr> 
 <tr> 
 <th><pre>HH24</pre></th> 
 <td>Yes</td> 
 <td>Hour of day (0-23).</td> 
 </tr> 
 <tr> 
 <th><pre>IW</pre></th> 
 <td></td> 
 <td>Week of year (1-52 or 1-53) based on the ISO standard.</td> 
 </tr> 
 <tr> 
 <th><pre>IYY
IY
I</pre></th> 
 <td></td> 
 <td>Last 3, 2, or 1 digit(s) of ISO year.</td> 
 </tr> 
 <tr> 
 <th><pre>IYYY</pre></th> 
 <td></td> 
 <td>4-digit year based on the ISO standard.</td> 
 </tr> 
 <tr> 
 <th><pre>J</pre></th> 
 <td>Yes</td> 
 <td>Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.</td> 
 </tr> 
 <tr> 
 <th><pre>MI</pre></th> 
 <td>Yes</td> 
 <td>Minute (0-59).</td> 
 </tr> 
 <tr> 
 <th><pre>MM</pre></th> 
 <td>Yes</td> 
 <td>Month (01-12; January = 01).</td> 
 </tr> 
 <tr> 
 <th><pre>MON</pre></th> 
 <td>Yes</td> 
 <td>Abbreviated name of month.</td> 
 </tr> 
 <tr> 
 <th><pre>MONTH</pre></th> 
 <td>Yes</td> 
 <td>Name of month.</td> 
 </tr> 
 <tr> 
 <th><pre>PM
P.M.</pre></th> 
 <td>Yes</td> 
 <td>Meridian indicator with or without periods.</td> 
 </tr> 
 <tr> 
 <th><pre>Q</pre></th> 
 <td></td> 
 <td>Quarter of year (1, 2, 3, 4; January - March = 1).</td> 
 </tr> 
 <tr> 
 <th><pre>RM</pre></th> 
 <td>Yes</td> 
 <td>Roman numeral month (I-XII; January = I).</td> 
 </tr> 
 <tr> 
 <th><pre>RR</pre></th> 
 <td>Yes</td> 
 <td>Lets you store 20th century dates in the 21st century using only two digits.See Also: <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#i116004" rel="nofollow">"The RR Datetime Format Element"</a></td> 
 </tr> 
 <tr> 
 <th><pre>RRRR</pre></th> 
 <td>Yes</td> 
 <td>Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.</td> 
 </tr> 
 <tr> 
 <th><pre>SS</pre></th> 
 <td>Yes</td> 
 <td>Second (0-59).</td> 
 </tr> 
 <tr> 
 <th><pre>SSSSS</pre></th> 
 <td>Yes</td> 
 <td>Seconds past midnight (0-86399).</td> 
 </tr> 
 <tr> 
 <th><pre>TS</pre></th> 
 <td>Yes</td> 
 <td>Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the <code>NLS_TERRITORY</code> and <code>NLS_LANGUAGE</code> initialization parameters.Restriction: You can specify this format only with the <code>DL</code> or <code>DS</code> element, separated by white space.</td> 
 </tr> 
 <tr> 
 <th><pre>TZD</pre></th> 
 <td>Yes</td> 
 <td>Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in <code>DATE</code> formats.Example: <code>PST</code> (for US/Pacific standard time); <code>PDT</code> (for US/Pacific daylight time).</td> 
 </tr> 
 <tr> 
 <th><pre>TZH</pre></th> 
 <td>Yes</td> 
 <td>Time zone hour. (See <code>TZM</code> format element.) Valid in timestamp and interval formats, but not in <code>DATE</code> formats.Example: <code>'HH:MI:SS.FFTZH:TZM'</code>.</td> 
 </tr> 
 <tr> 
 <th><pre>TZM</pre></th> 
 <td>Yes</td> 
 <td>Time zone minute. (See <code>TZH</code> format element.) Valid in timestamp and interval formats, but not in <code>DATE</code> formats.Example: <code>'HH:MI:SS.FFTZH:TZM'</code>.</td> 
 </tr> 
 <tr> 
 <th><pre>TZR</pre></th> 
 <td>Yes</td> 
 <td>Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in <code>DATE</code> formats.Example: US/Pacific</td> 
 </tr> 
 <tr> 
 <th><pre>WW</pre></th> 
 <td></td> 
 <td>Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.</td> 
 </tr> 
 <tr> 
 <th><pre>W</pre></th> 
 <td></td> 
 <td>Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.</td> 
 </tr> 
 <tr> 
 <th><pre>X</pre></th> 
 <td>Yes</td> 
 <td>Local radix character.Example: <code>'HH:MI:SSXFF'</code>.</td> 
 </tr> 
 <tr> 
 <th><pre>Y,YYY</pre></th> 
 <td>Yes</td> 
 <td>Year with comma in this position.</td> 
 </tr> 
 <tr> 
 <th><pre>YEAR
SYEAR</pre></th> 
 <td></td> 
 <td>Year, spelled out; <code>S</code> prefixes BC dates with a minus sign (-).</td> 
 </tr> 
 <tr> 
 <th><pre>YYYY
SYYYY</pre></th> 
 <td>Yes</td> 
 <td>4-digit year; <code>S</code> prefixes BC dates with a minus sign.</td> 
 </tr> 
 <tr> 
 <th><pre>YYY
YY
Y</pre></th> 
 <td>Yes</td> 
 <td>Last 3, 2, or 1 digit(s) of year.</td> 
 </tr> 
 </tbody> 
</table>

我们来逐一测试下:

1、诸如- / , . ; : "text"  这类符号表达字符可以在结果中重现

SQL> select to_char(sysdate,'yyyy"year"mm-dd;') from dual;
    TO_CHAR(SYSDATE,'YYYY"YEAR"MM-DD;')
    ------------------------------------------
    2015year02-24;

2、年的显示，SYEAR/YEAR字符显示年，SYYYY/YYYY显示公元数值年，Y,YYY 以逗号分隔的年显示方式，IYYY/IYY/IY/I显示年后几位数值，RR/RRRR数值显示年， B.C.公元前, A.D.公元 SCC/CC 世纪，如果年的后两个数值是01~99，则世纪值=年的前两个值加一，否则与年前两个值相同。AM(A.M.)/PM(P.M.)显示上午还是下午

SQL> select to_char(sysdate,'SYEAR,YEAR') from dual;
    TO_CHAR(SYSDATE,'SYEAR,YEAR')
    --------------------------------------------------------------------------------
     TWENTY FIFTEEN,TWENTY FIFTEEN
    SQL> select to_char(sysdate,'SYYYY,YYYY,Y,YYY,IYYY,IYY,IY,I:RR,RRRR:B.C.,A.D.,SCC,CC,AM,P.M.') from dual;
    TO_CHAR(SYSDATE,'SYYYY,YYYY,Y,YYY,IYYY,IYY,IY,I:RR,RRRR:B.C.,A.D.,SCC,CC,AM,P.M.
    --------------------------------------------------------------------------------
     2015,2015,2,015,2015,015,15,5:15,2015:A.D.,A.D., 21,21,PM,P.M.

3、月的显示, MM数值显示月份，MON字符显示月份，MON完整字符显示月份，RM罗马字母显示月份。

SQL> select to_char(sysdate,'MM,MON,MONTH,RM') from dual;
    TO_CHAR(SYSDATE,'MM,MON,MONTH,RM')
    --------------------------------------------------------------------------------
    02,FEB,FEBRUARY ,II

4、天的显示，D  一周中的天数，DAY 天的名称，DD 月中的天数，DDD表示年中的天数，DY以缩写的星期显示。

SQL> select to_char(sysdate,'D,DAY,DD,DDD,DY') from dual;
    TO_CHAR(SYSDATE,'D,DAY,DD,DDD,DY')
    --------------------------------------------------------------------------------
    3,TUESDAY  ,24,055,TUE

5、周显示，IW年中的周(1-53),WW年中的周以当年的第一天算第一周，W月中的周，以第每个月头一天算第一周。

SQL> select to_char(sysdate,'IW,WW,W') from dual;
    TO_CHAR(SYSDATE,'IW,W
    ---------------------
    09,08,4

6、时间格式 DL以长时间格式显示，DS以短时间格式显示，这两者取决于NLS\_TERRITORY和NLS\_LANGUAGE两个参数配置，不同地区显示的格式不一样。E,EE分别以缩写和全称的方式显示时代。

SQL> select to_char(sysdate,'DL:DS') from dual;
    TO_CHAR(SYSDATE,'DL:DS')
    --------------------------------------------------------------------------------
    Tuesday, February 24, 2015:2/24/2015

7、小时 HH/HH12以12小时显示，HH24以24小时显示。

SQL> select to_char(SYSTIMESTAMP,'HH,HH12,HH24') from dual;
    TO_CHAR(SYSTIMESTAMP,'HH
    ------------------------
    01,01,13

8、分钟MI

SQL> select to_char(systimestamp,'MI') from dual;
    TO_CHA
    ------
    13

9、秒数 SS显示秒数，FF显示毫秒精确度\[1~9\]，FM让返回的值没有前后空格，FX精确匹配日期格式类型。

SQL> select to_char(SYSTIMESTAMP,'SS.FF3,SSSSS') from dual;
    TO_CHAR(SYSTIMESTAMP,'SS.FF3,SSSSS')
    ------------------------------------------------------
    58.180,49438

10、其他 TS短格式显示时间，TZD白天即使，TZH区域小时，TZM区域分钟，TZR时间区域信息，X小数点，Q季度。

SQL> select to_char(systimestamp,'TS,TZD,TZH,TZM,TZR') from dual;
    TO_CHAR(SYSTIMESTAMP,'TS,TZD,TZH,TZM,TZR')
    --------------------------------------------------------------------------------
    2:09:27 PM,,+08,00,+08:00
    SQL> select to_char(systimestamp,'XQ') from dual;
    TO_CHA
    ------
    .1

11、计算时间差

年份差

select floor(to_number(sysdate-to_date('01-31-1999','MM-DD-YYYY'))/365) as "YEARS" FROM DUAL;

月份差

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 
    select ceil(months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')))"MONTHS" FROM DUAL;

天数差(时\*24，分24\*60，以此类推)

select floor(to_number(sysdate-to_date('01-31-1999','MM-DD-YYYY'))) as "YEARS" FROM DUAL;

通过EXTRACT()获取精准的时间

SQL> SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 2:38:40') from dual;
    EXTRACT(MINUTEFROMTIMESTAMP'2001-02-162:38:40')
    -----------------------------------------------
                                                 38

通过NEXT\_DAY()函数获取未来时间，这里1-7分别表示从日、一、二、三、四、五、六

SQL> select sysdate,next_day(sysdate,6) from dual;
    SYSDATE             NEXT_DAY(SYSDATE,6)
    ------------------- -------------------
    2015-03-09 12:44:02 2015-03-13 12:44:02

查看有关时间的环境参数设置：

SQL> col PARAMETER for a30
    SQL> col VALUE for a40
    SQL> select * from nls_session_parameters;
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE

转载于:https://blog.51cto.com/onlinekof2001/1614947

[http_docs.oracle.com_cd_E11882_01_server.112_e41084_sql_elements004.htm_i34924]: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#i34924