Oracle-日期算术

日期算术 - 日期之间的差异，以日期，小时，分钟和/或秒为单位

在oracle中，使用减法可以得到两个DATE之间的差（以days和/或fractions表示）：

1	SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;

输出两个日期之间的天数：

1
2
3

DIFFERENCE
----------
        89

和：

SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
         - TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
         AS difference
FROM   DUAL

输出两个日期之间的天数分数：

1
2
3

DIFFERENCE
----------
    1.0425

通过将该数字乘以24，24 * 60或24 * 60 * 60可以得到小时，分钟或秒的差异。

可以更改上一个示例，以获取两个日期之间的日期，小时，分钟和秒数：

SELECT TRUNC( difference                       ) AS days,
       TRUNC( MOD( difference * 24,       24 ) ) AS hours,
       TRUNC( MOD( difference * 24*60,    60 ) ) AS minutes,
       TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM   (
  SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
         - TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
         AS difference
  FROM   DUAL
);

（注意：使用TRUNC()而不是FLOOR()来正确处理负差异。）

输出：

1
2
3

DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
   1     1       1      12

上一个示例也可以通过使用NUMTODSINTERVAL()将数字差异转换为间隔来解决：

SELECT EXTRACT( DAY    FROM difference ) AS days,
       EXTRACT( HOUR   FROM difference ) AS hours,
       EXTRACT( MINUTE FROM difference ) AS minutes,
       EXTRACT( SECOND FROM difference ) AS seconds
FROM   (
  SELECT NUMTODSINTERVAL(
           TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
             - TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
           'DAY'
         ) AS difference
  FROM   DUAL
);

设置默认日期格式模型

当Oracle隐式地从DATE转换为字符串或反之亦然时（或在没有格式模型的情况下显式调用TO_CHAR()或TO_DATE()时），NLS_DATE_FORMAT会话参数将用作转换中的格式模型。如果文本不匹配格式模型，则会引发异常。

你可以使用以下命令查看此参数：

1	SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

你可以使用以下方式在当前会话中设置此值：

1	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

（注意：这不会更改任何其他用户的值。）

如果你依靠NLS_DATE_FORMAT在TO_DATE()或TO_CHAR()中提供格式掩码，那么当你的查询中断，如果这个值被改变你不应该感到惊讶。

日期算术 - 日期之间的差异（以月或年为单位）

可以使用MONTHS_BETWEEN( date1, date2 )找到两个日期之间的月份差异：

1	SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;

输出：

1
2
3

DIFFERENCE
----------
        12

如果差异包括部分月份，则它将返回基于每月有31天的月份分数：

1	SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;

输出：

1
2
3

DIFFERENCE
----------
 1.4516129

由于MONTHS_BETWEEN假设每月31天，因为每月可能有较少的天数，则这可能会导致跨越月份之间边界的差异值不同。

例：

SELECT MONTHS_BETWEEN( DATE'2016-02-01', DATE'2016-02-01' - INTERVAL '1' DAY ) AS "JAN-FEB",
       MONTHS_BETWEEN( DATE'2016-03-01', DATE'2016-03-01' - INTERVAL '1' DAY ) AS "FEB-MAR",
       MONTHS_BETWEEN( DATE'2016-04-01', DATE'2016-04-01' - INTERVAL '1' DAY ) AS "MAR-APR",
       MONTHS_BETWEEN( DATE'2016-05-01', DATE'2016-05-01' - INTERVAL '1' DAY ) AS "APR-MAY"
FROM   DUAL;

输出：

1
2
3

JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452

可以通过将月差除以12来找到年的差异。

更改SQL / Plus或SQL Developer显示日期的方式

当SQL / Plus或SQL Developer显示日期时，它们将使用默认日期格式模型对字符串执行隐式转换（请参阅设置默认日期格式模型示例）。

你可以通过更改NLS_DATE_FORMAT参数来更改日期的显示方式。

将日期转换为字符串

使用TO_CHAR( date [, format_model [, nls_params]] )：

（注意：如果不提供格式模型，那么将使用NLS_DATE_FORMAT会话参数作为默认格式模型;对于每个会话都可以使用不同的格式模型，因此不应该依赖它），最好始终指定格式模型。

CREATE TABLE table_name (
  date_value DATE
);
INSERT INTO table_name ( date_value ) VALUES ( DATE '2000-01-01' );
INSERT INTO table_name ( date_value ) VALUES ( TIMESTAMP '2016-07-21 08:00:00' );
INSERT INTO table_name ( date_value ) VALUES ( SYSDATE );

然后：

1	SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;

输出：

FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21

和：

