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
|
|
ADD_MONTHS
| |
|
CAST
| |
|
CURRENT_DATE
|
Returns the current date and time as a DATE value in the session time zone.
|
|
CURRENT_TIMESTAMP
|
Returns the current date and time as a TIMESTAMP WITH TIME ZONE value in the session time zone.
|
|
DBTIMEZONE
|
Returns the time zone offset (from UTC) of the database time zone in the form of a character string (e.g., '-05:00').
|
|
EXTRACT
| |
|
FROM_TZ
|
Adds time zone information to a TIMESTAMP value, converting it to a TIMESTAMP WITH TIME ZONE.
|
|
LAST_DAY
|
Returns the last day in the month containing a specified input DATE.
|
|
LOCALTIMESTAMP
|
Returns the current date and time as a TIMESTAMP value in the local time zone.
|
|
MONTHS_ BETWEEN
| |
|
NEW_TIME
|
Shifts a DATE value from one time zone to another. This functionality is somewhat obsolete. If you need to work with time zones in new applications, use the TIMESTAMP WITH TIME ZONE type.
|
|
NEXT_DAY
|
Returns the DATE of the first weekday specified that is later than an input DATE.
|
|
NUMTODSINTERVAL
|
Converts a number representing some number of days, hours, minutes, or seconds (your choice) to a value of type INTERVAL DAY TO SECOND.
|
|
NUMTOYMINTERAL
|
Converts a number representing some number of years or months (your choice) to a value of type INTERVAL YEAR TO MONTH.
|
|
ROUND
|
Returns a DATE rounded to a specified level of granularity.
|
|
SESSIONTIMEZONE
|
Returns the time zone offset (from UTC) of the session time zone in the form of a character string (e.g., '-05:00').
|
|
SYSDATE
|
Returns the current date and time from the Oracle server as a DATE value.
|
|
SYS_EXTRACT_UTC
|
Converts a TIMESTAMP WITH TIME ZONE value to a TIMESTAMP having the same date and time, but normalized to UTC.
|
|
SYSTIMESTAMP
|
Returns the current date and time from the Oracle server as a TIMESTAMP WITH TIME ZONE value.
|
|
TO_CHAR
| |
|
TO_DATE
| |
|
TO_DSINTERVAL
| |
|
TO_TIMESTAMP
| |
|
TO_TIMESTAMP_TZ
| |
|
TO_YMINTERVAL
| |
|
TRUNC
|
Truncates a DATE value to a specified level of granularity.
|
|
TZ_OFFSET
|
Returns the time zone offset from UTC (e.g., '-05:00') for a given time zone name, abbreviation, or offset.
|
扫一扫
6145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
