转：Oracle Dates and Timestamps收藏

Dates and Timestamps

转自：http://www.heysky.net/cgi-bin/mt/mt-tb.cgi/408

1.Datetime Datatypes

    DATE

      存储日期、时间，没有时区，精确到秒，是 9i 之前唯一的 datetime datatype。

    TIMESTAMP [(precision)]

      除了最多精确到 billionth of a second，其他和 DATE 一样。

    TIMESTAMP [(precision)] WITH TIME ZONE

      TIMESTAMP 的基础上，保存时区信息。在内部存储时，Oracle 将时间转换为 UTC 格式进行保存，比如：2002-02-06 20:00:00:00.00 -5:00。

    TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

      不保存时区信息，但会将时间转换为数据库的时区（如果是保存到数据库表的列中）或者 session 的时区（如果保存为 PL/SQL variables）。数据在不同时区间传输时，会进行转换，但不保存时区信息。

    其中 precision 表示秒的小数部分保留的位数，范围为：0~9。

   

    如何选择 Datetime Datatype

      1.如果你要精确到秒的小数位，那么使用 TIMESTAMP

      2.如果要保留 datetime 值的时区，那么使用 TIMESTAMP WITH TIME ZONE

      3.可以用 TIMESTAMP(0) 代替 DATE，但是两者的日期算法是不同的

      4.为了兼容 TIMESTAMP 出现之前的应用程序，那么使用 DATE

      5.PL/SQL 代码中的类型应该和数据库表中的类型相一致，比如将 TIMESTAMP WITH TIME ZONE 的类型存放到 DATE 类型的列中，时区就会丢失

      6.使用 9i 以前的版本，那么只能使用 DATE

      7.将 ADD_MONTHS 这种传统操作 DATE 类型的函数应用到新的 TIMESTAMP 类型上，会产生很大的不同

     

2.获得现在的时间

  Function            Time zone    Datatype returned

  CURRENT_DATE        Session      DATE

  CURRENT_TIMESTAMP   Session      TIMESTAMP WITH TIME ZONE

  LOCALTIMESTAMP      Session      TIMESTAMP

  SYSDATE             Server       DATE

  SYSTIMESTAMP        Server       TIMESTAMP WITH TIME ZONE

  注：返回的都是数据库服务器端的当时时间，单前三者会转换为 session 的时区，可以用 alter session set time_zone 改变

      9i 之前只有 SYSDATE

 

3.Interval Datatypes

    INTERVAL YEAR TO MONTH

      Allows you to define an interval of time in terms of years and months.

      INTERVAL YEAR [(year_precision)] TO MONTH

        year_precision：year 的位数，范围：0~4，默认：2

    INTERVAL DAY TO SECOND

      Allows you to define an interval of time in terms of days, hours, minutes, and seconds (including fractional seconds).

      INTERVAL DAY [(day_precision)] TO SECOND [(frac_sec_prec)]

        day_precision：day 的位数，范围：0~9，默认：2

        frac_sec_prec：秒小数部分保留的位数，范围：0~9，默认：6

       

    Month、hour、minute、second 的精度是不用设置的，Oracle 会保证他们的范围分别在 0~11、0~23、0~59、0~59

