在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’);