Les05 时间函数介绍.ppt

Managing Data in Different Time Zones Objectives After completing this lesson, you should be able to use the following datetime functions: TZ_OFFSET FROM_TZ TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL Time Zones TIME_ZONE Session Parameter TIME_ZONE may be set to: An absolute offset Database time zone OS local time zone A named region CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP CURRENT_DATE Returns the current date from the system Has a data type of DATE CURRENT_TIMESTAMP Returns the current timestamp from the system Has a data type of TIMESTAMP WITH TIME ZONE LOCALTIMESTAMP Returns the current timestamp from user session Has a data type of TIMESTAMP CURRENT_DATE Display the current date and time in the session’s time zone. CURRENT_TIMESTAMP Display the current date and fractional time in the session’s time zone. LOCALTIMESTAMP Display the current date and time in the session’s time zone in a value of TIMESTAMP data type. LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. DBTIMEZONE and SESSIONTIMEZONE Display the value of the database time zone. Display the value of the session’s time zone. TIMESTAMP Data Type The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type, plus hour, minute, and second values,as well as the fractional second value. Variations in TIMESTAMP are: TIMESTAMP[(fractional_seconds_precision)]_ TIMESTAMP [(fractional_seconds_precision)]_ WITH TIME ZONE TIMESTAMP [(fractional_seconds_precision)]_ WITH LOCAL TIME ZONE TIMESTAMP Data Types TIMESTAMP Fields Difference between DATE and TIMESTAMP TIMESTAMP WITH TIME ZONE Data Type TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone displacement in its value. The time zone displacement is the difference,in hours and minutes, between local time and UTC. It is specified as: TIMESTAMP WITH TIMEZONE: Example TIMESTAMP WITH LOCAL TIMEZO