1、nn學(xué)完本節(jié)課之后,你會(huì)明白如下知識(shí)使用數(shù)據(jù)類型相似的一微少的秒和道時(shí)區(qū)日使學(xué)完本節(jié)課之后,你會(huì)明白如下知識(shí)使用數(shù)據(jù)類型相似的一微少的秒和道時(shí)區(qū)日使用數(shù)據(jù)類兩個(gè)不同的日期使用如下日期函數(shù)nCURRENT_DATE,使用下列方法,CURRENT_DATE,使用下列方法,nThe image represents the time each time z timeThe image represents the time each time z timeis12:00.hen nTIME_ZONETIME_ZONE可以被設(shè)置為一偏移數(shù)據(jù)庫(kù)時(shí)TIME_ZONETIME_ZONE可以被設(shè)置為一偏移數(shù)據(jù)

2、庫(kù)時(shí)一個(gè)被命名的區(qū)nALTERSES ALTERSES ALTERSES ALTER SET TIME_ZONE = -SET TIME_ZONE = dbtimezone; SET TIME_ZONE = local;SET TIME_ZONE = CURRENT_DATE, CURRENT_TIMEST , 從用戶會(huì)話中返回當(dāng)前日DATE數(shù)據(jù)類:CURRENT_DATE, CURRENT_TIMEST , 從用戶會(huì)話中返回當(dāng)前日DATE數(shù)據(jù)類:從用戶會(huì)話中返回當(dāng)前日期和時(shí)WITH TIME ZONE的數(shù)據(jù)類:從用戶會(huì)話中返回當(dāng)前日期和時(shí)的數(shù)據(jù)類n期和時(shí)間的區(qū)別nALTER SET NLS_

3、DATE_FORMAT = DD-MON-YYYY HH24:MI:SS; ALTER SESSET TIME_ZONE = -5:00;SELECT TIMEZONE, 期和時(shí)間的區(qū)別nALTER SET NLS_DATE_FORMAT = DD-MON-YYYY HH24:MI:SS; ALTER SESSET TIME_ZONE = -5:00;SELECT TIMEZONE, CURRENT_DATE FROM SELECT TIMEZONE, FROM SELECT TIMEZONE, FROM 查詢結(jié)果n查詢結(jié)果nDBTIMEZONE顯示數(shù)據(jù)庫(kù)時(shí)區(qū)顯示會(huì)話時(shí)DBTIMEZONE顯示

4、數(shù)據(jù)庫(kù)時(shí)區(qū)顯示會(huì)話時(shí)區(qū)nSELECT TIMEZONE FROM SELECT DBTIMEZONE FROM n數(shù)據(jù)類字年,月,日n數(shù)據(jù)類字年,月,日,小時(shí),分鐘,秒以及秒的小數(shù)WITHTIME 和類型相同; 還包含TIMEZONE_HOUR, 和 TIMEZONE_MINUTE或 WITHLOCAL TIMEZONE類型相同; 還包含時(shí)區(qū)n日期時(shí)間字合法4712to9999(excludingyearn日期時(shí)間字合法4712to9999(excludingyear01 to 01 to 00 to 00 to 00 to 59.9(N) where 9(N) is 12 to 00 to

5、區(qū)別DATE和ABnALTER TABLE employees MODIFY hire_date TIMESTSELECThire_date FROM employees;-區(qū)別DATE和ABnALTER TABLE employees MODIFY hire_date TIMESTSELECThire_date FROM employees;-whenhire_dateis of type DATESELECThire_date FROM employees;nSELECT * FROM O web_orders values (current_date, current_timest+2)

6、;CREATETABLEnSELECT * FROM O web_orders values (current_date, current_timest+2);CREATETABLE(order_date TIMESTWITH TIME ZONE, delivery_time TIMESTWITHLOCALTIMEZONE);CURRENT_DATE,ERVAL 數(shù)據(jù)類使用下列方法,CURRENT_DATE,ERVAL 數(shù)據(jù)類使用下列方法,nERVAL兩個(gè)日期時(shí)間值之間的差有兩類Year-Day-間隔的精度是間字段ERVAL兩個(gè)日期時(shí)間值之間的差有兩類Year-Day-間隔的精度是間字段的子在

7、限定詞中指nERVAL YEAR TO Year,ERVAL DAY TO Days, Hour, Minute, Second with fractional secondsERVALnERVALValidValuesitiveorERVALnERVALValidValuesitiveor00 to itiveor00 to 00 to 00 to 59.9(N) where 9(N) is ERVALYEARTOMONTH: nERVALYEARTOMONTH: nCREATE TABLE (prod_id number, warranty_timeERVAL YEAR(3) TO O w

8、arranty VALUES ERVAL 8 O warranty VALUES (155,ERVAL200 O warranty VALUES (678, 200-SELECT * FROM ERVALDAYTOSECOND 數(shù)據(jù)類型: nERVALDAYTOSECOND 數(shù)據(jù)類型: nSELECT * FROM CREATE TABLE ( exp_id number, ERVAL DAY(2) TO O lab VALUES (100012, 90 00:00:00); O lab VALUES (56098,ERVAL 6 03:30:16 DAY TO CURRENT_DATE,使用

9、下列方法,CURRENT_DATE,使用下列方法,nnSELECT last name,nSELECT last name, hire FROM WHERE manager_id = EXTRACT (MONTH FROM EXTRACT (YEAR FROM FROM Canada/Yukon和Europe/LondonCanada/Yukon和Europe/London時(shí)區(qū)nSELECT FROM 顯示Australia/North時(shí)區(qū)WITHTIMEZONE值顯示Australia/North時(shí)區(qū)WITHTIMEZONE值的nSELECT 2000-07-12 08:00:00, Aus

10、tralia/North) FROM DUAL;:轉(zhuǎn)換字符串2007-03-11:00:00作為nSELECT (2007-03-06:轉(zhuǎn)換字符串2007-03-11:00:00作為nSELECT (2007-03-06 YYYY-MM-DD FROM 日期nSELECT 日期nSELECT hire_date + TO_YMERVAL(01-02) AS WHERE department_id = 小時(shí)的日期nSELECT TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss) hire_date, TO_CHAR(hire_date +小時(shí)的日期nSELECT TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss) hire_date, TO_CHAR(hire_date +ERVAL(100 mm-dd-yy:hh:mi:ss) hiredate2 FROM employees;日時(shí)間從01:59:59 AM 跳到03:00:00 日時(shí)間從01:59:59 AM 跳到03:00:00 時(shí)間值從02:00:00 AM到02:59:59 AM 是無(wú)效日時(shí)間從