在PL/SQL中使用日期型別

在PL/SQL中使用日期型別

之前的文章介紹了在PL/SQL中使用字串和數字了下。毫無疑問，字串和數字很重要，但是可以確定的是沒有哪個應用不依賴於日期的。
你需要記錄事件的發生事件，人們的出生日期等等很多。

通常的使用方式是：

1）宣告日期變數和常量

2）使用內建函式顯示和修改日期值

3）執行日期相關計算

日期型別比字串或數字更復雜。它有多個部分組成（年，月，日，時，分，秒等），而且關於一個有效的日期有很多規則。
本文將給你所有資訊以便在PL/SQL程式中使用日期。

1、PL/SQL中的日期，時間戳和間隔(Intervals)

絕大多數應用需要儲存和操作日期和時間。不像字串和數字，日期要求更復雜：不僅僅因為他們更高階的格式化資料，其對於有效值和有效計算方式都有
很多規則。

Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.

慶幸的是，Oracle資料庫和PL/SQL提供了一系列日期和時間型別以標準形式來儲存日期和時間資訊，還有一系列內建函式來實現日期和時間計算。
有三種相關型別：
1）DATE—This datatype stores a date and a time, resolved to the second. It does not include the time zone【時區】. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications. 也是最古老和最常用的型別。

2）TIMESTAMP—Time stamps are similar to dates, but with these two key distinctions:
(2.1) you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and
儲存精度達到10億分之一秒。
(2.2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp. 可帶時區。

3）INTERVAL—Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.
記錄時間間隔。可以指定年和月或天和秒級別的時間間隔。

來看個例子：
Code Listing 1: Declaring DATE, TIMESTAMP, and INTERVAL variables

DECLARE
   l_today_date        DATE := SYSDATE;
   l_today_timestamp   TIMESTAMP := SYSTIMESTAMP;
   l_today_timetzone   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
   l_interval1         INTERVAL YEAR (4) TO MONTH := '2011-11';
   l_interval2         INTERVAL DAY (2) TO SECOND := '15 00:30:44';
BEGIN
   null;
END;
/

2、如何選擇使用合適的日期型別？
1）Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.

2）You can, in general, use TIMESTAMP in place of DATE. A time stamp that does not contain subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When your time stamp does contain subsecond【亞秒】 data, it takes up 11 bytes of storage.

3）Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered.

4）Use TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones. 自動轉換資料庫時間和會話時間時區。

5）Use DATE when it’s necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced. 保持向前相容性。

3、如何獲得當前時間？
相信大多數開發者都是通過SYSDATE函式，但是Oracle資料庫還提供了其他一些函式，一起來看一下：

1）會話級別函式：
CURRENT_DATE 返回：DATE
CURRENT_TIMESTAMP 返回：TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP 返回：TIMESTAMP

2）資料庫級別
SYSDATE 返回：DATE
SYSTIMESTAMP 返回：TIMESTAMP WITH TIME ZONE

Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values

例如：

BEGIN
  DBMS_OUTPUT.put_line (SYSDATE);
  DBMS_OUTPUT.put_line (SYSTIMESTAMP);
  DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

Here is the output:

07-AUG-11
07-AUG-11 08.46.16.379000000 AM -05:00
-000000000 00:00:00.379000000

因為我給DBMS_OUTPUT.PUT_LINE傳遞了日期和時間戳，Oracle資料庫使用資料庫或會話級的預設格式(引數：NLS_DATE_FORMAT)將其隱式轉換為字串。
預設安裝資料庫時的格式為：DD-MON-YYYY。而預設時間戳格式包含了日期和時區的偏移（offset）。

如何修改？【譯者注：以下摘自網路】
1）.可以在使用者環境變數中指定（LINUX)。 在使用者的.bash_profile中增加兩句：
export NLS_LANG=AMERICAN —要注意這一句必須指定，不然下一句不生效。
export NLS_DATE_FORMAT=’yyyy/mm/dd hh24:mi:ss’
2）.在SQLPLUS的glogin.sql檔案增加一句： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
3）.直接修改當前會話的日期格式 ： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
4）.修改資料庫的引數，需要重啟資料庫後生效 SQL> alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’ scope=spfile;

