日期时间函数剖析.ppt

Oracle9i/10g 日期时间函数 目标 完成本课后, 您应当能够使用下面的日期时间函数： TZ_OFFSET CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT FROM_TZ TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL 时区 Oracle9i 日期时间支持 在 Oracle9i 中，你能够在你的日期和时间数据中包含时区，并且提供对小数秒的支持 3 中新的日期时间数据类型： TIMESTAMP TIMESTAMP WITH TIME ZONE (TSTZ) TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) Oracle9i 服务器提供对日期时间数据类型的夏令时支持 TZ_OFFSET CURRENT_DATE CURRENT_DATE 对于会话时区是敏感的 在罗马日历中返回值是日期 CURRENT_TIMESTAMP CURRENT_TIMESTAMP 对于会话时区是敏感的 返回值是 TIMESTAMP WITH TIME ZONE 数据类型 LOCALTIMESTAMP DBTIMEZONE 和 SESSIONTIMEZONE EXTRACT 用 FROM_TZ 转换 TIMESTAMP 用 TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 转换 STRING 到 TIMESTAMP 用 TO_YMINTERVAL 转换时间间隔 小结 TZ_OFFSET FROM_TZ TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT 练习 16 概览 本章练习包括使用 Oracle9i 日期时间函数： TIMESTAMP Conversion Using FROM_TZ The FROM_TZ function converts a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value. The syntax of the FROM_TZ function is as follows: FROM_TZ(TIMESTAMP timestamp_value, time_zone_value) where time_zone_value is a character string in the format TZH:TZM or a character expression that returns a string in TZR (time zone region) with optional TZD format (TZD is an abbreviated time zone string with daylight savings information.) TZR represents the time zone region in datetime input strings. Examples are Australia/North, UTC, and Singapore. TZD represents an abbreviated form of the time zone region with daylight savings information. Examples are PST for US/Pacific standard time and PDT for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the V$TIMEZONE_NAMES dynamic performance view. The example in the slide converts a TIMESTAMP value to TIMESTAMP WITH TIME ZONE. STRING To TIMESTAMP Conversion Using TO_TIMESTAMP and TO_TIMESTAMP_TZ The TO_TIMESTAMP function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a valu