1、教學(xué)目標管理不同時區(qū)的數(shù)據(jù)學(xué)完本節(jié)課之后,你會明白如下知識: 使用數(shù)據(jù)類型相似的一個儲存微少的秒和道時區(qū)日期 使用數(shù)據(jù)類型兩個不同的日期值使用如下日期函數(shù):CURRENT_DATECURRENT_TIMESTAMPLOCALTIMESTAMPDBTIMEZONETZ_OFFSET FROM_TZ TO_TIMESTAMPTO_YM TO_DSERVAL ERVAL SESTIMEZONE EXTRACT數(shù)據(jù)1數(shù)據(jù)2CURRENT_DATE, CURRENT_TIMESTAMP, 和LOCALTIMESTAMPCURRENT_DATE: 從用戶會話中返回當前日期 DATE數(shù)據(jù)類型CURRENT_

2、TIMESTAMP: 從用戶會話中返回當前日期和時間 TIMESTAMP WITH TIME ZONE的數(shù)據(jù)類型LOCALTIMESTAMP: 從用戶會話中返回當前日期和時間 TIMESTAMP的數(shù)據(jù)類型數(shù)據(jù)6TIME_ZONE會話叁數(shù)TIME_ZONE 可以被設(shè)置為:一個絕對的偏移量數(shù)據(jù)庫時區(qū)操作系統(tǒng)本地時區(qū)一個被命名的區(qū)域數(shù)據(jù)5ALTER SES ALTER SES ALTER SES ALTER SESSET TIME_ZONE = -05:00;SET TIME_ZONE = dbtimezone; SET TIME_ZONE = local;SET TIME_ZONE = Ameri

3、ca/New_York;時區(qū)08:0007:0002:0010:0005:00The image represents the time for each time zhen Greenwich time is 12:00.數(shù)據(jù)4課程安排CURRENT_DATE, CURRENT_TIMESTAMP,和 LOCALTIMESTAMPERVAL 數(shù)據(jù)類型使用下列方法:EXTRACTTZ_OFFSETFROM_TZTO_TIMESTAMPTO_YMERVALTO_DSERVAL數(shù)據(jù)3在會話的時區(qū)內(nèi)比較日期和時間在會話的時區(qū)內(nèi)比較日期和時間TIME_ZONE參數(shù)設(shè)置為5:00,然后執(zhí)行下面的SELE

4、CT 語句來比較日期和時間的區(qū)別.查詢結(jié)果:數(shù)據(jù)7數(shù)據(jù)8區(qū)別DATE 和TIMESTAMP不同AB數(shù)據(jù)12ALTER TABLE employees MODIFY hire_date TIMESTAMP;SELECT hire_date FROM employees;- when hire_date is of type DATESELECT hire_date FROM employees;TIMESTAMP 字段數(shù)據(jù)11日期時間字段合法值YEAR4712 to 9999 (excluding year 0)MONTH01 to 12DAY01 to 31HOUR00 to 23MINUTE

5、00 to 59SECOND00 to 59.9(N) where 9(N) is precTIMEZONE_HOUR12 to 14TIMEZONE_MINUTE00 to 59TIMESTAMP 數(shù)據(jù)類型數(shù)據(jù)10數(shù)據(jù)類型字段TIMESTAMP年,月,日,小時,分鐘,秒以及秒的小數(shù)位TIMESTAMP WITH TIME ZONE和TIMESTAMP 類型相同; 還包含:TIMEZONE_HOUR, 和TIMEZONE_MINUTE或 TIMEZONE_REGIONTIMESTAMP WITH LOCAL TIME ZONE和TIMESTAMP 類型相同; 還包含時區(qū)偏移值DBTIMEZON

6、E 和 SESTIMEZONE顯示數(shù)據(jù)庫時區(qū):顯示會話時區(qū):數(shù)據(jù)9SELECT SESTIMEZONE FROM DUAL;SELECT DBTIMEZONE FROM DUAL;ALTER SESSET NLS_DATE_FORMAT = DD-MON-YYYY HH24:MI:SS; ALTER SESSET TIME_ZONE = -5:00;SELECT SESTIMEZONE, CURRENT_DATE FROM DUAL; SELECT SESTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SELECT SESTIMEZONE, LOCALTIMEST

