Oracle 11g Release 1 (11.1) 单行函数日期函数

Oracle 11g Release 1 (11.1) 单行函数——日期函数 ,有需要的朋友可参考一下

Oracle 11g Release 1 (11.1) 单行函数——日期函数 ,有需要的朋友可参考一下

日期函数
日期函数操作日期值（DATE 类型的字段），时间戳值（TIMESTAMP、TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 类型的字段）和间隔值（INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH）。

大多数日期函数，如 ADD_MONTHS、CURRENT_DATE、LAST_DAY、NEW_TIME 和 NEXT_DAY，是为了 Oracle DATE 类型设计。若你提供一个时间戳值作为它们的参数，则 Oracle 数据库在内部把输入类型转换成一个 DATE 值，并返回。当返回一个 number 值时，MONTHS_BETWEEN 函数会抛出异常；ROUND 和 TRUNC 函数不接受时间戳或间隔值。

剩下的日期函数是为了接受三种日期类型（如 date、timestamp 和 interval）设计的，并返回这些类型的一个值。

返回系统日期信息的所有日期函数，如 SYSDATE、SYSTIMESTAMP、CURRENT_TIMESTAMP 等等，对于每个SQL 语句，不管这些语句被引用多少次，都会重新计算一次。

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT (datetime)

FROM_TZ

LAST_DAY

LOCALTIMESTAMP

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND (date)

SESSIONTIMEZONE

SYS_EXTRACT_UTC

SYSDATE

SYSTIMESTAMP

TO_CHAR (datetime)

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_DSINTERVAL

TO_YMINTERVAL

TRUNC (date)

TZ_OFFSET

 

ADD_MONTHS(date,integer)

--------------------------------------------------------------------------------

ADD_MONTHS 返回日期 date 加整数月 integer 后的日期。月是由会话参数 NLS_CALENDAR 定义的。

date 参数可以是日期值，或是任何可以隐式转换成 DATE 类型的值。integer 参数可以是整数，或是任何可以隐式转换成整数的值。不管参数 date 的类型如何，返回类型总是 DATE。

若 date 是月的最后一天，或结果月的天数比 date 少，则结果的天是结果月的最后一天。否则，与 date 的天相同。

示例 1：演示 employees 表 hire_date 字段加一个月。

SQL> SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') "Current month",  2         TO_CHAR(ADD_MONTHS(hire_date, 1), 'YYYY-MM-DD') "Next month",  3         TO_CHAR(ADD_MONTHS(TO_DATE('2012-2-29', 'YYYY-MM-DD'), 1),  4                 'YYYY-MM-DD') "Fewer Days"  5    FROM employees  6   WHERE last_name = 'Baer'; Current month Next month Fewer Days------------- ---------- ----------1994-06-07    1994-07-07 2012-03-31 SQL> 

CURRENT_DATE

--------------------------------------------------------------------------------

CURRENT_DATE 返回会话时区的当前日期，值为 DATE 类型阳历（Gregorian）日期值。

示例 2：演示 CURRENT_DATE。

SQL> SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; Session alteredSQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Session alteredSQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE      CURRENT_DATE-------------------- ---------------------05:00               2012-7-31 8:22:24SQL> ALTER SESSION SET TIME_ZONE = '-8:0'; Session alteredSQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE      CURRENT_DATE-------------------- ---------------------08:00               2012-7-31 5:22:24 SQL> 

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)

--------------------------------------------------------------------------------

CURRENT_TIMESTAMP 返回会话时区的当前日期和时间，值为 TIMESTAMP WITH TIME ZONE 类型。时区偏移反应 SQL 会话的当前的本地时间。若省略 precision，则默认为 6。该函数与 LOCALTIMESTAMP 的不同是，CURRENT_TIMESTAMP 返回 TIMESTAMP WITH TIME ZONE 值，而 LOCALTIMESTAMP 则返回 TIMESTAMP 值。

可选的参数 precision 指定了返回时间值的小数秒精度。

示例 3：演示 CURRENT_TIMESTAMP 对会话时区敏感。

SQL> ALTER SESSION SET TIME_ZONE = '-5:0'; Session alteredSQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Session alteredSQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE      CURRENT_TIMESTAMP-------------------- -----------------------------------------05:00               31-7月 -12 09.17.58.046000 上午 -05:00 SQL> SQL> ALTER SESSION SET TIME_ZONE = '-8:0'; Session alteredSQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE      CURRENT_TIMESTAMP-------------------- -----------------------------------------08:00               31-7月 -12 06.21.11.500000 上午 -08:00 SQL> 当使用带格式掩码的 CURRENT_TIMESTAMP，注意格式掩码匹配函数的返回值。例如，创建下表：

CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);下面 INSERT 语句会失败，因为掩码没有包含 CURRENT_TIMESTAP 函数返回类型的 TIME ZONE 部分：

INSERT INTO current_testVALUES  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));下面语句使用了正确的格式掩码：

INSERT INTO current_testVALUES  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));

DBTIMEZONE

--------------------------------------------------------------------------------

DBTIMEZONE 返回数据库时区值。返回类型是时区偏移（字符类型，格式为 '[+|-]TZH:TZM'），或时区区域名称，这依赖于用户在最近 CREATE DATABASE 或 ALTER DATABASE 语句指定数据库时区值。

示例 4：演示 DBTIMEZONE。假设数据库时区设置为 UTC 时区。

SQL> SELECT DBTIMEZONE FROM DUAL; DBTIME------+00:00 SQL>

EXTRACT (datetime)

--------------------------------------------------------------------------------

EXTRACT 从一个日期或时间间隔表达式提取并返回指定日期值。

示例 5：演示从指定日期提取年、月、日。

SQL> SELECT EXTRACT(YEAR FROM DATE '2012-07-31') "EXTRACT YEAR",  2         EXTRACT(MONTH FROM DATE '2012-07-31') "EXTRACT MONTH",  3         EXTRACT(DAY FROM DATE '2012-07-31') "EXTRACT DAY"  4    FROM DUAL; EXTRACT YEAR EXTRACT MONTH EXTRACT DAY------------ ------------- -----------        2012             7          31 SQL> 示例 6：演示每个月入职的员工数量。其实，这个演示用 Oracle OE Schema 下的 orders 订单表较好，可惜我没有这个表。

SQL> SELECT EXTRACT(month FROM hire_date) "Month",  2         COUNT(hire_date) "No. of Hire"  3    FROM employees  4   GROUP BY EXTRACT(month FROM hire_date)  5   ORDER BY "No. of Hire" DESC;      Month No. of Hire---------- -----------         3          17         1          14         2          13         6          11         8           9         4           7        12           7         7           7         5           6        10           6         9           5        11           5 12 rows selected SQL> 

FROM_TZ(timestamp_value,time_zone_value)

--------------------------------------------------------------------------------

FROM_TZ 把时间戳值和时区值转换成 TIMESTAMP WITH TIME ZONE 值。

time_zone_value 是格式为 'TZH:TZM' 的字符串，或返回 TZR 格式字符串的字符表达式，TZD 格式可选。

示例 7：演示返回一个 TIMESTAMP WITH TIME ZONE  的时间戳值。

SQL> SELECT FROM_TZ(TIMESTAMP '2012-07-31 08:00:00', '3:00') FROM DUAL; FROM_TZ(TIMESTAMP'2012-07-3108--------------------------------------------------------------------------------31-7月 -12 08.00.00.000000000 上午 +03:00 SQL> 

LAST_DAY(date)

--------------------------------------------------------------------------------

LAST_DAY 返回月的最后一天的日期。月的最后一天是由会话参数 NLS_CALENDAR 定义的。返回类型总是 DATE，而不是参数 date 的数据类型。

示例 8：演示当前月剩多少天。

SQL> SELECT SYSDATE,  2         LAST_DAY(SYSDATE) "Last",  3         LAST_DAY(SYSDATE) - SYSDATE "Days Left"  4    FROM DUAL; SYSDATE     Last         Days Left----------- ----------- ----------2012-8-1 22 2012-8-31 2         30 SQL> 

LOCALTIMESTAMP
LOCALTIMESTAMP(timestamp_precision)

--------------------------------------------------------------------------------

LOCALTIMESTAMP 返回会话时区中当前的日期和时间，类型为 TIMESTAMP。LOCALTIMESTAMP 返回 TIMESTAMP 值，而 CURRENT_TIMESTAMP 返回 TIMESTAMP WITH TIME ZONE 值。

参数 timestamp_precision 可选，指定返回小数秒精度的时间值。

示例 9：演示 LOCALTIMESTAMP 和 CURRENT_TIMESTAMP 之间的区别。

SQL> ALTER SESSION SET TIME_ZONE = '-5:00'; Session alteredSQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP                        LOCALTIMESTAMP---------------------------------------- ----------------------------------------01-8月 -12 10.06.21.203000 上午 -05:00   01-8月 -12 10.06.21.203000 上午 SQL> SQL> ALTER SESSION SET TIME_ZONE = '-8:00'; Session alteredSQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP                        LOCALTIMESTAMP---------------------------------------- ----------------------------------------01-8月 -12 07.06.27.281000 上午 -08:00   01-8月 -12 07.06.27.281000 上午 SQL> 当使用带格式掩码的 LOCALTIMESTAMP 时，注意格式掩码匹配函数的返回值。例如，创建下表：

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);下面 INSERT 语句会失败，因为掩码没有包含 LOCALTIMESTAMP 函数返回类型的 TIME ZONE 部分：

