Oracle 时区问题

oracle中的tz_offset是用来返回给定时区与标准时区(UTC universal of time Coordinate 通用时间坐标)的偏移量，其参数可以是：

1.一个合法的时区名

2.一个离UTC的标准偏移量

3.关键字SessionTimeZone或者是DB_timezone

 

其中合法的时区名可以从系统视图V$TimeZone_names中的列TZname中的得到：

TZNAME

——————————————————————————————————————————–

Africa/Cairo

Africa/Tripoli

America/Adak

America/Anchorage

America/Atka

America/Chicago

America/Denver

America/Detroit

America/Edmonton

America/Ensenada

America/Fort_Wayne

America/Halifax

America/Havana

America/Indiana/Indianapolis

America/Indianapolis

America/Jamaica

America/Los_Angeles

America/Manaus

America/Mazatlan

America/Mexico_City

America/Montreal

America/New_York

America/Noronha

America/Phoenix

America/Porto_Acre

America/Regina

America/Santiago

America/Sao_Paulo

America/Shiprock

America/St_Johns

America/Tijuana

America/Vancouver

America/Whitehorse

America/Winnipeg

Asia/Calcutta

Asia/Hong_Kong

Asia/Istanbul

Asia/Jerusalem

Asia/Riyadh

Asia/Seoul

Asia/Shanghai

Asia/Singapore

Asia/Taipei

Asia/Tehran

Asia/Tel_Aviv

Asia/Tokyo

Atlantic/Reykjavik

Australia/ACT

Australia/Adelaide

Australia/Brisbane

Australia/Broken_Hill

Australia/Canberra

Australia/Darwin

Australia/Hobart

Australia/LHI

Australia/Lindeman

Australia/Lord_Howe

Australia/Melbourne

Australia/NSW

Australia/North

Australia/Perth

Australia/Queensland

Australia/South

Australia/Sydney

Australia/Tasmania

Australia/Victoria

Australia/West

Australia/Yancowinna

Brazil/Acre

Brazil/DeNoronha

Brazil/East

Brazil/West

CET

CST

CST6CDT

Canada/Atlantic

Canada/Central

Canada/East-Saskatchewan

Canada/Eastern

Canada/Mountain

Canada/Newfoundland

Canada/Pacific

Canada/Saskatchewan

Canada/Yukon

Chile/Continental

Chile/EasterIsland

Cuba

EET

EST

EST5EDT

Egypt

Eire

Etc/GMT

Etc/GMT+0

Etc/GMT+1

Etc/GMT+10

Etc/GMT+11

Etc/GMT+12

Etc/GMT+2

Etc/GMT+3

Etc/GMT+4

Etc/GMT+5

Etc/GMT+6

Etc/GMT+7

Etc/GMT+8

Etc/GMT+9

Etc/GMT-0

Etc/GMT-1

Etc/GMT-10

Etc/GMT-11

Etc/GMT-12

Etc/GMT-13

Etc/GMT-2

Etc/GMT-3

Etc/GMT-4

Etc/GMT-5

Etc/GMT-6

Etc/GMT-7

Etc/GMT-8

Etc/GMT-9

Etc/GMT0

Etc/Greenwich

Europe/Dublin

Europe/Istanbul

Europe/Lisbon

Europe/London

Europe/Moscow

Europe/Warsaw

GB

GB-Eire

GMT

GMT+0

GMT-0

GMT0

Greenwich

HST

Hongkong

Iceland

Iran

Israel

Jamaica

Japan

Kwajalein

Libya

MET

MST

MST7MDT

Mexico/BajaNorte

Mexico/BajaSur

Mexico/General

NZ

NZ-CHAT

Navajo

PRC

PST

PST8PDT

Pacific/Auckland

Pacific/Chatham

Pacific/Easter

Pacific/Honolulu

Pacific/Kwajalein

Pacific/Pago_Pago

Pacific/Samoa

Poland

Portugal

ROC

ROK

Singapore

Turkey

US/Alaska

US/Aleutian

US/Arizona

US/Central

US/East-Indiana

US/Eastern

US/Hawaii

US/Michigan

US/Mountain

US/Pacific

US/Pacific-New

US/Samoa

W-SU

WET

 

 

/* SQL EXAMPLE */

 

SELECT SYSTIMESTAMP AT TIME ZONE 'Etc/GMT+5' FROM DUAL;

 

SELECT SYSTIMESTAMP AT TIME ZONE 'Etc/GMT-8' FROM DUAL;

 

SELECT SYSTIMESTAMP AT TIME ZONE 'Etc/GMT' FROM DUAL;

 

SELECT TO_TIMESTAMP_TZ('2009/05/07','YYYY/MM/DD') FROM DUAL;

 

SELECT *

  FROM DUAL

 WHERE TO_TIMESTAMP_TZ ('2009/05/07 0 -05:00', 'YYYY/MM/DD HH24 TZH:TZM') =

                   TO_TIMESTAMP_TZ ('2009/05/07 13 +08:00', 'YYYY/MM/DD HH24 TZH:TZM');

                  

select cast(SYSTIMESTAMP AT TIME ZONE 'US/Central' as date) from dual;