OCP課程18：SQL之管理不同時區下的資料
課程目標：
- TZ_OFFSET
- FROM_TZ
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_YMINTERVAL
- TO_DSINTERVAL
- CURRENT_DATE
- CURRENT_TIMESTAMP
- LOCALTIMESTAMP
- DBTIMEZONE
- SESSIONTIMEZONE
- EXTRACT
1、時區
時區是地理上的概念，把全球分成24個時區，每一個小時一個時區，定義了某一時刻不同地點的時間。
檢視作業系統的時區
[ ~]# date -R
Sat, 07 Nov 2015 11:08:16 +0800
如何計算區時
計算的區時=已知區時-（已知區時的時區-要計算區時的時區），（注：東時區為正，西時區為負）。
下面舉例加以說明：
例1：已知東京（東九區）時間為5月1日12:00，求北京（東八區）的區時？
北京時間=12:00-（9-8）=11:00（即北京時間為5月1日11:00）。
例2：已知北京時間為5月1日12:00，求倫敦（中時區）的區時？
倫敦時間=12:00-（8-0)=4:00（即倫敦時間為5月1日4:00）。
例3：已知北京時間為5月1日12:00，求紐約（西五區）的區時。
紐約時間=12:00-[8-(-5）]=-1:00+24:00-1天=23:00（即紐約時間為4月30日的23:00）。（注：當算出的區時為負數時，應加上24:00，日期減一天，即從5月1日變為4月30日）。
例4：已知紐約時間為5月1日12:00，求東京的區時？
東京時間=12:00-[(-5）-9]=26:00-24:00+1天=2:00）即東京時間為5月2日2:00）。（注：當算出的區時大於或等於24:00時，應減去24:00，日期加一天，即從5月1日變為5月2日）。
2、time_zone會話引數
Oracle 的時區可以分為兩種，一種是資料庫的時區，一種是 session 時區。資料庫的時區在建立資料庫時可以透過在 create database 語句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 來指定。建立之後，可以透過 alter database 來修改。Session 的時區是根據客戶端的時區來決定的，連線以後也可以透過alter session set time_zone來改變，改變的值可以設定為：
- 一個絕對的偏移值
- 資料庫時區
- 作業系統的時區
- 時區的命名區域
例子：使用sessiontimezone函式檢視當前session時區
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
例子：使用dbtimezone函式檢視資料庫的時區
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
例子：修改當前session的時區為-5：00時區
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00
例子：修改當前session的時區為資料庫的時區
SQL> alter session set time_zone=dbtimezone;
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
例子：修改當前session的時區為本地作業系統的時區
SQL> alter session set time_zone=local;
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
例子：修改當前session的時區為某一個時區命名區域
透過v$timezone_names查詢時區命名區域
SQL> select tzname from v$timezone_names where lower(tzname) like '%york%';
TZNAME
--------------------
America/New_York
SQL> alter session set time_zone='America/New_York';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
America/New_York
3、current_date函式
返回當前會話下面不同時區對應的日期和時間（資料型別為date）
SQL> select current_date from dual;
CURRENT_DATE
------------
06-NOV-15
如果要使返回的資訊包含時間，需要修改nls_date_format引數
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select current_date from dual;
CURRENT_DATE
-----------------------
06-nov-2015 23:04:29
例子：修改時區後檢視current_date的值
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE
-------------------- ----------------------- -----------------------
-05:00 07-nov-2015 13:46:20 07-nov-2015 00:46:20
SQL> alter session set time_zone='+08:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE
-------------------- ----------------------- -----------------------
+08:00 07-nov-2015 13:47:10 07-nov-2015 13:47:10
4、current_timestamp函式
返回當前會話下面不同時區對應的日期、時間（包含微秒及上下午）及時區（格式固定，不能修改，資料型別為timestamp with time zone）
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_timestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP
-------------------- ----------------------- ----------------------------------------
-05:00 07-nov-2015 13:49:41 07-NOV-15 12.49.41.656386 AM -05:00
SQL> alter session set time_zone='+08:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_timestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_TIMESTAMP
-------------------- ----------------------- ----------------------------------------
+08:00 07-nov-2015 13:52:00 07-NOV-15 01.52.00.084663 PM +08:00
5、localtimestamp函式
返回當前會話下面不同時區對應的日期、時間（包含微秒及上下午，不帶時區）（資料型別為timestamp）
SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
-------------------- ----------------------- ----------------------- ---------------------------------------- ------------------------------
+08:00 07-nov-2015 13:59:24 07-nov-2015 13:59:24 07-NOV-15 01.59.24.639690 PM +08:00 07-NOV-15 01.59.24.639690 PM
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select sessiontimezone,sysdate,current_date,current_timestamp,localtimestamp from dual;
SESSIONTIMEZONE SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
-------------------- ----------------------- ----------------------- ---------------------------------------- ------------------------------
-05:00 07-nov-2015 13:59:47 07-nov-2015 00:59:47 07-NOV-15 12.59.47.116923 AM -05:00 07-NOV-15 12.59.47.116923 AM
6、timestamp資料型別
- 是date資料型別的擴充套件
- 可以儲存微秒
- 三種型別：timestamp，timestamp with time zone，timestamp with local time zone
timestamp型別的值域
![clipboard[1]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692957FUKU.png "clipboard[1]")
（1）timestamp型別
timestamp除了年月日時分秒外，還包含微秒，預設精度是6位，最高可以到9位。
例子：比較date資料型別和timestamp資料型別的不同
SQL> conn hr/hr
Connected.
SQL> drop table emp5;
Table dropped.
SQL> create table emp5 as select * from employees;
Table created.
SQL> select hire_date from emp5 where employee_id=100;
HIRE_DATE
------------
17-JUN-03
SQL> alter table emp5 modify hire_date timestamp;
Table altered.
SQL> select hire_date from emp5 where employee_id=100;
HIRE_DATE
---------------------------------------------------------------------------
17-JUN-03 12.00.00.000000 AM
（2）timestamp with time zone型別
timestamp with time zone除了包含timestamp的資訊外，還帶有時區。
![clipboard[2]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692958OEet.png "clipboard[2]")
例子：建立一個表web_orders，儲存來自全球的訂單資訊，包含一個timestamp with time zone型別欄位，並插入資料
SQL> create table web_orders(
2 ord_id number primary key,
3 order_date timestamp with time zone);
Table created.
SQL> desc web_orders;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORD_ID NOT NULL NUMBER
ORDER_DATE TIMESTAMP(6) WITH TIME ZONE
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- ----------------------------------------
+08:00 07-NOV-15 10.15.54.762046 PM +08:00
SQL> insert into web_orders values(1,current_timestamp);
1 row created.
SQL> select * from web_orders;
ORD_ID ORDER_DATE
---------- ----------------------------------------
1 07-NOV-15 10.16.46.396682 PM +08:00
SQL> commit;
Commit complete.
另外開一個視窗，模擬來自另外一個地方的訂單
SQL> conn hr/hr
Connected.
修改時區
SQL> alter session set time_zone='-05:00';
Session altered.
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
07-NOV-15 09.22.49.860132 AM -05:00
插入資料
SQL> insert into web_orders values(2,current_timestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from web_orders;
ORD_ID ORDER_DATE
---------- ----------------------------------------
1 07-NOV-15 10.16.46.396682 PM +08:00
2 07-NOV-15 09.23.46.179299 AM -05:00
可以看到時區資訊及對應的日期時間儲存到記錄裡面了，在任何客戶端查詢都不會根據客戶端的時區而變化。
（3）timestamp with local time zone
不儲存時區資訊，時間根據客戶端的時區變化而變化
![clipboard[3]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692959mrmg.png "clipboard[3]")
例子：建立一個表，儲存全球的快遞投遞時間資訊，包含一個timestamp with local time zone型別欄位，並插入資料
SQL> create table shipping(delivery_time timestamp with local time zone);
Table created.
SQL> desc shipping;
Name Null? Type
----------------------------------------- -------- ----------------------------
DELIVERY_TIME TIMESTAMP(6) WITH LOCAL TIME
ZONE
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
----------------------------------------
07-NOV-15 10.42.50.916509 PM +08:00
SQL> insert into shipping values(current_timestamp);
SQL> select * from shipping;
DELIVERY_TIME
---------------------------------------------------------------------------
07-NOV-15 10.43.13.949702 PM
SQL> alter session set time_zone='+06:00';
Session altered.
SQL> select * from shipping;
DELIVERY_TIME
---------------------------------------------------------------------------
07-NOV-15 08.43.13.949702 PM
7、interval資料型別
儲存兩個日期時間的間隔，有以下兩類：
![clipboard[4]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_14506929607E8o.png "clipboard[4]")
interval型別的值域
![clipboard[5]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692960G7SS.png "clipboard[5]")
（1）interval year to month資料型別
year後面可以帶精度，預設是2位
![clipboard[6]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692961Dej5.png "clipboard[6]"){kind=small}
下面是一些示例
![clipboard[7]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692962WMXi.png "clipboard[7]")
例子：建立一個表，儲存產品的保質期
SQL> create table warranty(
2 prod_id number,
3 warranty_time interval year(3) to month);
Table created.
SQL> desc warranty;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NUMBER
WARRANTY_TIME INTERVAL YEAR(3) TO MONTH
SQL> insert into warranty values(123,interval '8' month);
1 row created.
SQL> insert into warranty values(155,interval '200' year(3));
1 row created.
SQL> insert into warranty values(678,'200-11');
1 row created.
SQL> select * from warranty;
PROD_ID WARRANTY_TIME
---------- --------------------
123 +000-08
155 +200-00
678 +200-11
（2）interval day to second資料型別
day後面可以帶精度，預設是2位
![clipboard[8]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692962t2Z5.png "clipboard[8]"){kind=small}
下面是一些示例
![clipboard[9]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692963AAjZ.png "clipboard[9]")
例子 ：建立一個表，儲存實驗的間隔時間
SQL> create table lab(
2 exp_id number,
3 test_time interval day(2) to second);
Table created.
SQL> desc lab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EXP_ID NUMBER
TEST_TIME INTERVAL DAY(2) TO SECOND(6)
SQL> insert into lab values(100012,'90 00:00:00');
1 row created.
SQL> insert into lab values(56098,interval '6 03:30:16' day to second);
1 row created.
SQL> select * from lab;
EXP_ID TEST_TIME
---------- ------------------------------
100012 +90 00:00:00.000000
56098 +06 03:30:16.000000
間隔型別單獨沒有什麼用處，一般是與日期時間進行運算
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select sysdate ,sysdate+test_time from lab;
SYSDATE SYSDATE+TEST_TIME
----------------------- -----------------------
08-NOV-2015 20:06:19 06-FEB-2016 20:06:19
08-NOV-2015 20:06:19 14-NOV-2015 23:36:35
8、extract函式
從時間日期或者間隔值中抽取特定的時間日期值
![clipboard[10]](http://img.blog.itpub.net/blog/attachment/201512/21/28536251_1450692964zeET.png "clipboard[10]")
例子：查詢當前的年份
SQL> select sysdate,extract(year from sysdate) from dual;
SYSDATE EXTRACT(YEARFROMSYSDATE)
----------------------- ------------------------
08-NOV-2015 20:16:05 2015
也可以使用to_char函式
SQL> select sysdate,to_char(sysdate,'yyyy') from dual;
SYSDATE TO_C
----------------------- ----
08-NOV-2015 20:16:45 2015
例子：查詢人員入職的月份
SQL> select last_name,hire_date,extract(month from hire_date) from employees where manager_id=100;
LAST_NAME HIRE_DATE EXTRACT(MONTHFROMHIRE_DATE)
------------------------- ----------------------- ---------------------------
Hartstein 17-FEB-2004 00:00:00 2
也可以使用to_char函式
SQL> select last_name,hire_date,to_char(hire_date,'mm') from employees where manager_id=100;
LAST_NAME HIRE_DATE TO
------------------------- ----------------------- --
Hartstein 17-FEB-2004 00:00:00 02
9、tz_offset函式
使用該函式將時區區域命名轉換成時區值
SQL> select tz_offset('US/Eastern') from dual;
TZ_OFFS
-------
-05:00
SQL> select tz_offset('Canada/Yukon') from dual;
TZ_OFFS
-------
-08:00
SQL> select tz_offset('Europe/London') from dual;
TZ_OFFS
-------
+00:00
前面講了透過v$timezone_names資料字典檢視查詢有哪些時區命名區域
SQL> select * from v$timezone_names where tzname like '%Chongqing%';
TZNAME TZABBREV
-------------------- --------------------
Asia/Chongqing LMT
SQL> select tz_offset('Asia/Chongqing') from dual;
TZ_OFFS
-------
+08:00
10、from_tz函式
將timestamp轉換成timestamp with time zone，這個函式用得很少
SQL> select from_tz(timestamp '2000-03-28 08:00:00','3:00') from dual;
FROM_TZ(TIMESTAMP'2000-03-2808:00:00','3:00')
---------------------------------------------------------------------------
28-MAR-00 08.00.00.000000000 AM +03:00
SQL> select from_tz(timestamp '2000-03-28 08:00:00','Australia/North') from dual;
FROM_TZ(TIMESTAMP'2000-03-2808:00:00','AUSTRALIA/NORTH')
---------------------------------------------------------------------------
28-MAR-00 08.00.00.000000000 AM AUSTRALIA/NORTH
11、to_timestamp和to_timestamp_tz函式
使用to_timestamp函式將字串轉換成timestamp型別，使用to_timestamp_tz函式將字串轉換成timestamp with time zone型別，帶時區。
例子：將字串轉換成timestamp型別
SQL> select to_timestamp('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;
TO_TIMESTAMP('2000-12-0111:00:00','YYYY-MM-DDHH:MI:SS')
---------------------------------------------------------------------------
01-DEC-00 11.00.00.000000000 AM
例子：將字串轉換成timestamp with time zone型別
SQL> select to_timestamp_tz('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
TO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
---------------------------------------------------------------------------
01-DEC-99 11.00.00.000000000 AM -08:00
12、to_yminterval函式
將字串轉換成年月間隔型別
例子：將入職時間加上1年2個月
SQL> select hire_date,hire_date+to_yminterval('01-02') as hire_date_yminterval from employees where department_id=20;
HIRE_DATE HIRE_DATE_YMINTERVAL
----------------------- -----------------------
17-FEB-2004 00:00:00 17-APR-2005 00:00:00
13、to_dsinterval函式
將字串轉換成天秒間隔型別
例子 ：將入職時間加上100天10小時
SQL> select last_name,to_char(hire_date,'mm-dd-yy hh:mi:ss') hire_date,to_char(hire_date+to_dsinterval('100 10:00:00'),'mm-dd-yy hh:mi:ss') hiredate2 from employees;
LAST_NAME HIRE_DATE HIREDATE2
------------------------- ----------------- -----------------
OConnell 06-21-07 12:00:00 09-29-07 10:00:00
14、相關習題：
（1）You need to create a table for a banking application with the following considerations: 1) You want a column in the table to store the duration of the credit period. 2) The data in the columnshould be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions. 4) The maximum period of the credit provision in the application is 30 days. 5) The interest has to be calculated for the number of days an individual has taken a credit for. Which data type would you use for such a column in the table？
A.INTERVAL YEAR TO MONTH
B.INTERVAL DAY TO SECOND
C.TIMESTAMP WITH TIME ZONE
D.TIMESTAMP WITH LOCAL TIME ZONE
答案：B
（2）Given below is a list of datetime data types and examples of values stored in them in a random order: Datatype Example 1)INTERVAL YEAR TO MONTH a) '2003?04?15 8:00:00 ?8:00' 2)TIMESTAMP WITH LOCAL TIME ZONE b) '+06 03:30:16.000000' 3)TIMESTAMP WITH TIME ZONE c) '17?JUN?03 12.00.00.000000 AM' 4)INTERVAL DAY TO SECOND d) '+02?00' Identify the option that correctly matches the data types with the values.
A.1-?d, 2?-c, 3-?a, 4-?b
B.1-?b, 2-?a, 3-?c, 4?-d
C.1?-b, 2-?a, 3?-d, 4-?c
D.1-?d, 2?-c, 3-?b, 4?-a
答案：A
（3）View the Exhibit and examine the description of the PRODUCT_INFORMATION table. You want to display the expiration date of the warranty for a product. Which SQL statement would you execute?
A.SELECT product_id, SYSDATE + warranty_period FROM product_information;
B.SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information;
C.SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information;
D.SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information;
答案：A
（4）Evaluate the SQL statements: CREATE TABLE new_order (orderno NUMBER(4), booking_date TIMESTAMP WITH LOCAL TIME ZONE)；The database is located in San Francisco where the time zone is -8:00. The user is located in New York where the time zone is -5:00. A New York user inserts the following record: INSERT INTO new_order VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00 )； Which statement is true ？
A.When the New York user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'
B.When the New York user selects the row, booking_date is displayed as '2007-05-10 6.00.00.000000 -5:00'.
C.When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000'
D.When the San Francisco user selects the row, booking_date is displayed as '007-05-10 3.00.00.000000 -8:00'
答案：C
（5）Which three statements are true？(Choose three.)
A.Only one LONG column can be used per table.
B.A TIMESTAMP data type column stores only time values with fractional seconds.
C.The BLOB data type column is used to store binary data in an operating system file.
D.The minimum column width that can be specified for a varchar2 data type column is one.
E.The value for a CHAR data type column is blank-padded to the maximum defined column width.
答案：ADE
（6）Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command？(Choose three.)
A.'os'
B.local
C.'-8:00'
D.dbtimezone
E.'Australia'
答案：BCD
（7）Evaluate the following query: SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR TO SECOND FROM dual；What is the correct output of the above query？
A.+25-00 , +54-02, +00 11:12:10.123457
B.+00-300, +54-02, +00 11:12:10.123457
C.+25-00 , +00-650, +00 11:12:10.123457
D.+00-300 , +00-650, +00 11:12:10.123457
答案：A
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-1876334/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle OCP(17)：管理不同時區的資料Oracle
- OCP課程13：SQL之使用資料字典檢視管理物件SQL物件
- OCP課程39：管理Ⅰ之移動資料
- OCP課程16：SQL之處理大資料SQL大資料
- OCP課程4：SQL之限制和排序資料SQL排序
- OCP課程60：管理Ⅰ之管理資料庫空間資料庫
- OCP課程26：管理Ⅰ之管理資料庫例項資料庫
- OCP課程56：管理II之SQL調優SQL
- OCP課程15：SQL之管理模式物件SQL模式物件
- OCP課程24：管理Ⅰ之資料庫安裝資料庫
- OCP課程61：管理II之複製資料庫資料庫
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- OCP課程50：管理II之診斷資料庫資料庫
- 管理不同時區的資料
- OCP課程23：管理Ⅰ之資料庫體系結構資料庫
- OCP課程53：管理II之使用閃回資料庫資料庫
- OCP課程27：管理Ⅰ之管理ASM例項ASM
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- OCP課程19：SQL之使用子查詢檢索資料SQL
- OCP課程54：管理II之管理記憶體記憶體
- OCP課程21：SQL之正規表示式SQL
- OCP課程12：SQL之建立其他模式物件SQL模式物件
- OCP課程9：SQL之使用集合運算子SQL
- OCP課程8：SQL之使用子查詢SQL
- OCP課程6：SQL之使用組函式SQL函式
- OCP課程3：SQL之使用SELECT語句檢索資料SQL
- OCP課程5：SQL之使用單行函式SQL函式
- OCP課程17：SQL之透過分組相關資料產生報告SQL
- 陳安之課程資料下載
- OCP課程58：管理II之自動任務
- OCP課程42：管理II之核心概念和工具
- OCP課程45：管理II之備份設定
- OCP課程14：SQL之控制使用者訪問SQL
- OCP課程20：SQL之分層查詢SQL
- OCP課程7：SQL之多表查詢SQL
- OCP課程48：管理II之使用RMAN執行恢復
- OCP課程51：管理II之使用閃回技術1
- OCP課程52：管理II之使用閃回技術2