Dates and Timestamps总结

Dates and Timestamps总结

1.Datetime Datatypes
1LH%w.m(d'W)GJAw0   DATECNOUG博客首页[:K%ee0]oJ5d jE
      存储日期、时间，没有时区，精确到秒，是 9i 之前唯一的 datetime datatype。
Zi;A9~_r!K.g"NPE0   TIMESTAMP [(precision)]CNOUG博客首页Y9qN3o)lv5~8NU
      除了最多精确到 billionth of a second，其他和 DATE 一样。
'x,?8hf#G[z&o0   TIMESTAMP [(precision)] WITH TIME ZONE
+G`.H!dlaE(R7b0      TIMESTAMP 的基础上，保存时区信息。在内部存储时，Oracle 将时间转换为 UTC 格式进行保存，比如：2002-02-06 20:00:00:00.00 -5:00。CNOUG博客首页#`/\_)Lx7w+e'H'a
   TIMESTAMP [(precision)] WITH LOCAL TIME ZONECNOUG博客首页f8q7Hkw`:r
      不保存时区信息，但会将时间转换为数据库的时区（如果是保存到数据库表的列中）或者 session 的时区（如果保存为 PL/SQL variables）。数据在不同时区间传输时，会进行转换，但不保存时区信息。
3HeE0k,q3lm/?0    其中 precision 表示秒的小数部分保留的位数，范围为：0~9。
gJ {$hOc0   
(o1@RCU!E0ll0   如何选择 Datetime DatatypeCNOUG博客首页*q$`-S NB7L6Z Hx
      1.如果你要精确到秒的小数位，那么使用 TIMESTAMPCNOUG博客首页o/T6gN:g
      2.如果要保留 datetime 值的时区，那么使用 TIMESTAMP WITH TIME ZONE