4、實現日期到字串和字串到日期的轉換？

正如to_char函式對於數字一樣，我們使用另外一個版本的to_char實現日期或時間戳型別到字串的轉換。

如果使用了不帶格式引數的to_char。則資料庫使用隱式轉換。

BEGIN
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE));
   DBMS_OUTPUT.put_line (
     TO_CHAR (SYSTIMESTAMP));
END;
/ 
07-AUG-11
07-AUG-11 08.55.00.470000000 AM -05:00

Use TO_CHAR to display the full names of both the day and the month in the date:

BEGIN
   DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY'));
END;
/
Sunday   , 07TH August    2011

Note: The language used to display these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as in
注意：用語顯示的日期語言由引數：NLS_DATE_LANGUAGE決定。其可以作為to_char的第3個引數使用：
如下：

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'Day, DDth Month YYYY', 
'NLS_DATE_LANGUAGE=Spanish'));
END;
/
Domingo  , 07TH Agosto     2011

另外為了去除顯示結果中多餘的0和空格，我可以使用FM元素修飾符。

BEGIN
  DBMS_OUTPUT.put_line (
     TO_CHAR (SYSDATE, 
'FMDay, DDth Month YYYY'));
END;
/
Sunday, 7TH August 2011

You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
你還可以使用格式引數僅提取日期的一部分內容：
What quarter is it? 當前時間的下一時刻？

TO_CHAR (SYSDATE, ‘Q’)

SCOTT@orcl> select sysdate from dual;

SYSDATE
-------------------
2015-07-25 06:37:17

SCOTT@orcl> select to_char(sysdate, 'Q') from dual;

T
-
3

What is the day of the year (1-366) for today’s date? 當前日期處於所在年份第幾天？

TO_CHAR (SYSDATE, ‘DDD’)

SCOTT@orcl> select TO_CHAR (SYSDATE, 'DDD') from dual;

TO_
---
206

What are the date and time of a DATE variable?

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
'YYYY-MM-DD HH24:MI:SS'));
END;
/

You can also use EXTRACT to extract and return the value of a specified element of a date. For example
還可以使用EXTRACT提取日期指定元素值：

What year is it? 當前年份？

EXTRACT (YEAR FROM SYSDATE)

SCOTT@orcl> select EXTRACT (YEAR FROM SYSDATE) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2015 

What is the day for today’s date? 所在當前月份的天數？

EXTRACT (DAY FROM SYSDATE)

SCOTT@orcl> select EXTRACT (DAY FROM SYSDATE)  from dual;

EXTRACT(DAYFROMSYSDATE)
-----------------------
                     25

如何將字串轉換為日期？使用to_date或to_timestamp內建函式。

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('12-JAN-2011');
END ;

If the string you provide does not match the default format, Oracle Database will raise an exception:
注意：如果你提供的字串引數與資料庫或會話設定的格式引數模型不一致，Oracle資料庫將丟擲異常：

DECLARE
  l_date   DATE;
BEGIN
  l_date := TO_DATE ('January 12 2011');
END;
/

ORA-01858: a non-numeric character was 
found where a numeric was expected

You should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time? Instead, always provide a format mask when converting strings to dates, as in
我們應該始終指定格式，因為你無法確定格式引數何時會改變。

例如：
l_date := TO_DATE (‘January 12 2011’, ‘Month DD YYYY’);

5、日期擷取Date truncation

使用TRUNC內建函式來擷取一個日期的指定單元。最常見的用法是TRUNC(DATE)-不指定任何引數。這時，TRUNC僅將time部分設定為00:00:00。
例如：

Set l_date to today’s date, but with the time set to 00:00:00:

l_date := TRUNC (SYSDATE);

獲取當前日期所在月份第一天 Get the first day of the month for the specified date:

l_date := TRUNC (SYSDATE, ‘MM’);

Get the first day of the quarter for the specified date:

l_date := TRUNC (SYSDATE, ‘Q’);

獲取當前日期所在年份的第一天 Get the first day of the year for the specified date:

l_date := TRUNC (SYSDATE, ‘Y’);

SCOTT@orcl> select TRUNC (SYSDATE, 'Y') from dual;

TRUNC(SYSDATE,'Y')
-------------------
2015-01-01 12:00:00

6、日期運算 Date arithmetic

針對日期和時間戳的運算，Oracle資料庫提供了以下方式：

Add a numeric value to or subtract it from a date, as in SYSDATE + 7; Oracle Database treats the number as the number of days.
給指定日期加上或減去一個數值，例如：SYSDATE + 7; Oracle認為該數字單位為：天。

Add one date to or subtract it from another, as in l_hiredate - SYSDATE.
兩個日期直接相加減，例如：l_hiredate - SYSDATE。

Use a built-in function to “move” a date by a specified number of months or to another date in a week.
使用內建函式使日期移動指定月數或到達周內的另外一個日期。

例如：
Set a local variable to tomorrow’s date: 設定日期變數為明天

l_date := SYSDATE + 1;

Move back one hour: 向前推1個小時
l_date := SYSDATE - 1/24;

Move ahead 10 seconds: 向前推10秒
l_date := SYSDATE + 10 / (60 * 60 * 24);

When you add one date to or subtract it from another, the result is the number of days between the two. As a result, executing this block:
如果你對2個日期相加減，結果是2個日期間隔的天數。

DECLARE
   l_date1   DATE := SYSDATE;
   l_date2   DATE := SYSDATE + 10;
BEGIN
   DBMS_OUTPUT.put_line (
      l_date2 - l_date1);
   DBMS_OUTPUT.put_line (
      l_date1 - l_date2);
END;


returns the following output:
10
-10

And the following function can be used to compute the age of a person, assuming that the person’s correct birth date is passed as the function’s only argument:
另外以下函式可用於計算一個人的年齡：

CREATE OR REPLACE FUNCTION 
your_age (birthdate_in IN DATE)
   RETURN NUMBER
IS
BEGIN
   RETURN SYSDATE - 
          birthdate_in;
END your_age;

下面來看下幾個內建函式：
ADD_MONTHS—針對日期或時間戳加減指定的月數

NEXT_DAY— 當前系統時間的下一星期？的時間 指定時間的下一個星期幾（由char指定）所在的日期
NEXT_DAY(date,char)
date引數為日期型，
char:為1~7或Monday~Sunday

指定時間的下一個星期幾（由char指定）所在的日期，
char也可用1～7替代，1表示星期日，2代表星期一。。。。
還可以是星期一、星期二。。。星期日

LAST_DAY—返回指定日期所在月份最後一天的日期

Move ahead one month: 往後推1個月【譯者注：one month later】

l_date := ADD_MONTHS (SYSDATE, 1);

Move backward three months: 往前推3個月【譯者注：one month ago】

l_date := ADD_MONTHS (SYSDATE, -3);

SCOTT@orcl> SELECT SYSDATE,
  2   LAST_DAY(SYSDATE) "Last",
  3   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
  4   FROM DUAL;

SYSDATE             Last                 Days Left
------------------- ------------------- ----------
2015-07-25 07:04:17 2015-07-31 07:04:17          6

Code Listing 3: Calls to ADD_MONTHS

BEGIN
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('31-jan-2011', 'DD-MON-YYYY'), 1));
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('27-feb-2011', 'DD-MON-YYYY'), -1));
   DBMS_OUTPUT.put_line (
      ADD_MONTHS (TO_DATE ('28-feb-2011', 'DD-MON-YYYY'), -1));
END; 
Here is the output: 

28-FEB-11
27-JAN-11 
31-JAN-11 

Find the next Saturday after today’s date:
找到今天過後的下一個星期六！

l_date := NEXT_DAY (SYSDATE, ‘SAT’);
– or
l_date := NEXT_DAY (SYSDATE, ‘SATURDAY’);