INSERT INTO local_testVALUES  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));下面语句使用了正确的格式掩码：

INSERT INTO local_testVALUES  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

MONTHS_BETWEEN(date1,date2)

--------------------------------------------------------------------------------

MONTHS_BETWEEN 返回日期 date1 和 date2 之间有多少月。月是由会话参数 NLS_CALENDAR 定义。若 date1 比 date2 晚，则结果为正值。若 date1 比 date2 早，则结果为负值。若 date1 和 date2 日期相同，或都是月的最后一天，则结果总是整数。否则，Oracle 数据库基于每月31天计算小数部分，考虑 date1 和 date2 的时间差。

示例 10：演示计算两个日期之间有多少月。

SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995', 'MM-DD-YYYY'),  2                        TO_DATE('01-01-1995', 'MM-DD-YYYY')) "Months"  3    FROM DUAL;     Months----------1.03225806 SQL> 

NEW_TIME(date,timezone1,timezone2)

--------------------------------------------------------------------------------

NEW_TIME 日期和时间的时区转换。使用该函数前，你必须设置回话参数 NLS_DATE_FORMAT 为24小时制，返回类型始终为 DATE，而不是参数 date 的类型。

备注：

This function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining the FROM_TZ function and the datetime expression.

参数 timezone1 和 timezone2 可以是下面任意文本：

•AST, ADT: 大西洋标准或夏令时（Atlantic Standard or Daylight Time）
•BST, BDT: 白令海标准或夏令时（Bering Standard or Daylight Time）
•CST, CDT: 中央标准或夏令时（Central Standard or Daylight Time）
•EST, EDT: 东部标准或夏令时（Eastern Standard or Daylight Time）
•GMT: 格林威治时间（Greenwich Mean Time）
•HST, HDT: 阿拉斯加 - 夏威夷标准时间或夏令时间（Alaska-Hawaii Standard Time or Daylight Time）
•MST, MDT: 山地标准或夏令时（Mountain Standard or Daylight Time）
•NST: 纽芬兰标准时间（Newfoundland Standard Time）
•PST, PDT: 太平洋标准或夏令时（Pacific Standard or Daylight Time）
•YST, YDT: 育空地区标准或夏令时（Yukon Standard or Daylight Time）
示例 11：演示返回等价于太平洋标准时间（Pacific Standard time ）的大西洋标准时间（Atlantic Standard time）。

SQL> ALTER SESSION SET NLS_DATE_FORMAT =  2     'YYYY-MM-DD HH24:MI:SS'; Session alteredSQL> SELECT NEW_TIME(TO_DATE('2012-8-3 01:23:45', 'YYYY-MM-DD HH24:MI:SS'),  2                  'AST',  3                  'PST') "New Date and Time"  4    FROM DUAL; New Date and Time-----------------2012-8-2 21:23:45 SQL> 

NEXT_DAY(date,char)

--------------------------------------------------------------------------------

NEXT_DAY 返回指定日期后，第一个指定星期的日期。返回类型总是 DATE。参数 char 必须是会话日期语言的周，周的全名或缩写。返回值具有与参数相同的时、分、秒。

示例 12：演示 2012 年 2 月 2 日下个星期二的日期。

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE =American; Session altered SQL> SELECT NEXT_DAY('02-FEB-2012', 'TUESDAY') "NEXT DAY" FROM DUAL; NEXT DAY-----------2012-2-7 SQL> 

NUMTODSINTERVAL(n,'interval_unit')
NUMTOYMINTERVAL(n,interval_unit)

--------------------------------------------------------------------------------

NUMTODSINTERVAL 把 n 转换成 INTERVAL DAY TO SECOND 字面义。参数 n 可以是任何 NUMBER 值或可以隐式转换成 NUMBER 值的表达式。参数 interval_unit 可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 类型。interval_unit 参数的值指定 n 的单位，必须是以下字符串：

•'DAY'
•'HOUR'
•'MINUTE'
•'SECOND'
interval_unit 参数大小写不敏感。返回值的精度默认为9。

NUMTOYMINTERVAL 把 n 转换成 INTERVAL YEAR TO MONTH 字面义。interval_unit 参数的值指定 n 的单位，必须是以下字符串：

•'YEAR'
•'MONTH'
示例 13：演示在 COUNT 函数使用 NUMTODSINTERVAL 计算，对于每个员工，由同一个经理在过去的 100 天雇用的员工数目。

SQL> SELECT manager_id,  2         last_name,  3         hire_date,  4         COUNT(*) OVER(PARTITION BY manager_id ORDER BY hire_date RANGE NUMTODSINTERVAL(100, 'day') PRECEDING) AS t_count  5    FROM employees; MANAGER_ID LAST_NAME                 HIRE_DATE      T_COUNT---------- ------------------------- ----------- ----------       100 Kochhar                   1989-9-21            1       100 De Haan                   1993-1-13            1       100 Raphaely                  1994-12-7            1       100 Kaufling                  1995-5-1             1       100 Hartstein                 1996-2-17            1       100 Weiss                     1996-7-18            1       100 Russell                   1996-10-1            2       100 Partners                  1997-1-5             2       100 Errazuriz                 1997-3-10            2       100 Fripp                     1997-4-10            3       100 Vollman                   1997-10-10           1       100 Cambrault                 1999-10-15           1       100 Mourgos                   1999-11-16           2       100 Zlotkey                   2000-1-29            2       101 Whalen                    1987-9-17            1       101 Mavris                    1994-6-7             3       101 Higgins                   1994-6-7             3       101 Baer                      1994-6-7             3       101 Greenberg                 1994-8-17            4       102 Hunold                    1990-1-3             1…… 107 rows selected SQL> 示例 14：演示在 SUM 函数使用 NUMTODSINTERVAL 计算，对每个员工，在过去一年雇用的员工的总工资数。

SQL> SELECT last_name,  2         hire_date,  3         salary,  4         SUM(salary) OVER(ORDER BY hire_date RANGE NUMTOYMINTERVAL(1, 'year') PRECEDING) AS t_sal  5    FROM employees  6   ORDER BY last_name, hire_date; LAST_NAME                 HIRE_DATE       SALARY      T_SAL------------------------- ----------- ---------- ----------Abel                      1996-5-11     11000.00      61900Ande                      2000-3-24      6400.00     106500Atkinson                  1997-10-30     2800.00     160000Austin                    1997-6-25      4800.00     134700Baer                      1994-6-7      10000.00      36800Baida                     1997-12-24     2900.00     180900Banda                     2000-4-21      6200.00     109400Bates                     1999-3-24      7300.00     104900Bell                      1996-2-4       4000.00      36300Bernstein                 1997-3-24      9500.00     132200Bissot                    1997-8-20      3300.00     145600Bloom                     1998-3-23     10000.00     147300Bull                      1997-2-20      4100.00      88000Cabrio                    1999-2-7       3000.00     104100Cambrault                 1998-12-9      7500.00     122500Cambrault                 1999-10-15    11000.00      81400Chen                      1997-9-28      8200.00     153800Chung                     1997-6-14      3800.00     133200Colmenares                1999-8-10      2500.00      76000Davies                    1997-1-29      3100.00      98400 …… 107 rows selected SQL> 

ROUND (date)
ROUND(date,fmt)
TRUNC(date)
TRUNC(date,fmt)

--------------------------------------------------------------------------------

ROUND 返回四舍五入到由格式模型 fmt 指定的日期单位。该函数对会话参数 NLS_CALENDAR 不是敏感的。它根据公历（Gregorian calendar）的规则执行。返回值总是 DATE。若省略 fmt，则日期四则五入到最近的一天。日期表达式必须被解析成 DATE 值。

示例 15：演示对日期 2012 年 10 月 27 日使用 ROUND 和 TRUNC。

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN; Session alteredSQL> SELECT ROUND(TO_DATE('27-OCT-12'), 'YEAR') as "ROUND New Year",  2         TRUNC(TO_DATE('27-OCT-12'), 'YEAR') as "TRUNC New Year"  3    FROM DUAL; ROUND New Year TRUNC New Year-------------- --------------2013-1-1       2012-1-1 SQL> 

SESSIONTIMEZONE

--------------------------------------------------------------------------------

SESSIONTIMEZONE 返回当前会话的时区。返回类型是时区偏移（格式为 '[+|]TZH:TZM'），或时区区域名称，这依赖于用户如何指定会话时区值。

示例 16：演示返回当前会话的时区。

SQL> SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE---------------------------------------------------------------------------+08:00 SQL> 

SYS_EXTRACT_UTC(datetime_with_timezone)

--------------------------------------------------------------------------------

 

SYS_EXTRACT_UTC 从一个带时区偏移或时区区域名的日期值提取 UTC（Coordinated Universal Time—formerly Greenwich Mean Time）。

示例 17：演示 SYS_EXTRACT_UTC。

SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2012-08-03 11:30:00.00 -08:00')  2     FROM DUAL; SYS_EXTRACT_UTC(TIMESTAMP'2012--------------------------------------------------------------------------------03-8月 -12 07.30.00.000000000 下午 SQL>

 

SYSDATE
SYSTIMESTAMP

--------------------------------------------------------------------------------

SYSDATE 返回当前数据库所在的操作系统的日期和时间。返回类型为 DATE，格式依赖于 NLS_DATE_FORMAT 初始化参数。该函数无任何参数。在一个分布式 SQL 语句中，该函数返回本地数据库操作系统的日期和时间。不能在一个 CHECK 约束的条件中使用该函数。

SYSTIMESTAMP 返回数据库所在操作系统的日期，包含小数秒是时区。返回类型为 TIMESTAMP WITH TIME ZONE。

示例 18：演示返回当前操作系统的日期和时间。

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "NOW" FROM DUAL; NOW-------------------2012-08-04 00:16:44 SQL> 示例 19：演示返回系统时间戳。

SQL> SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP--------------------------------------------------------------------------------04-8月 -12 12.21.48.465000 上午 +08:00 SQL> 下面代码演示如何显式指定小数秒（fractional seconds）：

SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL; TO_CHAR(SYSTIMESTAMP,'SSSSS.FF------------------------------01390.309000 SQL> 

TO_CHAR(datetime)
TO_CHAR(datetime,fmt)
TO_CHAR(datetime,fmt,'nlsparam')
TO_CHAR(interval)
TO_CHAR(interval,fmt)
TO_CHAR(interval,fmt,'nlsparam')

--------------------------------------------------------------------------------

TO_CHAR (datetime) 把类型为 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE 的日期时间或间隔时间转换成 指定格式 fmt 的日期。若省略 fmt，则日期被转换成 VARCHAR2 值：

•DATE 值被转换成默认日期格式的值
•TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 值被转换成默认时间戳格式的值
•TIMESTAMP WITH TIME ZONE 值被转换成默认带时区格式的时间戳的值
'nlsparam' 参数指定返回月、日名称和其缩写的语言。如：

'NLS_DATE_LANGUAGE = language' 若省略 'nlsparam'，则该函数使用会话中默认的日期语言。

示例 20：演示 TO_CHAR。

SQL> CREATE TABLE date_tab (  2     ts_col      TIMESTAMP,  3     tsltz_col   TIMESTAMP WITH LOCAL TIME ZONE,  4     tstz_col    TIMESTAMP WITH TIME ZONE); Table createdSQL> ALTER SESSION SET TIME_ZONE = '-8:00'; Session alteredSQL> INSERT INTO date_tab  2  VALUES  3    (TIMESTAMP'1999-12-01 10:00:00',  4     TIMESTAMP'1999-12-01 10:00:00',  5     TIMESTAMP'1999-12-01 10:00:00'); 1 row insertedSQL> INSERT INTO date_tab  2  VALUES  3    (TIMESTAMP'1999-12-02 10:00:00 -8:00',  4     TIMESTAMP'1999-12-02 10:00:00 -8:00',  5     TIMESTAMP'1999-12-02 10:00:00 -8:00'); 1 row insertedSQL> SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_date,  2         TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_date  3    FROM date_tab  4   ORDER BY ts_date, tstz_date; TS_DATE                         TSTZ_DATE------------------------------- --------------------------------------01-12ÔÂ-1999 10:00:00.000000    01-12ÔÂ-1999 10:00:00.000000 -08:0002-12ÔÂ-1999 10:00:00.000000    02-12ÔÂ-1999 10:00:00.000000 -08:00SQL> SELECT SESSIONTIMEZONE,  2         TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz  3    FROM date_tab  4   ORDER BY sessiontimezone, tsltz; SESSIONTIMEZONE TSLTZ--------------- --------------------------------08:00          01-12ÔÂ-1999 10:00:00.000000-08:00          02-12ÔÂ-1999 10:00:00.000000SQL> ALTER SESSION SET TIME_ZONE = '-5:00'; Session alteredSQL> SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col,  2         TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_col  3    FROM date_tab  4   ORDER BY ts_col, tstz_col; TS_COL                          TSTZ_COL------------------------------- --------------------------------------01-12ÔÂ-1999 10:00:00.000000    01-12ÔÂ-1999 10:00:00.000000 -08:0002-12ÔÂ-1999 10:00:00.000000    02-12ÔÂ-1999 10:00:00.000000 -08:00SQL> SELECT SESSIONTIMEZONE,  2         TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz_col  3    FROM date_tab  4   ORDER BY sessiontimezone, tsltz_col; SESSIONTIMEZONE TSLTZ_COL--------------- --------------------------------05:00          01-12ÔÂ-1999 13:00:00.000000-05:00          02-12ÔÂ-1999 13:00:00.000000 SQL> 

TO_TIMESTAMP(char)
TO_TIMESTAMP(char,fmt)
TO_TIMESTAMP(char,fmt,'nlsparam')
TO_TIMESTAMP_TZ(char)
TO_TIMESTAMP_TZ(char,fmt)
TO_TIMESTAMP_TZ(char,fmt,'nlsparam')

--------------------------------------------------------------------------------

TO_TIMESTAMP 把类型为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 的值转换成 TIMESTAMP 类型。fmt 参数指定格式。'nlsparam' 参数指定语言。

该函数不直接支持 CLOB 类型。但是，CLOB 可以作为参数隐式转换来传递。

TO_TIMESTAMP_TZ 为 TO_TIMESTAMP 相应的时区函数。

示例 21：演示把字符串转换成时间戳。字符串不是默认的 TIMESTAMP 格式，因此必须指定格式。

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; Session alteredSQL> SELECT TO_TIMESTAMP('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')  2    FROM DUAL; TO_TIMESTAMP('10-SEP-0214:10:1--------------------------------------------------------------------------------10-SEP-02 02.10.10.123000000 PM SQL> 

TO_DSINTERVAL(…)

--------------------------------------------------------------------------------

TO_DSINTERVAL 把类型为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 的值转换成 INTERVAL DAY TO SECOND 类型。TO_DSINTERVAL 接受如下两种格式的参数：

•SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
•ISO duration format compatible with the ISO 8601:2004 standard
在 SQL 格式，日是在 0 到 999999999 之间的整数，小时是在 0 到 23 之间的整数，分和秒在 0 到 59 之间的整数。小数秒（frac_secs）是在 .0 到 .999999999 之间的秒的小数部分。日和小时间可以多个空格。格式元素之间可以有额外的空格。

在 ISO 格式，日、小时、分和秒是在 0 到 999999999 之间的整数。小数秒（frac_secs）是在 .0 到 .999999999 之间的秒的小数部分。值中不允许有空格。

TO_YMINTERVAL 把类型为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 的值转换成 INTERVAL YEAR TO MONTH 类型。TO_YMINTERVAL 也接受两种格式：

•SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
•ISO duration format compatible with the ISO 8601:2004 standard
在 SQL 格式，年是在 0 到 999999999 之间的整数，月是在 0 到 11 之间的整数。格式元素之间允许额外的空格。

在 ISO 格式，年和月是在 0 到 999999999 之间的整数。天、小时、分、秒和小数秒（frac_secs）是在非负整数，可忽略，若指定的话。值之间不允许空格。

示例 22：演示 TO_DSINTERVAL 和 TO_YMINTERVAL。

SQL> SELECT employee_id, last_name  2    FROM employees  3   WHERE hire_date + TO_DSINTERVAL('100 00:00:00') SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"  2    FROM employees; HIRE_DATE   14 months----------- -----------1987-6-17   1988-8-171989-9-21   1990-11-211993-1-13   1994-3-131990-1-3    1991-3-31991-5-21   1992-7-211997-6-25   1998-8-251998-2-5    1999-4-51999-2-7    2000-4-71994-8-17   1995-10-171994-8-16   1995-10-161997-9-28   1998-11-281997-9-30   1998-11-301998-3-7    1999-5-71999-12-7   2001-2-71994-12-7   1996-2-71995-5-18   1996-7-181997-12-24  1999-2-241997-7-24   1998-9-241998-11-15  2000-1-151999-8-10   2000-10-10 …… 107 rows selected SQL> 

TZ_OFFSET(…)

--------------------------------------------------------------------------------

TZ_OFFSET 返回时区偏移。

示例 23：演示 US/Eastern  时区相对于 UTC 的时区偏移。

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN; Session alteredSQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL; TZ_OFFSET('US/EASTERN')------------------------04:00 SQL>

 

 

 

 

 

 

 

 

备注

--------------------------------------------------------------------------------

DATE 数据类型
DATE 类型存储日期和时间信息。尽管日期和时间信息可以以字符和数字类型显示，但是 DATE 类型有其特殊的相关属性。对于每个 DATE 值，Oracle 存储下面信息：世纪、年、月、日、时、分和秒。

可以指定 DATE 值的字面义，或用 TO_DATE  函数把字符或数字值转换成日期值。例如，如下表达 DATE 值的方式，参考“日期时间的字面义”。

按如下确定默认的日期值：

•年是当前年，由 SYSDATE 返回
•月是当前月，由 SYSDATE 返回
•天是 01（月的第一天
•时、分和秒全为 0
SQL> SELECT TO_DATE('2005', 'YYYY') FROM DUAL; TO_DATE('---------01-MAY-05 SQL>TIMESTAMP 类型
TIMESTAMP 类型是 DATE 类型的扩展。它存储年、月、日，还有时、分、秒。该类型对于存储精确的时间值很有用。特别是如下 TIMESTAMP 类型：

TIMESTAMP [(fractional_seconds_precision)] 其中，fractional_seconds_precision 可选，指定在秒的日期时间字段的小数部分 Oracle 存储的位数。当创建一个该类型的列时，值的范围是 0 到 9。默认值为 6。

TIMESTAMP WITH TIME ZONE 类型
TIMESTAMP WITH TIME ZONE 是 TIMESTAMP 的变种，值中包含时区区域名称或时区偏移。时区便宜是本地时间与 UTC（Coordinated Universal Time—formerly Greenwich Mean Time）之间的差（以小时和分）。该类型对跨越地理区域收集和评估信息很有用。

如下方式指定 TIMESTAMP WITH TIME ZONE 类型：

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE其中，fractional_seconds_precision 可选。

TIMESTAMP WITH LOCAL TIME ZONE 类型
TIMESTAMP WITH LOCAL TIME ZONE 是 TIMESTAMP 的另一个变种，值中包含时区偏移。不同于存储在数据库，并被规范成数据库时区的 TIMESTAMP WITH TIME ZONE，它的时区偏移不作为列数据的一部分存储。当用户检索数据时，Oracle 返回用户本地会话时区。时区便宜是本地时间与 UTC 之间的差。该数据类型对于在两层应用（two-tier application）的客户端系统中显示数据信息很有用。这个数据类型是用于在两层_应用_的最新信息显示指定在客户端系统_时区。

如下指定 TIMESTAMP WITH LOCAL TIME ZONE 类型：

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE其中，fractional_seconds_precision 可选。

INTERVAL YEAR TO MONTH 类型
INTERVAL YEAR TO MONTH 使用年和月的日期时间字段存储一段时间。该数据类型只要年和月来表达两个日期之间的差异很有用。

如下指定 INTERVAL YEAR TO MONTH：

INTERVAL YEAR [(year_precision)] TO MONTH其中，year_precision 年日期时间字段的位数。默认为 2。

INTERVAL DAY TO SECOND 类型
INTERVAL DAY TO SECOND 以天、时、分和秒存储一段时间。该类型对两个日期时间之间的精度差异很有用。

如下指定该类型：

INTERVAL DAY [(day_precision)]    TO SECOND [(fractional_seconds_precision)]其中，

•day_precision 是 DAY 日期时间字段的位数。范围为 0 到 9。默认为 2
•fractional_seconds_precision 是 SECOND 日期时间字段小数部分的位数。范围为 0 到 9。默认为 6