SELECT TO_CHAR(
         date_value,
         'FMMonth d yyyy, hh12:mi:ss AM',
         'NLS_DATE_LANGUAGE = French'
       ) AS formatted_date
FROM   table_name;

输出：

FORMATTED_DATE
-----------------------------
Janvier   01 2000, 12:00:00 AM
Juillet   21 2016, 08:00:00 AM
Juillet   21 2016, 19:08:31 PM

提取日期的年，月，日，小时，分钟或秒成分

可以使用EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )找到DATE数据类型的年，月或日组件

SELECT EXTRACT (YEAR  FROM DATE '2016-07-25') AS YEAR,
       EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH,
       EXTRACT (DAY   FROM DATE '2016-07-25') AS DAY
FROM DUAL;

输出：

1
2
3

YEAR MONTH DAY
---- ----- ---
2016     7  25

组件的时间（小时，分钟或秒）可以通过以下任一方式找到：

使用CAST( datevalue AS TIMESTAMP )将DATE转换为TIMESTAMP，然后使用EXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); 要么
使用TO_CHAR( datevalue, format_model ) 将值作为字符串获取。

例如：

SELECT EXTRACT( HOUR   FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
       EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
       EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM   (
  SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);

输出：

1
2
3

HOURS MINUTES SECONDS
----- ------- -------
    9      42       1

使用时间组件生成日期

使用TO_DATE()将它从字符串文字转换：

1	SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;

或使用TIMESTAMP文字：

CREATE TABLE date_table(
  date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );

当将其存储在表的DATE列中时，Oracle会将TIMESTAMP隐式转换为DATE; 但是你可以显式地将CAST()的值改为DATE：

1	SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;

生成没有时间组件的日期

所有DATE都有时间组件; 然而，通常存储不需要包括具有设置为零（即，午夜）的小时/分钟/秒的时间信息的日期。

使用ANSI DATE文本（使用ISO 8601日期格式）：

1	SELECT DATE '2000-01-01' FROM DUAL;

使用TO_DATE()将它从字符串文字转换：

1	SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;

（有关日期格式模型的更多信息，请参见Oracle文档。）

要么：

SELECT TO_DATE(
         'January 1, 2000, 00:00 A.M.',
         'Month dd, YYYY, HH12:MI A.M.',
         'NLS_DATE_LANGUAGE = American'
       )
FROM   DUAL;

（如果你要转换特定于语言的术语（如月份名称），那么最好将第3个nlsparam参数包含在TO_DATE()函数中，并指定期望的语言。

日期的格式

在Oracle中，DATE数据类型没有格式; 当Oracle向客户端程序（SQL/Plus，SQL/Developer，Toad，Java，Python等）发送DATE时，它将发送表示日期的7-或8-字节。

未存储在表中的DATE（即，由SYSDATE生成并且在使用DUMP()命令时具有“type 13”）具有8个字节并具有结构（右边的数字是2012-11-26 16:41:09的内部表示）：

BYTE VALUE                           EXAMPLE
---- ------------------------------- --------------------------------------
1    Year modulo 256                 220
2    Year multiples of 256           7   (7 * 256 + 220 = 2012)
3    Month                           11
4    Day                             26
5    Hours                           16
6    Minutes                         41
7    Seconds                         9
8    Unused                          0

存储在表（在使用DUMP()命令时为“type 12”）的DATE具有7个字节并具有结构（右侧的数字是2012-11-26 16:41:09的内部表示）：

BYTE VALUE                           EXAMPLE
---- ------------------------------- --------------------------------------
1    ( Year multiples of 100 ) + 100 120
2    ( Year modulo 100 ) + 100       112 ((120-100)*100 + (112-100) = 2012)
3    Month                           11
4    Day                             26
5    Hours + 1                       17
6    Minutes + 1                     42
7    Seconds + 1                     10

如果你想让日期有一个特定的格式，那么你需要将它转换为具有格式（即字符串）的东西。 SQL客户端可以隐式地执行此操作，或者可以使用TO_CHAR( date, format_model, nls_params )将值显式转换为字符串。

时区和夏令时

DATE数据类型不处理时区或夏令时的更改。

或者：

使用TIMESTAMP WITH TIME ZONE数据类型; 要么
处理应用程序逻辑中的更改。

DATE可以存储为协调世界时（UTC），并转换为当前会话时区，如下所示：

SELECT FROM_TZ(
         CAST(
           TO_DATE( '2016-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' )
           AS TIMESTAMP
         ),
         'UTC'
       )
       AT LOCAL AS time
FROM   DUAL;

如果运行ALTER SESSION SET TIME_ZONE = '+01:00'; 那么输出是：

1
2
3

TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00

和ALTER SESSION SET TIME_ZONE ='PST'; 那么输出是：

1
2
3

TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST