Oracle：时间戳

来源：互联网发布：卷皮折扣和淘宝那个好编辑：程序博客网时间：2024/06/10 07:04

与时间戳有关的函数：

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 上午

阅读全文

0 0