p2Z
n2hk!_0?X0      3.可以用 TIMESTAMP(0) 代替 DATE，但是两者的日期算法是不同的
OwXfad,Td0      4.为了兼容 TIMESTAMP 出现之前的应用程序，那么使用 DATE
9Ax&[#kqhQ0      5.PL/SQL 代码中的类型应该和数据库表中的类型相一致，比如将 TIMESTAMP WITH TIME ZONE 的类型存放到 DATE 类型的列中，时区就会丢失CNOUG博客首页 @J M2Z-TT(tr(y
      6.使用 9i 以前的版本，那么只能使用 DATE
0zk
BuM0      7.将 ADD_MONTHS 这种传统操作 DATE 类型的函数应用到新的 TIMESTAMP 类型上，会产生很大的不同CNOUG博客首页d-BWU(Z2bc aPJ\
     

2.获得现在的时间
oK*kq:L G`4R0U3@+Ta0  Function            Time zone    Datatype returnedCNOUG博客首页8jj8Yc1p4|'~OX-J
  CURRENT_DATE        Session      DATE
&Qr'y\*a!_,J+kr0  CURRENT_TIMESTAMP   Session      TIMESTAMP WITH TIME ZONE
9n$P5xfC4G B0  LOCALTIMESTAMP      Session      TIMESTAMPCNOUG博客首页:T'qQO8jC?
  SYSDATE             Server       DATE
]9r3KteWW%t0  SYSTIMESTAMP        Server       TIMESTAMP WITH TIME ZONE

  注：返回的都是数据库服务器端的当时时间，单前三者会转换为 session 的时区，可以用 alter session set time_zone 改变CNOUG博客首页n,E4?,i2@5\6I7m%H!vR
      9i 之前只有 SYSDATECNOUG博客首页oc+hF4o3RO
 

3.Interval Datatypes
Obb+C.E.T_:f0   INTERVAL YEAR TO MONTH
kIVlL*jr4d0      Allows you to define an interval of time in terms of years and months.
O'u-|.Bm3C?0      INTERVAL YEAR [(year_precision)] TO MONTHCNOUG博客首页:A0w:lP4N[*Z1]
        year_precision：year 的位数，范围：0~4，默认：2CNOUG博客首页 g9Y:n5\L
H
   INTERVAL DAY TO SECONDCNOUG博客首页/BG T.e#Y} uy+[f~fK
      Allows you to define an interval of time in terms of days, hours, minutes, and seconds (including fractional seconds).CNOUG博客首页1M@
E-K i\
H
      INTERVAL DAY [(day_precision)] TO SECOND [(frac_sec_prec)]
5uV
uC^4U/x0        day_precision：day 的位数，范围：0~9，默认：2
g@1^UwM:O#@2L"g0        frac_sec_prec：秒小数部分保留的位数，范围：0~9，默认：6CNOUG博客首页YM1h2{p7j M#Z
       CNOUG博客首页#RS3MU$P+A
    Month、hour、minute、second 的精度是不用设置的，Oracle 会保证他们的范围分别在 0~11、0~23、0~59、0~59

CNOUG博客首页TMK.NJh I.ni8[5C
4.Datetime Conversions
)R&Cd+BW6q0    Date 范围：CNOUG博客首页poXm1r'r4o;o+g4G
      4712-01-01 B.C. —— 9999-12-31 A.D.CNOUG博客首页8o$k&lZYA*U
     
/QWFM?/q0   From Strings to Datetimes
5Ap/}a6u]9?-w+t0      隐式：
)C_;s$kAg~I|:x0        根据 NLS_DATE_FORMAT 的格式写 String 的值，Oracle 会隐式转换，如果和 NLS_DATE_FORMAT 不匹配，不能转换。
0e],y7f
s8kY0      显式（使用内建函数）：
m/uJ,O@ [F&f0        TO_DATE( string[, format_mask[, nls_language]])CNOUG博客首页2Z]\FJA7F TV$v
        TO_DATE( number[, format_mask[, nls_language]])
#X)U.X{F+H0          用数字表示 Julian date 转换为 Date 类型，此时 format_mask = 'J'，number 表示从 January 1, 4712 B.C. 开始的天数，由于 Oracle 中最大日期是 December 31, 9999 A.D.，所以 number 的范围为：1 ~ 5373484CNOUG博客首页Wd7cV;n&v A
        TO_TIMESTAMP( string[, format_mask[, nls_language]])
Vj5d _$I0        TO_TIMESTAMP_TZ( string[, format_mask[, nls_language]])
?1Z'B%T&V0          此函数用于将 string 转换为 TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONECNOUG博客首页7lV4k1S"z |z;Z:T
        format_mask：CNOUG博客首页x5O2z Rq
          默认为 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT（分别对应 TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ）CNOUG博客首页)`f.{n.H _Z
          对于 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 的第一个字符决定。
v\$U8mk0WN0        nls_language：
0r \G@.P)V'il|'V0          Optionally specifies the language to be used to interpret the names and abbreviations of both months and days in the string.
}(L)@,e*mY0        几个限制：
r i:Tp??0          1.传给 TO_DATE 中的 string 长度不能超过 220 个字符。
9u6r Gg8D0          2.format mask 中 Julian date element (J) 和 the day of year element (DDD) 不能同时出现。
_j:v2| H;?g0          3.format mask 中 date/time 的某一个部分不能重复出现，比如：'YYYY-YYY-DD-MM'
dV,a%~"S/u0          4.format mask 中 HH24 不能和 am/pm 同时出现CNOUG博客首页+k8\U uNz
         
?"{yt}9f6N$O'}[ ~0   From Datetimes to Strings
1R/n0Z!JQ0      使用 TO_CHAR，默认格式 'DD-MON-RR'（9 位），可以用 NLS_DATE_FORMAT 覆盖。CNOUG博客首页#zc@5[N:?#v
      对于 TIMESTAMP 类型，秒的小数部分可以用 FF1 ~ FF9 来表示保留几位（自动四舍五入）。CNOUG博客首页)?:UZx%V4B
      不能将用于 TIMESTAMP 的 format_mask 用于 DATE 类型，否则会报 ORA-01821，反过来可以。