4.Datetime Conversions

    Date 范围：

      4712-01-01 B.C. —— 9999-12-31 A.D.

     

    From Strings to Datetimes

      隐式：

        根据 NLS_DATE_FORMAT 的格式写 String 的值，Oracle 会隐式转换，如果和 NLS_DATE_FORMAT 不匹配，不能转换。

      显式（使用内建函数）：

        TO_DATE( string[, format_mask[, nls_language]])

        TO_DATE( number[, format_mask[, nls_language]])

          用数字表示 Julian date 转换为 Date 类型，此时 format_mask = 'J'，number 表示从 January 1, 4712 B.C. 开始的天数，由于 Oracle 中最大日期是 December 31, 9999 A.D.，所以 number 的范围为：1 ~ 5373484

        TO_TIMESTAMP( string[, format_mask[, nls_language]])

        TO_TIMESTAMP_TZ( string[, format_mask[, nls_language]])

          此函数用于将 string 转换为 TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE

        format_mask：

          默认为 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT（分别对应 TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ）

          对于 TIMESTAMP 类型，秒的小数部分可以用 '.FF' 或者 'XFF' 表示，比如：'mm/dd/yyyy hh:mi:ss.ff AM TZD' or 'mm/dd/yyyy hh:mi:ssxff AM TZD'，其中 'X' 由 NLS_NUMERIC_CHARACTERS 的第一个字符决定。

        nls_language：

          Optionally specifies the language to be used to interpret the names and abbreviations of both months and days in the string.

        几个限制：

          1.传给 TO_DATE 中的 string 长度不能超过 220 个字符。

          2.format mask 中 Julian date element (J) 和 the day of year element (DDD) 不能同时出现。

          3.format mask 中 date/time 的某一个部分不能重复出现，比如：'YYYY-YYY-DD-MM'

          4.format mask 中 HH24 不能和 am/pm 同时出现

         

    From Datetimes to Strings

      使用 TO_CHAR，默认格式 'DD-MON-RR'（9 位），可以用 NLS_DATE_FORMAT 覆盖。

      对于 TIMESTAMP 类型，秒的小数部分可以用 FF1 ~ FF9 来表示保留几位（自动四舍五入）。

      不能将用于 TIMESTAMP 的 format_mask 用于 DATE 类型，否则会报 ORA-01821，反过来可以。

     

    Working with Time Zones

      明确表示某一个时区，应该联合使用 TZH TZM 或者 TZR TZD

        TZH：与 UTC 之间 HOUR 的偏移

        TZM：与 UTC 之间 MINUTE 的偏移

        TZR：The time zone region

        TZD：The abbreviated time zone name

        注：后两者可以查看 V$TIMEZONE_NAMES 获得

            对于时区，存入的是什么信息，显示的也是什么信息，比如用 TZH：TZM 存入和 UTC 之间的偏移，就只能显示类似 +08：00 的时区偏移，而无法显示具体哪个 time zone region

       

    二位数年份的处理

      使用 RR/RRRR 时，Oracle 自动根据现在的年份辨别输入的二位数年份：

        1.如果现在是前半世纪（0~49）

          如果输入的是前半世纪（0~49），那么返回本世纪的年份

          如果输入的是后半世纪（50~99），那么返回上世纪的年份

        2.如果现在是后半世纪（50~99）

          如果输入的是前半世纪（0~49），那么返回下世纪的年份

          如果输入的是后半世纪（50~99），那么返回本世纪的年份

      使用 YY/YYYY 时，不管输入的是什么，都返回本世纪的年份

      注：这种自动转换只适用于 String -> Date 的转换，如果是 Date -> String，那就按照 Date 存储的值来转换，此时再用 RR/RRRR 已经没有意义了，因为 Oracle 内部存储的年份是四位数的。

         

5.Date and Timestamp Literals

    这是 9i 之后 引入的 ISO SQL standard 格式，格式是固定的，不能更改，也不受环境变量影响，因此可以作为常量来使用

      DATE 'YYYY-MM-DD'

      TIMESTAMP 'YYYY-MM-DD HH:MI:SS[.FFFFFFFFF] [{+|-}HH:MI]' （HH 必须是 24 小时制的，FFFFFFFFF 可选 1~9 位，也可以没有，时区也可以使用 time zone region（EST），但这只是 Oracle 提供的格式，不是 ANSI/ISO standards）

    例子：

      DATE '2002-02-19'

      TIMESTAMP '2002-02-19 14:00:00.000000000 -5:00';

     

     

6.Interval Conversions

    Numbers to Intervals

      NUMTOYMINTERVAL ( n , 'char_expr' )

      NUMTODSINTERVAL ( n , 'char_expr' )

      char_expr：

      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

      以上不区分大小写

     

    Strings to Intervals

      TO_YMINTERVAL('Y-M')

      TO_DSINTERVAL('D HH:MI:SS')

      格式是固定的，不能缺少任何一个部分

 

 

7.Interval Literals

    语法： INTERVAL 'character_representation' start_element TO end_element

           character_representation 不需要指明所有 datetime 的元素，但必须指明从 start_element 到 end_element 的所有连续元素，如果只有 start_element 只需要一个元素就行了，但 start_element 和 end_element 不能跨越 month 和 day。

           注：由于 bug 的问题，在 9i Releases 1 and 2 和 10g Release 1 中在 pl/sql 中指明部分元素会出错，比如 INTERVAL '1:02' HOUR TO MINUTE，但在 SQL 中不会。

           Oracle 自动会将 high-end value 规格化，比如：INTERVAL '72:15' HOUR TO MINUTE 会规格化为 +03 00:15:00.000000，但 INTERVAL '72:75' HOUR TO MINUTE 会报错，只有 high-end value（这里的 HOUR）会自动转换

          

          

8.CAST and EXTRACT

    两者都是 standard SQL functions

   

    CAST（Oracle8 开始）

      语法：

        CAST（var as type）

        注：在 SQL 中，type 可以指定长度，比如：varchar2(40)，但在 pl/sql 中不能指定长度

      可以在 string、datetime（DATE、TIMESTAMP）之间互相转换

      由于无法指定 format，所以取决于 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT 环境变量

    EXTRACT（Oracle9i 开始）

      语法：

        EXTRACT (component_name, FROM {datetime | interval})

        component_name（不区分大小写）：

        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

       

      由于用 to_char 无法格式化 interval 的显示格式（格式是固定的），所以唯一的方法就是使用 EXTRACT 来格式化

 

9.Datetime Arithmetic

    Adding and Subtracting Intervals to/from Datetimes

      1.和 Intervals 做 +- 操作。在和 INTERVAL DAY TO SECOND 进行运算时，不用考虑任何问题，但和 INTERVAL YEAR TO MONTH 做运算时，由于它是直接 +- 原始 datetimes 的年和月，所以可能够产生 ORA-01839 错误，应该有相关的 exception 处理语句。

      2.直接和 number 做 +- 操作。number 表示天数，可以用分数小数表示：

        Value    Expression   Represents

        1/24     1/24         One hour

        1/1440   1/24/60      One minute

        1/86400  1/24/60/60   One second

        注：不建议进行约分等操作，这样使得程序一目了然。

      3.使用 ADD_MONTHS 函数。

          1.如果输入的日期是输入月份中的最后一天，那么结果也是结果月份的最后一天

            比如：

              add_months(Date '2006-02-28',1)  --> 2006-03-31

          2.如果结果月份的最后一天小于输入月份的日子，那么结果日期是结果月份的最后一天

            比如：

              add_months(Date '2006-03-30',-1)  --> 2006-02-28

         

          可以构建一个自定义函数，解决 1 的问题，让他不返回月份的最后一天

            FUNCTION my_add_months (

               date_in IN DATE, months_shift IN NUMBER)

            RETURN DATE IS

               date_out DATE;

               day_in NUMBER;

               day_out NUMBER;

            BEGIN

               date_out := ADD_MONTHS(date_in, months_shift);

               day_in := TO_NUMBER(TO_CHAR(date_in,'DD'));

               day_out := TO_NUMBER(TO_CHAR(date_out,'DD'));

               IF day_out > day_in

               THEN

                  date_out := date_out - (day_out - day_in);

               END IF;

               RETURN date_out;

            END;

      建议：

        虽然以上三者 DATE TIMESTAMP 都可以操作，但建议 TIMESTAMP 使用 1，DATE 可以使用 2、3

           

    Computing the Interval Between Two Datetimes

      两个 TIMESTAMP 相减返回的永远是 INTERVAL DAY TO SECOND

      两个 DATE 相减返回的是天数，如果有小数部分，意思是 hours, minutes, and seconds 转换为天的结果

      MONTHS_BETWEEN 函数：

        定义：

              FUNCTION MONTHS_BETWEEN (date1 IN DATE, date2 IN DATE)

                RETURN NUMBER

              尝试了一下也可以使用 TIMESTAMP，但还是建议不使用，不知道会出现什么问题。

        1.date1 > date2 返回正数，小于返回负数，相等为0

        2.如果在同一年的同一月中，结果的范围是 > -1 and < 1，1 和 -1 都是不会到达的，一月按照 31 天计算，TIME 部分也要计算

        3.如果 date1 date2 分别是相应月份中的第一天或者最后一天，则返回整数，且忽略 TIME 部分的值

        4.如果 date1 date2 在不同的月份中，且至少有一个不是该月的第一天或者最后一天，那么返回小数。小数是基于 31 天为一个月计算的，且要计算 TIME 部分。

       

    Mixing DATEs and TIMESTAMPs

      由前面可知：TIMESTAMPs 相减返回 INTERVAL DAY TO SECOND，DATEs 相减返回 numeric value

      那么：

        1.如果 DATEs 相减想返回 INTERVAL DAY TO SECOND 需要用 CAST 显示地将 DATEs 转换为 TIMESTAMPs

            CAST(a AS TIMESTAMP) - CAST(b AS TIMESTAMP)

        2.如果 DATE 和 TIMESTAMP 混合使用，Oracle 隐式地将 DATE 转换为 TIMESTAMP，因此返回的是 INTERVAL DAY TO SECOND

       

    Adding and Subtracting Intervals

      INTERVAL 相加减必须满足类型相同：两个 INTERVAL DAY TO SECOND 相加减，或者两个 INTERVAL YEAR TO MONTH 相加减，不能混合使用。

     

    Multiplying and Dividing Intervals

      DATETIMEs 是不能进行乘除运算的，但是 Intervals 可以。进行乘除运算时，每一个元素都会进行运算，如果超过该元素的范围就往上一级元素进位，如果出现小数就将小数部分转化为下一级元素，秒除外。

   

    Using Unconstrained INTERVAL Types

      由于在函数、过程中，参数不能指定精度，因此当 INTERVAL 传入时如果精度大于默认精度：YEAR(2),DAY(2),SECOND(6)，会返回错误 ORA-01873: the leading precision of the interval is too small

      因此，引入两个特殊类型：

        YMINTERVAL_UNCONSTRAINED

          接受任何精度的 INTERVAL YEAR TO MONTH

        DSINTERVAL_UNCONSTRAINED

          接受任何精度的 INTERVAL DAY TO SECOND

 

10.Date/Time Functions

    对于传统的 DATE 函数，比如 ADD_MONTHS 建议不要用于 TIMESTAMP，Oracle 会将他们隐式地转为 DATE，这样会丢失秒的小数部分，还会将时区修改为 SESSION 的时区。因此，对于 TIMESTAMP 应该使用 INTERVAL 来操作。