免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
› 论坛 › 数据库技术 › Oracle › Oracle与时间戳有关的函数
最近访问板块 发新帖
查看: 2504 | 回复: 0
打印 上一主题 下一主题

Oracle与时间戳有关的函数 [复制链接]

lxl0121

论坛徽章:
0
跳转到指定楼层
1楼 [收藏(0)] [报告]
发表于 2011-12-23 01:12 |只看该作者 |倒序浏览
有很多函数可以用来查询和处理时间戳，表中列出了这些函数。
函    数说    明
CURRENT_TIMESTAMP()返回一个 TIMESTAMP WITH TIME ZONE 类型的值，其中包括当前会话的日期和时间以及会话的时区
EXTRACT(

{ YEAR | MONTH | DAY |

HOUR | MINUTE | SECOND } |

{ TIMEZONE_HOUR |

TIMEZONE_MINUTE } |

{ TIMEZONE_REGION | }

TIMEZONE_ABBR }

FROM x)		从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型
FROM_TZ( x, time_zone)将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值
LOCALTIMESTAMP返回一个TIMESTAMP 类型，其中包含会话的当前日期和时间
SYSTIMESTAMP返回一个TIMESTAMP WITH TIME ZONE类型，其中包括数据库的当前日期、时间，以及数据库时区
SYS_EXTRACT_UTC(x)将TIMESTAMP WITH TIMEZONE类型的x转换为一个TIMESTAMP类型，其中包含了UTC时区中的日期和时间
TO_TIMESTAMP(x, [format])将字符串x转换为一个TIMESTAMP类型，还可以为x指定一个可选的参数format
TO_TIMESTAMP_TZ(x, [format])将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型，还可以为x指定一个可选的参数format

 

 

1. CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP

下面这个查询同时调用了CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP函数(我的会话时区和数据库时区都是PST，它比UTC晚8小时)：

Sql代码  收藏代码
  1. SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  
  2. FROM dual;  
  3. CURRENT_TIMESTAMP  
  4. -----------------------------------  
  5. LOCALTIMESTAMP  
  6. -----------------------------------  
  7. SYSTIMESTAMP  
  8. -----------------------------------  
  9. 05-NOV-07 12.15.32.734000 PM PST  
  10. 05-NOV-07 12.15.32.734000 PM  
  11. 05-NOV-07 12.15.32.734000 PM -08:00  

 

如果将TIME_ZONE设置为EST，并重新执行上面这个查询，就会得到如下输出结果：

Sql代码  收藏代码
  1. ALTER SESSION SET TIME_ZONE = 'EST';  
  2.   
  3. Session altered.  
  4.   
  5. SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  
  6. FROM dual;  
  7.   
  8. CURRENT_TIMESTAMP  
  9. -----------------------------------------------------------  
  10. LOCALTIMESTAMP  
  11. -----------------------------------------------------------  
  12. SYSTIMESTAMP  
  13. -----------------------------------------------------------  
  14. 05-NOV-07 03.19.57.562000 PM EST  
  15. 05-NOV-07 03.19.57.562000 PM  
  16. 05-NOV-07 12.19.57.562000 PM -08:00  

 

下面这条语句将会话时区设置会PST：

 

Sql代码  收藏代码
  1. ALTER SESSION SET TIME_ZONE = 'PST';  
  2.   
  3. Session altered.  

 

2. EXTRACT函数

 

    EXTRACT函数用于从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型。下面这个查询使用EXTRACT函数从由TO_DATE()返回的DATE类型中提取年、月、日：

Sql代码  收藏代码
  1.  SELECT  
  2. EXTRACT(YEAR FROM TO_DATE('01-JAN-2008 19:15:26',  
  3. 'DD-MON-YYYY HH24:MI:SS')) AS YEAR,  
  4. EXTRACT(MONTH FROM TO_DATE('01-JAN-2008 19:15:26',  
  5. 'DD-MON-YYYY HH24:MI:SS')) AS MONTH,  
  6. EXTRACT(DAY FROM TO_DATE('01-JAN-2008 19:15:26',  
  7. 'DD-MON-YYYY HH24:MI:SS')) AS DAY  
  8. FROM dual;  
  9.   
  10.        YEAR       MONTH           DAY  
  11. ---------- ---------- ----------  
  12.  2008            1             1  
 



    下面这个查询使用EXTRACT函数从由TO_TIMESTAMP()返回的TIMESTAMP类型中提取时、分、秒：

Sql代码  收藏代码
  1.  SELECT  
  2. EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
  3. 'DD-MON-YYYY HH24:MI:SS')) AS HOUR,  
  4. EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
  5. 'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,  
  6. EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  
  7. 'DD-MON-YYYY HH24:MI:SS')) AS SECOND  
  8. FROM dual;  
  9.   
  10.       HOUR       MINUTE       SECOND  
  11. ---------- ---------- ----------  
  12. 19            15            26  
 



    最后这个查询使用EXTRACT函数从由TO_TIMESTAMP_TZ()返回的TIMESTAMP WITH TIMEZONE类型中提取时区的时、分、秒、区域和时区缩写：

Sql代码  收藏代码
  1.  SELECT  
  2. EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(  
  3. '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  
  4. AS TZH,  
  5. EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(  
  6. '01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  
  7. AS TZM,  
  8. EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(  
  9. '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  
  10. AS TZR,  
  11. EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(  
  12. '01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  
  13. AS TZA  
  14. FROM dual;  
  15.   
  16.         TZH          TZM   TZR                   TZA  
  17. ---------- ---------- ----------- ----------  
  18.   -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代码  收藏代码
  1.  SELECT FROM_TZ(TIMESTAMP '2008-05-13 07:15:31.1234', '-7:00')  
  2. FROM dual;  
  3.   
  4. FROM_TZ(TIMESTAMP'2008-05-1307:15:31.1234','-7:00')  
  5. ---------------------------------------------------  
  6. 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代码  收藏代码
  1.  SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-11-17 19:15:26 PST')  
  2. FROM dual;  
  3.   
  4. SYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719:15:26PST')  
  5. -------------------------------------------------  
  6. 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代码  收藏代码
  1.  SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-05-17 19:15:26 PST')  
  2. FROM dual;  
  3.   
  4. SYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719:15:26PST')  
  5. -------------------------------------------------  
  6. 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代码  收藏代码
  1.  SELECT TO_TIMESTAMP('2008-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')  
  2. FROM dual;  
  3.   
  4. TO_TIMESTAMP('2008-05-1307:15:31.1234','YYYY-MM-DDHH24:MI:SS.FF')  
  5. -----------------------------------------------------------------  
  6. 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代码  收藏代码
  1.  SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  
  2. 'YYYY-MM-DD HH24:MI:SS.FF TZR')  
  3. FROM dual;  
  4.   
  5. TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFTZR')  
  6. --------------------------------------------------------------------------  
  7. 13-MAY-08 07.15.31.123400000 AM PST  
 



    下面这个查询使用与UTC的时差 -7:00(-7:00在格式字符串中用TZR和TZM来识别)：

Sql代码  收藏代码
  1. SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 -7:00',  
  2.   
  3.  'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')  
  4. FROM dual;  
  5.   
  6. TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234-7:00','YYYY-MM-DDHH24:MI:SS.FFTZH  
  7. --------------------------------------------------------------------------  
  8. 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代码  收藏代码
  1.  SELECT CAST('13-JUN-08' AS TIMESTAMP WITH LOCAL TIME ZONE)  
  2. FROM dual;  
  3.   
  4. CAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)  
  5. ---------------------------------------------  
  6. 13-JUN-08 12.00.00.000000 AM  
 



    此查询返回的时间戳包含日期2008年6月13日和时间12 A.M.。

    下面这个查询使用CAST()将一个更复杂的字符串转换为TIMESTAMP WITH LOCAL TIME ZONE：

Sql代码  收藏代码
  1.  SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  
  2. 'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  
  3. FROM dual;  
  4.   
  5. CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FF  
  6. --------------------------------------------------------------------------  
  7. 13-MAY-08 06.15.31.123400 AM  
 



    此查询返回的时间戳包含日期2008年5月13日和时间6:15:31.1234 AM PST(PST是数据库时区和会话时区)。

    下面这个查询与上面的查询做相同的事情，只是时区是EST：

Sql代码  收藏代码
  1.  SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 EST',  
  2. 'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  
  3. FROM dual;  
  4.   
  5. CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234EST','YYYY-MM-DDHH24:MI:SS.FF  
  6. --------------------------------------------------------------------------  
  7. 13-MAY-08 04.15.31.123400 AM  
 



    此查询返回的时区包含日期2008年3月13日和时间4:15:31.1234 AM PST(因为PST比EST晚3小时，所以此时间戳返回的时间比实际查询的时间早3小时)。
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号：11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们：huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP