oracl timezone时区的使用

不同时区的查询

  --不同时区的查询
  select n.tzname, n.tzabbrev, tz_offset(n.tzname) from v$timezone_names n;
   
  select distinct n.tzabbrev from v$timezone_names n
  where tzname ='Africa/Lusaka'

SELECT SYSTIMESTAMP FROM DUAL;
SELECT SYSTIMESTAMP + INTERVAL '5' HOUR FROM DUAL;

SELECT TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00',
                      'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
 FROM DUAL;

• TIMESTAMP类型到TIMESTAMP WITH TIME ZONE类型：
• from_tz函数，可以将一个timstamp和timzone拼成一个timestamp with timezone

SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), '-11:00') FROM DUAL;

使用TIMESTAMP AT TIME ZONE的方式指定时区

SELECT TIMESTAMP '2009-3-9 17:56:53.232323' AT TIME ZONE 'GMT' FROM DUAL;

• 时区的转换。比如希望将东4区时间转换为西3区的时间：
• 东区转西区是退后时间

SELECT TIMESTAMP '2009-3-9 18:05:23.234 +04:00' AT TIME ZONE '-03:00'
  FROM DUAL;

/*
赞比亚时间为+2，北京时间为+8
转换北京时间为赞比亚时间
*/
select to_char(from_tz(CAST(SYSDATE AS TIMESTAMP), tz_offset('+08:00')) at TIME ZONE
               '+02:00',
               'yyyymmdd') as currdate
  from dual;

/*
转换北京时间为赞比亚时间
*/
select from_tz(CAST(SYSDATE AS TIMESTAMP), tz_offset('Asia/Shanghai')) at TIME ZONE 'Africa/Lusaka' as zanbiya
 from dual;
--如果仅仅转换到UTC (GMt)，使用 sys_extract_utc
select systimestamp,
      sys_extract_utc(systimestamp) utc,
      (to_timestamp_tz(to_char(systimestamp)) at time zone '08:00') manually_utc
 from dual;

--自定义函数  
CREATE OR REPLACE FUNCTION "MY_NEW_TIME" (
p_dwtz timestamp with time zone,
p_tz varchar2
)
return date
as
begin
 return cast(p_dwtz at time zone p_tz as date);
end;  

----调用自定义函数
select my_new_time(timestamp '2008-05-04 23:00:00' at time zone '+08:00' , '0:0')  from dual;
select my_new_time(systimestamp , tz_offset('Africa/Lusaka')) from dual;

---

夏令时：

冬令时：