发布日期:2015-11-06 15:24 来源: 标签: 数据库 oracle教程 oracle函数 oracle时间戳
本章我们主要学习与Oracle时间戳有关的函数有哪些?怎样使用这些函数?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
有很多函数可以用来查询和处理时间戳,表5-9中列出了这些函数。


1. CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP
下面这个查询同时调用了CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP函数(我的会话时区和数据库时区都是PST,它比UTC晚8小时):
Sql代码
    SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  
    FROM dual;  
    CURRENT_TIMESTAMP  
    -----------------------------------  
    LOCALTIMESTAMP  
    -----------------------------------  
    SYSTIMESTAMP  
    -----------------------------------  
    05-NOV-07 12.15.32.734000 PM PST  
    05-NOV-07 12.15.32.734000 PM  
    05-NOV-07 12.15.32.734000 PM -08:00 
如果将TIME_ZONE设置为EST,并重新执行上面这个查询,就会得到如下输出结果:
Sql代码 
    ALTER SESSION SET TIME_ZONE = 'EST';  
    Session altered.  
    SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  
    FROM dual;  
    CURRENT_TIMESTAMP  
    -----------------------------------------------------------  
    LOCALTIMESTAMP  
    -----------------------------------------------------------  
    SYSTIMESTAMP  
    -----------------------------------------------------------  
    05-NOV-07 03.19.57.562000 PM EST  
    05-NOV-07 03.19.57.562000 PM  
    05-NOV-07 12.19.57.562000 PM -08:00  
下面这条语句将会话时区设置会PST:
Sql代码
    ALTER SESSION SET TIME_ZONE = 'PST';  
    Session altered.  

2. EXTRACT函数
    EXTRACT函数用于从x中提取并返回年、月、日、时、分、秒或时区,其中x可以是时间戳类型或DATE类型。下面这个查询使用EXTRACT函数从由TO_DATE()返回的DATE类型中提取年、月、日:
Sql代码  
    SELECT  
    EXTRACT(YEAR FROM TO_DATE('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS YEAR,  
    EXTRACT(MONTH FROM TO_DATE('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS MONTH,  
    EXTRACT(DAY FROM TO_DATE('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS DAY  
    FROM dual; 
           YEAR       MONTH           DAY  
    ---------- ---------- ----------  
     2008            1             1  
    下面这个查询使用EXTRACT函数从由TO_TIMESTAMP()返回的TIMESTAMP类型中提取时、分、秒:
Sql代码  
    SELECT  
    EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS HOUR,  
    EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,  
    EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
    'DD-MON-YYYY HH24:MI:SS')) AS SECOND  
    FROM dual;  
          HOUR       MINUTE       SECOND  
    ---------- ---------- ----------  
    19            15            26  
    最后这个查询使用EXTRACT函数从由TO_TIMESTAMP_TZ()返回的TIMESTAMP WITH TIMEZONE类型中提取时区的时、分、秒、区域和时区缩写:
Sql代码 
    SELECT  
    EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(  
    '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  
    AS TZH,  
    EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(  
    '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  
    AS TZM,  
    EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(  
    '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  
    AS TZR,  
    EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(  
    '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  
    AS TZA  
    FROM dual; 
            TZH          TZM   TZR                   TZA  
    ---------- ---------- ----------- ----------  
      -7          -15   PST                    PST  

3. FROM_TZ()
    FROM_TZ(x, time_zone)将TIMESTAMP类型的x转换为由time_zone指定的时区,并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值。
    例如,下面这个查询将时间戳2008-05-13 07:15:31.1234和与UTC的时差-7:00合并起来:
Sql代码
    SELECT FROM_TZ(TIMESTAMP '2008-05-13 07:15:31.1234', '-7:00')  
    FROM dual;
    FROM_TZ(TIMESTAMP'2008-05-1307:15:31.1234','-7:00')  
    ---------------------------------------------------  
    13-MAY-08 07.15.31.123400000 AM -07:00  

4. SYS_EXTRACT_UTC()
    SYS_EXTRACT_UTC (x)用于将TIMESTAMP WITH TIMEZONE类型的x转换为一个包含UTC时区日期和时间的TIMESTAMP类型。
    下面这个查询将2008-11-17 19:15:26 PST转换为UTC:
Sql代码 
    SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-11-17 19:15:26 PST')  
    FROM dual; 
    SYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719:15:26PST')  
    -------------------------------------------------  
    18-NOV-08 03.15.26.000000000 AM  
    因为在冬季PST比UTC晚8个小时,因此这个查询返回的TIMESTAMP类型的值比2008-11-17 19:15:26 PST早了8个小时,即18-NOV-08 03.15.26 AM.。
    如果是在夏季,则返回的TIMESTAMP类型的值只比UTC早7个小时。
Sql代码 
    SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-05-17 19:15:26 PST')  
    FROM dual; 
    SYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719:15:26PST')  
    -------------------------------------------------  
    18-MAY-08 02.15.26.000000000 AM 

5. TO_TIMESTAMP()
    TO_TIMESTAMP(x,[format])用于将字符串x(可能是CHAR、VARCHAR2、NCHAR或NVARCHAR2)转换为一个TIMESTAMP类型,该函数中可以为x指定可选的参数format。
    下面这个查询将字符串2005-05-13 07:15:31.1234转换为TIMESTAMP类型,格式为YYYY-MM-DD HH24:MI:SS.FF:
Sql代码 
    SELECT TO_TIMESTAMP('2008-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')  
    FROM dual;  
    TO_TIMESTAMP('2008-05-1307:15:31.1234','YYYY-MM-DDHH24:MI:SS.FF')  
    -----------------------------------------------------------------  
    13-MAY-08 07.15.31.123400000 AM 

6. TO_TIMESTAMP_TZ()
    TO_TIMESTAMP_TZ(x,[ format ])用于将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型,该函数中可以为x指定可选的参数format。
    下面这个查询向TO_TIMESTAMP_TZ函数传递时区PST(在格式字符串中使用TZR来识别):
Sql代码
     SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  
    'YYYY-MM-DD HH24:MI:SS.FF TZR')  
    FROM dual; 
    TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFTZR')  
    --------------------------------------------------------------------------  
    13-MAY-08 07.15.31.123400000 AM PST  
    下面这个查询使用与UTC的时差 -7:00(-7:00在格式字符串中用TZR和TZM来识别):
Sql代码 
    SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 -7:00',  
    'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')  
    FROM dual; 
    TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234-7:00','YYYY-MM-DDHH24:MI:SS.FFTZH  
    --------------------------------------------------------------------------  
    13-MAY-08 07.15.31.123400000 AM -07:00  

7. 将字符串转换为TIMESTAMP WITH LOCAL TIME ZONE
    使用CAST函数可以将一个字符串转换为TIMESTAMP WITH LOCAL TIME ZONE。第4章曾介绍过CAST(),回想一下,CAST(x AS type)将x转换为由type指定的兼容的数据库类型。
    下面这个查询使用CAST()将字符串13-JUN-08转换为TIMESTAMP WITH LOCAL TIME ZONE:
Sql代码
    SELECT CAST('13-JUN-08' AS TIMESTAMP WITH LOCAL TIME ZONE)  
    FROM dual; 
    CAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)  
    ---------------------------------------------  
    13-JUN-08 12.00.00.000000 AM  
    此查询返回的时间戳包含日期2008年6月13日和时间12 A.M.。
    下面这个查询使用CAST()将一个更复杂的字符串转换为TIMESTAMP WITH LOCAL TIME ZONE:
Sql代码
     SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  
    'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  
    FROM dual;  
      
    CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FF  
    --------------------------------------------------------------------------  
    13-MAY-08 06.15.31.123400 AM  
    此查询返回的时间戳包含日期2008年5月13日和时间6:15:31.1234 AM PST(PST是数据库时区和会话时区)。
    下面这个查询与上面的查询做相同的事情,只是时区是EST:
Sql代码
     SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 EST',  
    'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  
    FROM dual; 
    CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234EST','YYYY-MM-DDHH24:MI:SS.FF  
    --------------------------------------------------------------------------  
    13-MAY-08 04.15.31.123400 AM 
    此查询返回的时区包含日期2008年3月13日和时间4:15:31.1234 AM PST(因为PST比EST晚3小时,所以此时间戳返回的时间比实际查询的时间早3小时)。

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。