;J u6ELm%@A0     
3jw*\LU0   Working with Time ZonesCNOUG博客首页~2e7g;\-@;v
      明确表示某一个时区，应该联合使用 TZH TZM 或者 TZR TZDCNOUG博客首页}e4z9i3o.[DP
        TZH：与 UTC 之间 HOUR 的偏移CNOUG博客首页"gI5gx6WA2U#n-g
        TZM：与 UTC 之间 MINUTE 的偏移CNOUG博客首页.{#_8gms,bx
        TZR：The time zone region
Fw)^-dj}F0        TZD：The abbreviated time zone nameCNOUG博客首页F4f4d3U8w.gEd
        注：后两者可以查看 V$TIMEZONE_NAMES 获得
HrN [6GC0            对于时区，存入的是什么信息，显示的也是什么信息，比如用 TZH：TZM 存入和 UTC 之间的偏移，就只能显示类似 +08：00 的时区偏移，而无法显示具体哪个 time zone region
r'atF#M7j#o WOV0       
u1T4FB&h5N;M0   二位数年份的处理CNOUG博客首页5]%~
RdIULjj
      使用 RR/RRRR 时，Oracle 自动根据现在的年份辨别输入的二位数年份：CNOUG博客首页\zoiv
C;L9m;r
        1.如果现在是前半世纪（0~49）
` Y9T&Ex&E4b0          如果输入的是前半世纪（0~49），那么返回本世纪的年份
U#p4cF1E5N0          如果输入的是后半世纪（50~99），那么返回上世纪的年份CNOUG博客首页aE%z^T
        2.如果现在是后半世纪（50~99）
AP2?]r0w,?/w"{0          如果输入的是前半世纪（0~49），那么返回下世纪的年份CNOUG博客首页rex|R
          如果输入的是后半世纪（50~99），那么返回本世纪的年份CNOUG博客首页T p.sJ6a2F
      使用 YY/YYYY 时，不管输入的是什么，都返回本世纪的年份
$l#p%F,tl!_0      注：这种自动转换只适用于 String -> Date 的转换，如果是 Date -> String，那就按照 Date 存储的值来转换，此时再用 RR/RRRR 已经没有意义了，因为 Oracle 内部存储的年份是四位数的。CNOUG博客首页e&|)C-[fz ]Ud
         

5.Date and Timestamp LiteralsCNOUG博客首页[Yt[$p B?
    这是 9i 之后 引入的 ISO SQL standard 格式，格式是固定的，不能更改，也不受环境变量影响，因此可以作为常量来使用
et Ky.]:r v0      DATE 'YYYY-MM-DD'
+O0CK'z5R5F7QSb0      TIMESTAMP 'YYYY-MM-DD HH:MI:SS[.FFFFFFFFF] [{+|-}HH:MI]' （HH 必须是 24 小时制的，FFFFFFFFF 可选 1~9 位，也可以没有，时区也可以使用 time zone region（EST），但这只是 Oracle 提供的格式，不是 ANSI/ISO standards）
RS7iScpg#X0    例子：CNOUG博客首页!Y;d,i5E7pdw
kt
      DATE '2002-02-19'CNOUG博客首页q;sqt!O.So
      TIMESTAMP '2002-02-19 14:00:00.000000000 -5:00';
5s,Q)\7{}*h ]0     CNOUG博客首页,|gnpaR:_:r2GK N
     CNOUG博客首页t^|4R `}
6.Interval ConversionsCNOUG博客首页/`4Z\-QM
   Numbers to IntervalsCNOUG博客首页E#qL h qEB
      NUMTOYMINTERVAL ( n , 'char_expr' )CNOUG博客首页8_S_p7]
      NUMTODSINTERVAL ( n , 'char_expr' )CNOUG博客首页 J9Pzu*y0?h"W,}+F
      char_expr：
0p9L)A8HcqM0      Name       DescrīptionCNOUG博客首页h j%d9[:f
MNO
      YEAR       Some number of years, ranging from 1 through 999,999,999CNOUG博客首页#p*Wc1N|1\
      MONTH      Some number of months, ranging from 0 through 11
/F$k(~6A8L|}0      DAY        Some number of days, ranging from 0 to 999,999,999CNOUG博客首页(a3KS)LI
_W [8l]
      HOUR       Some number of hours, ranging from 0 through 23
)})vO+k @3B&LQ0      MINUTE     Some number of minutes, ranging from 0 through 59
2FBY:m%C,hS&\|$Q0      SECOND     Some number of seconds, ranging from 0 through 59.999999999CNOUG博客首页~3V3g5}`#T%sv
      以上不区分大小写
/j+owi+x0     
+qv7V1gT.haf A0   Strings to Intervals
WXg0n}|0      TO_YMINTERVAL('Y-M')CNOUG博客首页sD/ud(h
      TO_DSINTERVAL('D HH:MI:SS')
+J1fk3N~C|:q0      格式是固定的，不能缺少任何一个部分CNOUG博客首页y9\1KmwFIW^
 CNOUG博客首页N{,aoa:e'x eS"_d
 
g%yf6|;N4G07.Interval Literals
fPk7W msy$K0    语法： INTERVAL 'character_representation' start_element TO end_element
Q{yw4N
rH0           character_representation 不需要指明所有 datetime 的元素，但必须指明从 start_element 到 end_element 的所有连续元素，如果只有 start_element 只需要一个元素就行了，但 start_element 和 end_element 不能跨越 month 和 day。CNOUG博客首页;c{r0G.{g
           注：由于 bug 的问题，在 9i Releases 1 and 2 和 10g Release 1 中在 pl/sql 中指明部分元素会出错，比如 INTERVAL '1:02' HOUR TO MINUTE，但在 SQL 中不会。
T8lqV;V0           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）会自动转换CNOUG博客首页a*dj ~U
          CNOUG博客首页 vO&v0Xh N
          CNOUG博客首页Iu8O/X] ]e
8.CAST and EXTRACTCNOUG博客首页C Y i&{!] D
    两者都是 standard SQL functionsCNOUG博客首页$D;|6E6i7Rv,]
   CNOUG博客首页v&k `8F6qJZ
   CAST（Oracle8 开始）
Z$[ ^,tCv&xqM~3u~0      语法：CNOUG博客首页Pnr Wsz F
        CAST（var as type）CNOUG博客首页s/DPS3z3z:E]"O
        注：在 SQL 中，type 可以指定长度，比如：varchar2(40)，但在 pl/sql 中不能指定长度CNOUG博客首页:mk}#DW{&AJ
      可以在 string、datetime（DATE、TIMESTAMP）之间互相转换
$P$`&e/{c|}{.H0      由于无法指定 format，所以取决于 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT 环境变量

   EXTRACT（Oracle9i 开始）CNOUG博客首页7j |NSr&?`
      语法：
1fHl(S`0        EXTRACT (component_name, FROM {datetime | interval})
0zu'|0F.^0        component_name（不区分大小写）：CNOUG博客首页c^;\#Q {6Wg7ww
        Component name     Return datatypeCNOUG博客首页'LHDp Sp{y
        YEAR               NUMBERCNOUG博客首页y
a+gS h(IYL
        MONTH              NUMBER
;J+Ax"V.W vX m5ER0        DAY                NUMBERCNOUG博客首页\Q;[RJy @#|0?
        HOUR               NUMBER
1t&gDu3C {)vF|AeE4L0        MINUTE             NUMBERCNOUG博客首页'@ S^xr0C
        SECOND             NUMBER
^H0S(@ E{!_SL0        TIMEZONE_HOUR      NUMBERCNOUG博客首页:C jb#Vkb7w
        TIMEZONE_MINUTE    NUMBER
(z,z;I#~K9h;T6@'JJ0        TIMEZONE_REGION    VARCHAR2
6C2PU8yN:pY0        TIMEZONE_ABBR      VARCHAR2CNOUG博客首页DE$t B|(s/lN%B;jY
       CNOUG博客首页.et+S{T
      由于用 to_char 无法格式化 interval 的显示格式（格式是固定的），所以唯一的方法就是使用 EXTRACT 来格式化CNOUG博客首页"{|-R6}']'R"hqd3e
 

9.Datetime Arithmetic
umd$bO(V0   Adding and Subtracting Intervals to/from DatetimesCNOUG博客首页)o%Jjs/ao1GI
      1.和 Intervals 做 +- 操作。在和 INTERVAL DAY TO SECOND 进行运算时，不用考虑任何问题，但和 INTERVAL YEAR TO MONTH 做运算时，由于它是直接 +- 原始 datetimes 的年和月，所以可能够产生 ORA-01839 错误，应该有相关的 exception 处理语句。
'HPY6q0Y7T2r0      2.直接和 number 做 +- 操作。number 表示天数，可以用分数小数表示：
C mjc%e0        Value    Expression   Represents
-te-M9An rLO0        1/24     1/24         One hourCNOUG博客首页[FW+pS
        1/1440   1/24/60      One minute
"[k7h!Z4h
x0        1/86400  1/24/60/60   One secondCNOUG博客首页AX6o9Aa"m
        注：不建议进行约分等操作，这样使得程序一目了然。CNOUG博客首页$`c'MM;G$x*O)K/S
      3.使用 ADD_MONTHS 函数。CNOUG博客首页V/`4s9R-iUT\T
          1.如果输入的日期是输入月份中的最后一天，那么结果也是结果月份的最后一天
5`^Pz.M7fG0y0            比如：CNOUG博客首页d.[ o,z'\7]4| W
              add_months(Date '2006-02-28',1)  --> 2006-03-31CNOUG博客首页R!l*Ep*L
          2.如果结果月份的最后一天小于输入月份的日子，那么结果日期是结果月份的最后一天CNOUG博客首页
|;Lj^E
            比如：
(J4F\i$O V0              add_months(Date '2006-03-30',-1)  --> 2006-02-28CNOUG博客首页f6mae;vUL
         
]!U1g}R$X*s/JQ0          可以构建一个自定义函数，解决 1 的问题，让他不返回月份的最后一天CNOUG博客首页3Be,eU2Hw]&W2NZ
            FUNCTION my_add_months (CNOUG博客首页w5\ OJGH!Z
g5_1H
               date_in IN DATE, months_shift IN NUMBER)
B:~%g| T8H
j0            RETURN DATE ISCNOUG博客首页w*r1VKF,Ci]$|
               date_out DATE;
1pzg2u(f0               day_in NUMBER;CNOUG博客首页8v;z.G}+^
               day_out NUMBER;
7C%J%MV+L"W@0            BEGINCNOUG博客首页!@ }5~Id1{
               date_out := ADD_MONTHS(date_in, months_shift);CNOUG博客首页\X4?YB
wq#f
               day_in := TO_NUMBER(TO_CHAR(date_in,'DD'));
ME(q`:`*H;aL0               day_out := TO_NUMBER(TO_CHAR(date_out,'DD'));CNOUG博客首页 {iXw/K A!M'P
               IF day_out > day_inCNOUG博客首页(e1b!R;mTH"v5A
               THEN
fOvlI'v0                  date_out := date_out - (day_out - day_in);CNOUG博客首页i'q}!}hN3M
               END IF;CNOUG博客首页ChnJA!WQX
               RETURN date_out;CNOUG博客首页+?VEx;l5]VW4\
            END;CNOUG博客首页u.geC7}
e
      建议：CNOUG博客首页v
PWp,q
        虽然以上三者 DATE TIMESTAMP 都可以操作，但建议 TIMESTAMP 使用 1，DATE 可以使用 2、3
,]#B6w}#F&U z[RZ0           CNOUG博客首页_Uw4R1tvy]D
   Computing the Interval Between Two DatetimesCNOUG博客首页Y ngET?{Wq
      两个 TIMESTAMP 相减返回的永远是 INTERVAL DAY TO SECOND
tM2Yh V:V0      两个 DATE 相减返回的是天数，如果有小数部分，意思是 hours, minutes, and seconds 转换为天的结果
n]v^op*vjH0      MONTHS_BETWEEN 函数：CNOUG博客首页
k#h*CoPX:@
        定义：CNOUG博客首页T@ uH^.rTa'D T
              FUNCTION MONTHS_BETWEEN (date1 IN DATE, date2 IN DATE)CNOUG博客首页5{.d nc'\
](Xx!@{
                RETURN NUMBERCNOUG博客首页L"i$Z5a0e7R QJ
              尝试了一下也可以使用 TIMESTAMP，但还是建议不使用，不知道会出现什么问题。CNOUG博客首页OU"V*o:v!p
        1.date1 > date2 返回正数，小于返回负数，相等为0CNOUG博客首页4vMqAe5E-g [
        2.如果在同一年的同一月中，结果的范围是 > -1 and < 1，1 和 -1 都是不会到达的，一月按照 31 天计算，TIME 部分也要计算
d;Z|b$M[7PCB2f0        3.如果 date1 date2 分别是相应月份中的第一天或者最后一天，则返回整数，且忽略 TIME 部分的值CNOUG博客首页!FW+uZ0ExTH
        4.如果 date1 date2 在不同的月份中，且至少有一个不是该月的第一天或者最后一天，那么返回小数。小数是基于 31 天为一个月计算的，且要计算 TIME 部分。
"s!k4I(z6q6uXO%zh0       
w0_LrkI3]1W]0   Mixing DATEs and TIMESTAMPsCNOUG博客首页7k1uP%Jqq
      由前面可知：TIMESTAMPs 相减返回 INTERVAL DAY TO SECOND，DATEs 相减返回 numeric value
{-MX7D-}@0      那么：CNOUG博客首页"H`3Y#t ] y K
        1.如果 DATEs 相减想返回 INTERVAL DAY TO SECOND 需要用 CAST 显示地将 DATEs 转换为 TIMESTAMPs
0ovod{&J;^)ir0            CAST(a AS TIMESTAMP) - CAST(b AS TIMESTAMP)
%`2aq?vs0        2.如果 DATE 和 TIMESTAMP 混合使用，Oracle 隐式地将 DATE 转换为 TIMESTAMP，因此返回的是 INTERVAL DAY TO SECOND
.N3i?4{_0       
K'a1`3k*?3J\*\0   Adding and Subtracting IntervalsCNOUG博客首页
t{Y T2R
      INTERVAL 相加减必须满足类型相同：两个 INTERVAL DAY TO SECOND 相加减，或者两个 INTERVAL YEAR TO MONTH 相加减，不能混合使用。
"@o N%vbBk W0     CNOUG博客首页uY{^C!H:g3U$E
   Multiplying and Dividing Intervals
D6v0O*B.B0      DATETIMEs 是不能进行乘除运算的，但是 Intervals 可以。进行乘除运算时，每一个元素都会进行运算，如果超过该元素的范围就往上一级元素进位，如果出现小数就将小数部分转化为下一级元素，秒除外。
.TeT,V8w$J)s0   
f&?'Z5e7GH0}4p'M'E0   Using Unconstrained INTERVAL Types
%P"z"X'f1uk\0      由于在函数、过程中，参数不能指定精度，因此当 INTERVAL 传入时如果精度大于默认精度：YEAR(2),DAY(2),SECOND(6)，会返回错误 ORA-01873: the leading precision of the interval is too small
/] P(UK:Lf0      因此，引入两个特殊类型：
1|"XA!L%B c&kU hP7F0        YMINTERVAL_UNCONSTRAINED
!Ub w/jo nr0          接受任何精度的 INTERVAL YEAR TO MONTH
g"D#l5Iy0}*t,H:W0        DSINTERVAL_UNCONSTRAINEDCNOUG博客首页 PVW2v[-j:|
          接受任何精度的 INTERVAL DAY TO SECOND

CNOUG博客首页yA
Z:VQNS
10.Date/Time FunctionsCNOUG博客首页%FF H p6L}M0u
    对于传统的 DATE 函数，比如 ADD_MONTHS 建议不要用于 TIMESTAMP，Oracle 会将他们隐式地转为 DATE，这样会丢失秒的小数部分，还会将时区修改为 SESSION 的时区。因此，对于 TIMESTAMP 应该使用 INTERVAL 来操作。