Oracle 四大datetimes

在oracle9i之后，关于datetime的数据类型主要包括date，timestamp，timestamp with time zone， timestamp with local time zone。

 

直接来个例子吧

先创建一个表

CREATE TABLE T_timestamp (t1 DATE, t2 TIMESTAMP , t3 TIMESTAMP(9), t4 TIMESTAMP WITH TIME ZONE , t5 TIMESTAMP WITH LOCAL TIME ZONE);

 

设置session timezone，插入一条记录

ALTER SESSION SET TIME_ZONE ='America/New_York';

INSERT INTO t_timestamp VALUES(to_date('2000-01-01','yyyy-mm-dd'),
CAST( to_date('2000-01-01','yyyy-mm-dd') AS TIMESTAMP),
CAST( to_date('2000-01-01','yyyy-mm-dd') AS TIMESTAMP),
from_tz(CAST(to_date('2000-01-01','yyyy-mm-dd') AS TIMESTAMP),'America/Denver'),
CAST( to_date('2000-01-01','yyyy-mm-dd') AS TIMESTAMP));

SELECT * FROM t_timestamp;

 

查询数据，发现其中不同格式的日期是大有乾坤
SELECT DBTIMEZONE,SESSIONTIMEZONE FROM dual;

SELECT * FROM t_timestamp;

 

重设时区，再查询，发现with local time zone的玄机。
ALTER SESSION SET TIME_ZONE ='America/Denver';
SELECT * FROM t_timestamp;

 

下面再贴些用到的一些日期函数使用（可能针对不同场合）：

 

select to_char(from_tz(TIMESTAMP '2005-09-11 01:50:42', '-05:00') AT TIME ZONE '+08:00') FROM dual;
SELECT to_date(to_char(from_tz(TIMESTAMP '2005-09-11 01:50:42', '-05:00') AT TIME ZONE '+08:00','yyyy-mon-dd'),'yyyy-mon-dd') FROM dual;
select from_tz(TIMESTAMP '2005-09-11 01:50:42', 'America/new_york') AT TIME ZONE '+08:00' FROM dual;
SELECT to_timestamp_tz(to_char(from_tz(TIMESTAMP '2005-09-11 01:50:42', '-05:00') AT TIME ZONE '+08:00'),'DD-MON-RR HH.MI.SSXFF AM TZR') FROM dual;

SELECT * FROM Nls_Database_Parameters;
select * from DATABASE_PROPERTIES

alter session set time_zone='America/Denver';
SELECT  cast(timestamp '2000-06-30 18:30:00' as TIMESTAMP WITH LOCAL TIME ZONE) AT TIME ZONE 'Asia/Shanghai',
        cast(timestamp '2000-01-30 18:30:00' as TIMESTAMP WITH LOCAL TIME ZONE) AT TIME ZONE 'Asia/Shanghai' FROM dual;
       

SELECT SYSDATE,Systimestamp FROM dual;
SELECT to_char(SYSDATE,'YYYY-MON-DD HH24:MI:SS') FROM dual;
SELECT to_date('08-JAN-2009','DD-MON-YYYY') FROM dual;
ALTER SESSION SET TIME_ZONE='-5:00'

ALTER SESSION SET TIME_ZONE='America/Denver';
SELECT tz_offset('America/Denver') FROM dual;

SELECT CAST(TO_TIMESTAMP_TZ('28-06-10 15:00:00 -6:00','DD-MM-YY HH24:MI:SS TZH:TZM') AS TIMESTAMP WITH LOCAL TIME ZONE)  FROM dual;

SELECT CAST(TO_TIMESTAMP_TZ( to_char(PN_ASSIGNMENT_WORK.WORK_START,'DD-MON-YY DD-MON-YY HH24:MI:SS')||tz_offset('America/Denver'),'DD-MM-YY HH24:MI:SS TZH:TZM')
AS TIMESTAMP WITH LOCAL TIME ZONE),TO_TIMESTAMP_TZ('2000-10-28 15:00:00 -7:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM dual;

select from_tz(cast(timestamp '2000-06-30 18:30:00' as timestamp),tz_offset('America/Denver'))
AT TIME ZONE 'America/Denver',from_tz(cast(timestamp '2000-01-30 18:30:00' as timestamp),tz_offset('America/Denver')) 
from dual;

SELECT CAST (TIMESTAMP '30-01-00 07.30.00.000000 PM AMERICA/DENVER' AS TIMESTAMP WITH TIME ZONE) FROM dual;

ALTER SESSION SET TIME_ZONE='America/Denver';
SELECT cast(timestamp '2000-06-30 18:30:00' as TIMESTAMP WITH LOCAL TIME ZONE) AT TIME ZONE 'Asia/Shanghai' FROM dual;