当前位置:Gxlcms > mysql > Oracle11gRelease1(11.1)单行函数日期函数

Oracle11gRelease1(11.1)单行函数日期函数

时间:2021-07-01 10:21:17 帮助过:35人阅读

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') <= DATE '1990-01-01' 4 ORDER BY employee_id; EMPLOYEE_ID LAST_NAME----------- ------------------------- 100 King 101 Kochhar 200 WhalenSQL> 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

人气教程排行