Oracle：时间戳

最新推荐文章于 2024-09-14 09:08:26 发布

安善良民弱女子

最新推荐文章于 2024-09-14 09:08:26 发布

阅读量3.3k

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/u011008029/article/details/78461173

版权

oracle 专栏收录该内容

45 篇文章 0 订阅

订阅专栏

与时间戳有关的函数：

1.CURRENT_TIMESTAMP/LOCALTIMESTAMP和SYSTIMESTAMP

select CURRENT_TIMESTAMP,LOCALTIMESTAMP,SYSTIMESTAMP from dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP SYSTIMESTAMP
------------------------------------------------
06-11月-17 09.41.45.024436 上午    +08:00 06-11月-17 09.41.45.024436 上午       06-11月-17 09.41.45.024423 上午 +08:00

2.EXTRACT()

select extract (year from sysdate ) from dual;

EXTRACT(YEARFROMSYSDATE)
-------------------------
2017

3.FROM_TZ()
FROM_TZ(x, time_zone):将timestamp类型的x转换为由time_zone指定的时区，并返回一个timestamp with timezone类型的值。就是将x和time_zone合并

select from_TZ(timestamp '2017-10-23 09:13:31.1234', '-7:00') as fromTZ from dual;

FROMTZ
-----------------------------------------
23-10月-17 09.13.31.123400000 上午 -07:00

4.SYS_EXTRACT_UTC()
SYS_EXTRACT_UTC(x)：用于将timestamp with timezone类型的x转换为UTC时区日期和时间的timestamp类型。

select SYS_EXTRACT_UTC(timestamp '2017-10-23 09:13:31 PST') as SYSEXTRACTUTC from dual;
SYSEXTRACTUTC
----------------------------------
23-10月-17 04.13.31.000000000 下午

5.TO_TIMESTAMP()
TO_TIMESTAMP(x):用于将x转换为timestamp类型。

select TO_TIMESTAMP('2017-10-23 09:13:31', 'YYYY-MM-DD HH24:MI:SS.FF') as TOTIMESTAMP from dual;

TOTIMESTAMP
---------------------------------------
23-10月-17 09.13.31.000000000 上午

6.TO_TIMESTAMP_TZ()
TO_TIMESTAMP_TZ(x):用于将x转换为timestamp with timezone类型。

select TO_TIMESTAMP_TZ('2017-10-23 09:13:31 PST', 'YYYY-MM-DD HH24:MI:SS.FF TZR') as TOTIMESTAMPTZ from dual;

TOTIMESTAMPTZ
---------------------------------------
23-10月-17 09.13.31.000000000 上午 PST

7.CAST()
CAST():将字符串转换为timestamp with local time zone类型

select cast(to_timestamp_TZ('2017-12-10 13:10:34.1234 PST','YYYY-MM-DD HH24:MI:SS.FF TZR') as timestamp with local time zone) as castTemp from dual;
CASTTEMP
---------------------------------------
11-12月-17 05.10.34.123400 上午