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