Oracle 11g Release 1 (11.1) 单行函数——日期函数
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#autoId5
本文内容
- 日期函数
- 附录
- 修改记录
日期函数
日期函数操作日期值（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 altered
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered
SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
-------------------- --------------------
-05:00 2012-7-31 8:22:24
SQL> ALTER SESSION SET TIME_ZONE = '-8:0';
Session altered
SQL> 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 altered
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered
SQL> 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 altered
SQL> 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_test
VALUES
(TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
下面语句使用了正确的格式掩码：
INSERT INTO current_test
VALUES
(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 EXTRACT(HOUR FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT HOUR",
5 EXTRACT(MINUTE FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT MINUTE",
6 EXTRACT(SECOND FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT SECOND"
7 FROM DUAL;
EXTRACT YEAR EXTRACT MONTH EXTRACT DAY EXTRACT HOUR EXTRACT MINUTE EXTRACT SECOND
------------ ------------- ----------- ------------ -------------- --------------
2012 7 31 8 20 40
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 altered
SQL> 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 altered
SQL> 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_test
VALUES
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
下面语句使用了正确的格式掩码：
INSERT INTO local_test
VALUES
(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 altered
SQL> 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 61900
Ande 2000-3-24 6400.00 106500
Atkinson 1997-10-30 2800.00 160000
Austin 1997-6-25 4800.00 134700
Baer 1994-6-7 10000.00 36800
Baida 1997-12-24 2900.00 180900
Banda 2000-4-21 6200.00 109400
Bates 1999-3-24 7300.00 104900
Bell 1996-2-4 4000.00 36300
Bernstein 1997-3-24 9500.00 132200
Bissot 1997-8-20 3300.00 145600
Bloom 1998-3-23 10000.00 147300
Bull 1997-2-20 4100.00 88000
Cabrio 1999-2-7 3000.00 104100
Cambrault 1998-12-9 7500.00 122500
Cambrault 1999-10-15 11000.00 81400
Chen 1997-9-28 8200.00 153800
Chung 1997-6-14 3800.00 133200
Colmenares 1999-8-10 2500.00 76000
Davies 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 altered
SQL> 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 created
SQL> ALTER SESSION SET TIME_ZONE = '-8:00';
Session altered
SQL> 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 inserted
SQL> 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 inserted
SQL> 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:00
02-12ÔÂ-1999 10:00:00.000000 02-12ÔÂ-1999 10:00:00.000000 -08:00
SQL> 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.000000
SQL> ALTER SESSION SET TIME_ZONE = '-5:00';
Session altered
SQL> 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:00
02-12ÔÂ-1999 10:00:00.000000 02-12ÔÂ-1999 10:00:00.000000 -08:00
SQL> 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 altered
SQL> 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') <= DATE '1990-01-01'
4 ORDER BY employee_id;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
200 Whalen
SQL> SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
2 FROM employees;
HIRE_DATE 14 months
----------- -----------
1987-6-17 1988-8-17
1989-9-21 1990-11-21
1993-1-13 1994-3-13
1990-1-3 1991-3-3
1991-5-21 1992-7-21
1997-6-25 1998-8-25
1998-2-5 1999-4-5
1999-2-7 2000-4-7
1994-8-17 1995-10-17
1994-8-16 1995-10-16
1997-9-28 1998-11-28
1997-9-30 1998-11-30
1998-3-7 1999-5-7
1999-12-7 2001-2-7
1994-12-7 1996-2-7
1995-5-18 1996-7-18
1997-12-24 1999-2-24
1997-7-24 1998-9-24
1998-11-15 2000-1-15
1999-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 altered
SQL> 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
修改记录
- 2012-08-18 [ADD]
