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)
<source lang="sql">
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>
</source>
Obtaining Time Zone Names
<source lang="sql">
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>
</source>
Select the current time zone
<source lang="sql">
SQL> SQL> select dbtimezone from dual; DBTIME
+00:00 SQL>
</source>
Time change from Chicago to Los Angeles(Central to Pacific)
<source lang="sql">
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>
</source>
Timestamp with local time zone as a table column
<source lang="sql">
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>
</source>
Time zone name
<source lang="sql">
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 --
</source>
TIME_ZONE to EST and get CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
<source lang="sql">
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>
</source>
