1.Datetime數據類型
DATE：最早的日期時間類型，精確到秒
TIMESTAMP: 比DATE更精確的日期時間型，精確到十億分之一秒（當然能否精確到那么小有賴于操作系統和硬件）
TIMESTAMP WITH TIME ZONE:存儲了TIMESTAMP和時區
TIMESTAMP WITH LOCAL TIME ZONE:會將輸入的日期時間轉換為當前時區的日期時間。
從下圖可以看出幾個類型之間的異同：
使用實例：
DECLARE
hire_date TIMESTAMP (0) WITH TIME ZONE;
todays_date CONSTANT DATE := SYSDATE;
pay_date TIMESTAMP DEFAULT TO_TIMESTAMP('20050204','YYYYMMDD');
BEGIN
NULL;
END;
/
2.取得日期和時間
函數 時區 返回類型
-----------------------------------------------------------------
CURRENT_DATE SESSION DATE
CURRENT_TIMESTAMP SESSION TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP SESSION TIMESTAMP
SYSDATE SERVER DATE
SYSTIMESTAMP SERVER TIMESTAMP WITH TIME ZONE
-----------------------------------------------------------------
3.Interval（時間長度）類型
兩種類型
var_name INTERVAL YEAR [(year_precision)] TO MONTH
var_name INTERVAL DAY [(day_precision)] TO SECOND [(frec_sec_prec)]
year_precision是年的精度，范圍為0到4數字，缺省值為2.也就是說最大的取值范圍是9999年這么長的時間間隔。
day_precision是日的精度，范圍是0到9位數字,也就是說最大的取值范圍999999999天這么長的時間間隔。
第一種表示從年到月為單位的時間間隔，第二種表示從日到表(微秒甚至更小)為單位的時間間隔。
提供者兩種類型主要是為了對時間的量的計算方便。
用例如下：
DECLARE
start_date TIMESTAMP;
end_date TIMESTAMP;
service_interval INTERVAL YEAR TO MONTH;
years_of_service NUMBER;
months_of_service NUMBER;
BEGIN
--Normally, we would retrieve start and end dates from a database.
start_date := TO_TIMESTAMP('29-DEC-1988','dd-mon-yyyy');
end_date := TO_TIMESTAMP ('26-DEC-1995','dd-mon-yyyy');
--Determine and display years and months of service:
service_interval := (end_date - start_date) YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE(service_interval);
--Use the new EXTRACT function to grab individual
--year and month components.
years_of_service := EXTRACT(YEAR FROM service_interval);
months_of_service := EXTRACT(MONTH FROM service_interval);
DBMS_OUTPUT.PUT_LINE(years_of_service || ' years and '
|| months_of_service || ' months');
END;
CREATE OR REPLACE FUNCTION expiration_date (
good_for_period_in IN INTERVAL YEAR TO MONTH
)
RETURN TIMESTAMP
IS
bad_month EXCEPTION;
PRAGMA EXCEPTION_INIT (bad_month, -1839);
todays_date TIMESTAMP;
result_date TIMESTAMP;
BEGIN
todays_date := TRUNC(SYSTIMESTAMP);
LOOP
BEGIN
result_date := todays_date + good_for_period_in;
EXCEPTION
WHEN bad_month THEN
todays_date := todays_date - INTERVAL '1' DAY;
WHEN OTHERS THEN
RAISE;
END;
EXIT WHEN result_date IS NOT NULL;
END LOOP;
RETURN result_date;
END;
4.日期時間型數據的類型轉換
從string到datetime
DECLARE
birthdate DATE;
BEGIN
birthdate := TO_DATE('15-Nov-1961','dd-mon-yyyy');
END;
上述TO_DATE函數的第一個參數表示日期值，第二個參數表示日期格式，該格式參照NLS_DATE_FORMAT參數的設置。
更多的函數：
TO_DATE( string[, format_mask[, nls_language]])
TO_DATE( number[, format_mask[, nls_language]])
TO_TIMESTAMP( string[, format_mask[, nls_language]])
TO_TIMESTAMP_TZ( string[, format_mask[, nls_language]])
舉例如下：
DECLARE
a DATE;
b TIMESTAMP;
c TIMESTAMP WITH TIME ZONE;
d TIMESTAMP WITH LOCAL TIME ZONE;
BEGIN
a := TO_DATE('12/26/2005','mm/dd/yyyy');
b := TO_TIMESTAMP('24-Feb-2002 09.00.00.50 PM');
c := TO_TIMESTAMP_TZ('06/2/2002 09:00:00.50 PM EST',
'mm/dd/yyyy hh:mi:ssxff AM TZD');
d := TO_TIMESTAMP_TZ('06/2/2002 09:00:00.50 PM EST',
'mm/dd/yyyy hh:mi:ssxff AM TZD');
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);
END;
輸出如下:
26-DEC-05
24-FEB-02 09.00.00.500000 PM
02-JUN-02 09.00.00.500000 PM -05:00
02-JUN-02 09.00.00.500000 PM
從datetime到string
FUNCTION TO_CHAR
(date_in IN DATE
[, format_mask IN VARCHAR2
[, nls_language IN VARCHAR2]])
RETURN VARCHAR2
參數說明如下，
date_in表示要轉換的日期
format_mask 轉換后的格式（該格式請參考附錄中的格式參考），缺省以NLS_DATE_FORMAT為準
nls_language 語言
例子如下：
TO_CHAR (SYSDATE, 'Month DD, YYYY') --> 'February 05, 1994'
TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') --> 'February 5, 1994'
TO_CHAR (SYSDATE, 'MON DDth, YYYY') --> 'FEB 05TH, 1994'
TO_CHAR (SYSDATE, 'fmMon DDth, YYYY') --> 'Feb 5TH, 1994'
TO_CHAR (A_TIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM')
--> a value like: 2002-02-19 01:52:00.123457000 PM -05:00
DECLARE
A TIMESTAMP WITH TIME ZONE;
BEGIN
A := TIMESTAMP '2002-02-19 13:52:00.123456789 -5:00';
DBMS_OUTPUT.PUT_LINE(TO_CHAR(a,'YYYY-MM-DD HH:MI:SS.FF6 AM TZH:TZM'));
END;
5.日期時間的字面表示
DATE 'YYYY-MM-DD'
TIMESTAMP 'YYYY-MM-DD HH:MI:SS[.FFFFFFFFF] [{+|-}HH:MI]'
舉例：
DECLARE
a TIMESTAMP WITH TIME ZONE;
b TIMESTAMP WITH TIME ZONE;
c TIMESTAMP WITH TIME ZONE;
d TIMESTAMP WITH TIME ZONE;
e DATE;
BEGIN
--Two digits for fractional seconds
a := TIMESTAMP '2002-02-19 11:52:00.00 -05:00';
--Nine digits for fractional seconds, 24-hour clock, 14:00 = 2:00 PM
b := TIMESTAMP '2002-02-19 14:00:00.000000000 -5:00';
--No fractional seconds at all
c := TIMESTAMP '2002-02-19 13:52:00 -5:00';
--No time zone, defaults to session time zone
d := TIMESTAMP '2002-02-19 13:52:00';
--A date literal
e := DATE '2002-02-19';
END;
6.INTERVAL類型的類型轉換
下表是INTERVAL中的單位
Name Description
------------------------------------------
YEAR Some number of years, ranging from 1 through 999,999,999
MONTH Some number of months, ranging from 0 through 11
DAY Some number of days, ranging from 0 to 999,999,999
HOUR Some number of hours, ranging from 0 through 23
MINUTE Some number of minutes, ranging from 0 through 59
SECOND Some number of seconds, ranging from 0 through 59.999999999
------------------------------------------
轉換NUMBER到INTERVAL
DECLARE
A INTERVAL YEAR TO MONTH;
BEGIN
A := NUMTOYMINTERVAL (10.5,'Year');
DBMS_OUTPUT.PUT_LINE(A);
END;
DECLARE
A INTERVAL DAY TO SECOND;
BEGIN
A := NUMTODSINTERVAL (1440,'Minute');
DBMS_OUTPUT.PUT_LINE(A);
END;
轉換string到INTERVAL
TO_YMINTERVAL('Y-M')
該函數轉換一個字符串到INTERVAL YEAR TO MONTH,其中Y表示年的數量，M表示月的數量
TO_DSINTERVAL('D HH:MI:SS')
該函數轉換一個字符串到INTERVAL DAY TO SECOND,其中D表示天數，HH表示小時數，MI表示分鐘數，SS表示秒數
舉例如下：
DECLARE
A INTERVAL YEAR TO MONTH;
B INTERVAL DAY TO SECOND;
C INTERVAL DAY TO SECOND;
BEGIN
A := TO_YMINTERVAL('40-3'); --my age
B := TO_DSINTERVAL('10 1:02:10');
C := TO_DSINTERVAL('10 1:02:10.123'); --fractional seconds
END;
格式化INTERVAL的顯示格式
DECLARE
A INTERVAL YEAR TO MONTH;
BEGIN
A := INTERVAL '40-3' YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(A,'YY "Years" and MM "Months"'));
END;
輸出結果：
+000040-03
DECLARE
A INTERVAL YEAR TO MONTH;
BEGIN
A := INTERVAL '40-3' YEAR TO MONTH;
DBMS_OUTPUT.PUT_LINE(
EXTRACT(YEAR FROM A) || ' Years and '
|| EXTRACT(MONTH FROM A) || ' Months'
);
END;
輸出結果：
40 Years and 3 Months
7.INTERVAL的字面表示
INTERVAL 'character_representation' start_element TO end_element
舉例如下：
DECLARE
A INTERVAL YEAR TO MONTH;
B INTERVAL YEAR TO MONTH;
C INTERVAL DAY TO SECOND;
D INTERVAL DAY TO SECOND;
BEGIN
/* Some YEAR TO MONTH examples */
A := INTERVAL '40-3' YEAR TO MONTH;
B := INTERVAL '40' YEAR;
/* Some DAY TO SECOND examples */
C := INTERVAL '10 1:02:10.123' DAY TO SECOND;
/* Fails in Oracle9i, Release 1 because of a bug */
--D := INTERVAL '1:02' HOUR TO MINUTE;
/* Following are two workarounds for defining intervals,
such as HOUR TO MINUTE, that represent only a portion of the
DAY TO SECOND range. */
SELECT INTERVAL '1:02' HOUR TO MINUTE
INTO D
FROM dual;
D := INTERVAL '1' HOUR + INTERVAL '02' MINUTE;
END;
下面的例子輸出一個72小時15分種的INTERVAL:
DECLARE
A INTERVAL DAY TO SECOND;
BEGIN
SELECT INTERVAL '72:15' HOUR TO MINUTE INTO A FROM DUAL;
DBMS_OUTPUT.PUT_LINE(A);
END;
8.CAS與EXTRACT
CAST是一個ANSI標準的類型轉換函數，用于不同類型數據的類型轉換
舉例如下：
DECLARE
a TIMESTAMP WITH TIME ZONE;
b VARCHAR2(40);
c TIMESTAMP WITH LOCAL TIME ZONE;
BEGIN
a := CAST ('24-Feb-2002 09.00.00.00 PM US/Eastern'
AS TIMESTAMP WITH TIME ZONE);
b := CAST (a AS VARCHAR2);
c := CAST (a AS TIMESTAMP WITH LOCAL TIME ZONE);
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
DBMS_OUTPUT.PUT_LINE(c);
END;
輸出如下：
24-FEB-02 09.00.00.000000 PM US/EASTERN
24-FEB-02 09.00.00.000000 PM US/EASTERN
24-FEB-02 09.00.00.000000 PM
EXTRACT，意為提取。可以從DATTIME類型中提取出相應的值。可提取的值得類型如下表：
Component name Return datatype
---------------------------
YEAR NUMBER
MONTH NUMBER
DAY NUMBER
HOUR NUMBER
MINUTE NUMBER
SECOND NUMBER
TIMEZONE_HOUR NUMBER
TIMEZONE_MINUTE NUMBER
TIMEZONE_REGION VARCHAR2
TIMEZONE_ABBR VARCHAR2
-----------------------
舉例如下：
BEGIN
IF EXTRACT (MONTH FROM SYSDATE) = 11 THEN
DBMS_OUTPUT.PUT_LINE('It is November');
ELSE
DBMS_OUTPUT.PUT_LINE('It is not November');
END IF;
END;
9.datetime運算
增加/減少
DECLARE
current_date TIMESTAMP;
result_date TIMESTAMP;
BEGIN
current_date := SYSTIMESTAMP;
result_date:= current_date + INTERVAL '1500 4:30:2' DAY TO SECOND;
DBMS_OUTPUT.PUT_LINE(result_date);
END;
DECLARE
current_date DATE;
next_month DATE;
BEGIN
current_date := SYSDATE;
next_month := ADD_MONTHS(current_date, 1);
DBMS_OUTPUT.PUT_LINE(next_month);
END;
SYSDATE + 1
SYSDATE + (4/24)
注意最后兩行的直接運算方法。下面列出這種直接運算值的規則
Value Expression Represents
---------------------------------
1/24 1/24 One hour
1/1440 1/24/60 One minute
1/86400 1/24/60/60 One second
----------------------------------
兩個datetime之間的運算
DECLARE
leave_on_trip TIMESTAMP := TIMESTAMP '2005-03-22 06:11:00.00';
return_from_trip TIMESTAMP := TIMESTAMP '2005-03-25 15:50:00.00';
trip_length INTERVAL DAY TO SECOND;
BEGIN
trip_length := return_from_trip - leave_on_trip;
DBMS_OUTPUT.PUT_LINE('Length in days hours:minutes:seconds is ' || trip_length);
END;
輸出結果：
Length in days hours:minutes:seconds is +03 09:39:00.000000
BEGIN
DBMS_OUTPUT.PUT_LINE (
TO_DATE('25-Mar-2005 3:50 pm','dd-Mon-yyyy hh:mi am')
- TO_DATE('22-Mar-2005 6:11 am','dd-Mon-yyyy hh:mi am')
);
END;
輸出：
3.40208333333333333333333333333333333333
FUNCTION MONTHS_BETWEEN (date1 IN DATE, date2 IN DATE)
RETURN NUMBER
BEGIN
--Calculate two ends of month, the first earlier than the second:
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('31-JAN-1994', '28-FEB-1994'));
--Calculate two ends of month, the first later than the second:
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('31-MAR-1995', '28-FEB-1994'));
--Calculate when both dates fall in the same month:
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('28-FEB-1994', '15-FEB-1994'));
--Perform months_between calculations with a fractional component:
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('31-JAN-1994', '1-MAR-1994'));
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('31-JAN-1994', '2-MAR-1994'));
DBMS_OUTPUT.PUT_LINE(
MONTHS_BETWEEN ('31-JAN-1994', '10-MAR-1994'));
END;
輸出:
-1
13
.4193548387096774193548387096774193548387
-1.03225806451612903225806451612903225806
-1.06451612903225806451612903225806451613
-1.32258064516129032258064516129032258065
DATE與TIMESTAMP的混合使用
DECLARE
a DATE;
b TIMESTAMP;
c INTERVAL DAY(3) TO SECOND(0);
d INTERVAL DAY(3) TO SECOND(0);
BEGIN
a := TO_DATE('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am');
b := TO_TIMESTAMP('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am');
c := a - b;
d := b - a;
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);
END;
輸出:
+149 00:02:00
-149 00:02:00
INTERVAL的運算
DECLARE
a1 INTERVAL DAY TO SECOND := '2 3:4:5.6';
b1 INTERVAL DAY TO SECOND := '1 1:1:1.1';
a2 INTERVAL YEAR TO MONTH := '2-10';
b2 INTERVAL YEAR TO MONTH := '1-1';
a3 NUMBER := 3;
b3 NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE(a1 - b1);
DBMS_OUTPUT.PUT_LINE(a2 - b2);
DBMS_OUTPUT.PUT_LINE(a3 - b3);
END;
輸出:
+000000001 02:03:04.500000000
+000000001-09
2
DECLARE
a1 INTERVAL DAY TO SECOND := '2 3:4:5.6';
a2 INTERVAL YEAR TO MONTH := '2-10';
a3 NUMBER := 3;
BEGIN
--Show some interval multiplication
DBMS_OUTPUT.PUT_LINE(a1 * 2);
DBMS_OUTPUT.PUT_LINE(a2 * 2);
DBMS_OUTPUT.PUT_LINE(a3 * 2);
--Show some interval division
DBMS_OUTPUT.PUT_LINE(a1 / 2);
DBMS_OUTPUT.PUT_LINE(a2 / 2);
DBMS_OUTPUT.PUT_LINE(a3 / 2);
END;
輸出:
+000000004 06:08:11.200000000
+000000005-08
6
+000000001 01:32:02.800000000
+000000001-05
1.5
DECLARE
B INTERVAL DAY(9) TO SECOND(9);
FUNCTION double_my_interval (
A IN INTERVAL DAY TO SECOND) RETURN INTERVAL DAY TO SECOND
IS
BEGIN
RETURN A * 2;
END;
BEGIN
B := '1 0:0:0.123456789';
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(double_my_interval(B));
END;
輸出:
+000000001 00:00:00.123456789
+02 00:00:00.246914
兩個不丟失精度的INTERVAL數據類型
YMINTERVAL_UNCONSTRAINED
Accepts any INTERVAL YEAR TO MONTH value with no loss of precision
DSINTERVAL_UNCONSTRAINED
Accepts any INTERVAL DAY TO SECOND value with no loss of precision
舉例：
DECLARE
B INTERVAL DAY(9) TO SECOND(9);
FUNCTION double_my_interval (
A IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED
IS
BEGIN
RETURN A * 2;
END;
BEGIN
B := '100 0:0:0.123456789';
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(double_my_interval(B));
END;
輸出:
+000000100 00:00:00.123456789
+000000200 00:00:00.246913578
10.date/time函數參考
|
Name
|
Description
|
|
|
|