7、AMP FROM DUAL;比較 TIMESTAMP 數(shù)據(jù)類型課程安排CURRENT_DATE, CURRENT_TIMESTAMP,和 LOCALTIMESTAMPERVAL 數(shù)據(jù)類型使用下列方法:EXTRACTTZ_OFFSETFROM_TZTO_TIMESTAMPTO_YMTO_DSERVAL ERVAL數(shù)據(jù)13數(shù)據(jù)14ERVAL DAY TO SECOND 數(shù)據(jù)類型: 范例數(shù)據(jù)18SELECT * FROM lab;CREATE TABLE lab( exp_id number, test_timeERVAL DAY(2) TO SECOND);INSERTO lab VALUES (

8、100012, 90 00:00:00); INSERTO lab VALUES (56098,ERVAL 6 03:30:16 DAY TO SECOND);ERVAL YEAR TO MONTH: 范例數(shù)據(jù)17CREATE TABLE warranty(prod_id number, warranty_timeERVAL YEAR(3) TO MONTH);INSERTO warranty VALUES (123,ERVAL 8 MONTH);INSERTO warranty VALUES (155,ERVAL 200 YEAR(3);INSERTO warranty VALUES (67

9、8, 200-11);SELECT * FROM warranty;ERVAL 字段數(shù)據(jù)16ERVAL FieldValid Values for ervalYEARAnytive or negative egerMONTH00 to 11DAYAnytive or negative egerHOUR00 to 23MINUTE00 to 59SECOND00 to 59.9(N) where 9(N) is precERVAL 數(shù)據(jù)類型ERVAL 數(shù)據(jù)類型被用做兩個日期時間值之間的差.有兩類:Year-monthDay-time間隔的精度是:間隔字段的子集在限定詞中指定數(shù)據(jù)15Da ypeF

10、ieldsERVAL YEAR TO MONTHYear, MonthERVAL DAY TO SECONDDays, Hour, Minute, Second with fractional secondsSELECT * FROM web_orders;INSERTO web_orders values (current_date, current_timestamp + 2);CREATE TABLE web_orders(order_date TIMESTAMP WITH TIME ZONE, delivery_time TIMESTAMP WITH LOCAL TIME ZONE);

11、EXTRACT課程安排CURRENT_DATE, CURRENT_TIMESTAMP,和 LOCALTIMESTAMPERVAL 數(shù)據(jù)類型使用下列方法:EXTRACTTZ_OFFSETFROM_TZTO_TIMESTAMPTO_YMERVALTO_DSERVAL顯示 SYSDATE 的 YEAR 部分為那些職員MANAGER_ID 是100顯示來自 HIRE_DATE 的MONTH 部分.1920數(shù)據(jù)數(shù)據(jù)TO_YMERVAL顯示在 DEPARTMENT_ID為20 的部門中職員雇傭后一年零二個月的日期.數(shù)據(jù)24SELECT hire_date,hire_date + TO_YMERVAL(01

12、-02) AS HIRE_DATE_YMINERVALFROMemployeesWHERE department_id = 20;TO_TIMESTAMP轉(zhuǎn)換字符串2007-03-06 11:00:00作為TIMESTAMP :數(shù)據(jù)23SELECT TO_TIMESTAMP (2007-03-06 11:00:00,YYYY-MM-DD HH:MI:SS)FROM DUAL;FROM_TZ顯示Australia/North時區(qū), TIMESTAMP 為2000-03-28 08:00:00 的TIMESTAMP WITH TIME ZONE 值.數(shù)據(jù)22SELECT FROM_TZ(TIMES

13、TAMP2000-07-12 08:00:00, Australia/North) FROM DUAL;TZ_OFFSET對US/Eastern, Canada/Yukon和Europe/London時區(qū)顯示時區(qū)的偏移量:數(shù)據(jù)21SELECT TZ_OFFSET(US/Eastern),TZ_OFFSET(Canada/Yukon), TZ_OFFSET(Europe/London)FROM DUAL;SELECT last name, hire date,FROM employeesWHERE manager_id = 100;EXTRACT (MONTH FROM HIRE DATE)SE

14、LECTEXTRACT (YEAR FROM SYSDATE)FROM DUAL;TO_DSERVAL夏令時顯示所有的職員顯示在雇傭日期后 100 天零 10 小時的日期.在四月中的第一日 時間從01:59:59 AM 跳到03:00:00 AM. 時間值從02:00:00 AM到02:59:59 AM 是無效的十月份的最后一個日 時間從02:00:00 AM 跳到01:00:01 AM. 時間值從01:00:01 AM到02:00:00 AM 是不明確,因為他們會被經(jīng)過兩次25數(shù)據(jù)26數(shù)據(jù)練習 5: 概述This practice covers using the datetime functions.這個練習包括使用datetime (日期)函數(shù).數(shù)據(jù)28總結(jié)在這課中,你應(yīng)該學(xué)會如下方法:CURRENT_DATECURRENT_TIMESTAMPLOCALTIMESTAMPDBTIMEZON