Oracle PL/SQL/Date Timezone/Time Zone
Содержание
- 1 ALTER SESSION statement sets the local time zone to Pacific Standard Time (PST)
- 2 Obtaining Time Zone Names
- 3 Select the current time zone
- 4 Time change from Chicago to Los Angeles(Central to Pacific)
- 5 Timestamp with local time zone as a table column
- 6 Time zone name
- 7 TIME_ZONE to EST and get CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
ALTER SESSION statement sets the local time zone to Pacific Standard Time (PST)
SQL>
SQL> --ALTER SESSION statement sets the local time zone to Pacific Standard Time (PST):
SQL>
SQL> ALTER SESSION SET TIME_ZONE = "PST";
Session altered.
SQL>
SQL>
Obtaining Time Zone Names
SQL>
SQL> --You can obtain all the time zone names by selecting all the rows from v$timezone_names.
SQL>
SQL> SELECT * FROM v$timezone_names where rownum < 200;
TZNAME TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
Africa/Algiers WEST
Africa/Algiers CET
Africa/Algiers CEST
Africa/Cairo LMT
Africa/Cairo EET
Africa/Cairo EEST
Africa/Casablanca LMT
Africa/Casablanca WET
Africa/Casablanca WEST
Africa/Casablanca CET
Africa/Ceuta LMT
Africa/Ceuta WET
Africa/Ceuta WEST
Africa/Ceuta CET
Africa/Ceuta CEST
Africa/Djibouti LMT
Africa/Djibouti EAT
Africa/Freetown LMT
Africa/Freetown FMT
Africa/Freetown WAT
Africa/Freetown SLST
Africa/Freetown GMT
Africa/Johannesburg LMT
Africa/Johannesburg SAST
Africa/Khartoum LMT
Africa/Khartoum CAT
Africa/Khartoum CAST
Africa/Mogadishu LMT
Africa/Mogadishu EAT
Africa/Mogadishu BEAT
Africa/Nairobi LMT
Africa/Nairobi EAT
Africa/Nairobi BEAT
Africa/Nairobi BEAUT
Africa/Nouakchott LMT
Africa/Nouakchott GMT
Africa/Nouakchott WAT
Africa/Tripoli LMT
Africa/Tripoli CET
Africa/Tripoli CEST
Africa/Tripoli EET
Africa/Tunis LMT
Africa/Tunis PMT
Africa/Tunis CET
Africa/Tunis CEST
Africa/Windhoek LMT
Africa/Windhoek SWAT
Africa/Windhoek SAST
Africa/Windhoek CAT
Africa/Windhoek WAT
Africa/Windhoek WAST
America/Adak LMT
America/Adak NST
America/Adak NWT
America/Adak BST
America/Adak BDT
America/Adak HAST
America/Adak HADT
America/Anchorage LMT
America/Anchorage CAT
America/Anchorage CAWT
America/Anchorage AHST
America/Anchorage AHDT
America/Anchorage AKST
America/Anchorage AKDT
America/Anguilla LMT
America/Anguilla AST
America/Araguaina LMT
America/Araguaina BRT
America/Araguaina BRST
America/Aruba LMT
America/Aruba ANT
America/Aruba AST
America/Asuncion LMT
America/Asuncion AMT
America/Asuncion PYT
America/Asuncion PYST
America/Atka LMT
America/Atka NST
America/Atka NWT
America/Atka BST
America/Atka BDT
America/Atka HAST
America/Atka HADT
America/Belem LMT
America/Belem BRT
America/Belem BRST
America/Boa_Vista LMT
America/Boa_Vista AMT
America/Boa_Vista AMST
America/Bogota LMT
America/Bogota BMT
America/Bogota COT
America/Bogota COST
America/Boise LMT
America/Boise PST
America/Boise PWT
America/Boise MST
America/Boise MWT
America/Boise MDT
America/Buenos_Aires LMT
America/Buenos_Aires CMT
America/Buenos_Aires ART
America/Buenos_Aires ARST
America/Cambridge_Bay LMT
America/Cambridge_Bay MST
America/Cambridge_Bay MDT
America/Cambridge_Bay MDDT
America/Cambridge_Bay CST
America/Cambridge_Bay CDT
America/Cambridge_Bay EST
America/Cancun LMT
America/Cancun CST
America/Cancun EST
America/Cancun EDT
America/Cancun CDT
America/Caracas LMT
America/Caracas CMT
America/Caracas VET
America/Cayenne LMT
America/Cayenne GFT
America/Cayman LMT
America/Cayman KMT
America/Cayman EST
America/Chicago LMT
America/Chicago CST
America/Chicago CWT
America/Chicago CDT
America/Chicago EST
America/Chihuahua LMT
America/Chihuahua MST
America/Chihuahua CST
America/Chihuahua CDT
America/Chihuahua MDT
America/Costa_Rica LMT
America/Costa_Rica SJMT
America/Costa_Rica CST
America/Costa_Rica CDT
America/Cuiaba LMT
America/Cuiaba AMT
America/Cuiaba AMST
America/Curacao LMT
America/Curacao ANT
America/Curacao AST
America/Dawson LMT
America/Dawson YST
America/Dawson YDT
America/Dawson YDDT
America/Dawson PST
America/Dawson PDT
America/Dawson_Creek LMT
America/Dawson_Creek PST
America/Dawson_Creek PDT
America/Dawson_Creek MST
America/Denver LMT
America/Denver MST
America/Denver MWT
America/Denver MDT
America/Detroit LMT
America/Detroit CST
America/Detroit EST
America/Detroit EWT
America/Detroit EDT
America/Edmonton LMT
America/Edmonton MST
America/Edmonton MDT
America/El_Salvador LMT
America/El_Salvador CST
America/El_Salvador CDT
America/Ensenada LMT
America/Ensenada MST
America/Ensenada PST
America/Ensenada PDT
America/Ensenada PWT
TZNAME TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
America/Fort_Wayne LMT
America/Fort_Wayne CST
America/Fort_Wayne CWT
America/Fort_Wayne CDT
America/Fort_Wayne EST
America/Fort_Wayne EDT
America/Fortaleza LMT
America/Fortaleza BRT
America/Fortaleza BRST
America/Godthab LMT
America/Godthab WGT
America/Godthab WGST
America/Goose_Bay LMT
America/Goose_Bay NST
America/Goose_Bay NDT
America/Goose_Bay AST
America/Goose_Bay ADT
America/Goose_Bay ADDT
America/Grand_Turk LMT
America/Grand_Turk KMT
America/Grand_Turk EST
America/Grand_Turk EDT
199 rows selected.
SQL>
Select the current time zone
SQL>
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
SQL>
Time change from Chicago to Los Angeles(Central to Pacific)
SQL>
SQL> -- Time change from Chicago to Los Angeles.
SQL> SELECT TO_CHAR(NEW_TIME(TO_DATE("060297 01:00:00 AM", "MMDDYY HH:MI:SS AM"), "CDT","PDT"), "DD-MON-YY HH:MI:SS AM") "Central to Pacific" from DUAL;
Central to Pacific
---------------------
01-JUN-97 11:00:00 PM
SQL>
Timestamp with local time zone as a table column
SQL> create table local_conference_calls (
2 title varchar2(100),
3 phone varchar2(20),
4 place varchar2(100),
5 starts timestamp with local time zone)
6 /
Table created.
SQL>
SQL> insert into local_conference_calls (title, phone, place, starts)
2 values ("Sales Strategy", "212.123.4567", "New York",
3 TIMESTAMP "2001-12-01 15:00:00.000000 EST")
4 /
1 row created.
SQL>
SQL>
SQL> insert into local_conference_calls (title, phone, place, starts)
2 values ("Product Features", "650.123.4567", "San Francisco",
3 TIMESTAMP "2001-12-01 17:00:00.000000 PST")
4 /
1 row created.
SQL>
SQL> insert into local_conference_calls (title, phone, place, starts)
2 values ("Football Highlights", "44 1234 5678", "London",
3 TIMESTAMP "2001-12-01 20:00:00.000000 GMT")
4 /
1 row created.
SQL>
SQL> select * from local_conference_calls;
TITLE PHONE
---------------------------------------------------------------------------------------------------- --------------------
PLACE STARTS
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
Sales Strategy 212.123.4567
New York 01-DEC-01 01.00.00.000000 PM
Product Features 650.123.4567
San Francisco 01-DEC-01 06.00.00.000000 PM
Football Highlights 44 1234 5678
London 01-DEC-01 01.00.00.000000 PM
SQL>
SQL> drop table local_conference_calls;
Table dropped.
SQL>
SQL>
Time zone name
Time Zone Description
AST (ADT) Atlantic Standard (or Daylight Time)
BST (BDT) Bering Standard (or Daylight Time)
CST (CDT) Central Standard (or Daylight Time)
EST (EDT) Eastern Standard (or Daylight Time)
GMT Greenwich Mean Time
HST (HDT) Alaska-Hawaii Standard Time or Daylight Time
MST (MDT) Mountain Standard or Daylight Time
NST Newfoundland Standard Time
PST (PDT) Pacific Standard or Daylight Time
YST (YDT) Yukon Standard or Daylight Time
--
TIME_ZONE to EST and get CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
SQL>
SQL> -- TIME_ZONE to EST and repeat the previous query
SQL>
SQL> ALTER SESSION SET TIME_ZONE = "EST";
Session altered.
SQL>
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP FROM dual;
CURRENT_TIMESTAMP LOCALTIMESTAMP
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
16-SEP-06 08.35.53.046000 PM EST 16-SEP-06 08.35.53.046000 PM
16-SEP-06 05.35.53.046000 PM -07:00
SQL>
SQL>
SQL>