Oracle9
i
provides extended date and time support across different time zones with the help of new datetime data types and functions. To understand the working of these data types and functions, it is necessary to be familiar with the concept of time zones. This topic group introduces you to the concepts of time such as Coordinated Universal Time, time zones, and daylight saving time.
The earth is divided into twenty four time zones, one for each hour of the day. The time along the prime meridian in Greenwich, England is known as Coordinated Universal Time, or UTC (formerly known as Greenwich Mean Time, or GMT ). UTC is the time standard against which all other time zones are referenced.
Note:
The following topics discuss prime meridian and UTC in more detail.
Since time began, the time flow on earth has been ruled by the apparent position of the sun in the sky. In the past, when methods of transportation made even short travels last for several days, no one, except astronomers, understood that solar time at any given moment is different from place to place. Around the 1800s with the development of faster modes of transportation and a need for accurate time references for sea navigation, Greenwich mean time (GMT), which later became known as
Coordinated Universal Time (UTC)
, was introduced. The earth surface is divided into 24 adjacent, equal, and equatorially perpendicular zones, called time zones. Each time zone is delimited by 2 meridians. UTC is the time standard against which all other time zones in the world are referenced. UTC is measured with astronomical techniques at the Greenwich astronomical observatory in England.
The main purpose of
daylight saving time
(called Summer Time in many places around the world) is to make better use of daylight. By switching clocks an hour forward in summer, we can save a lot of energy and enjoy sunny summer evenings. Today approximately 70 countries use daylight saving time.
When Is Daylight Saving Time Observed Around the World?
Equatorial and tropical countries from the lower latitudes do not observe daylight saving time. Because the daylight hours are similar during every season, there is no advantage to moving clocks forward during the summer.
How Is This Information Relevant To Time Zones?
The world is divided into 24 time zones and UTC is the time standard against which all other time zones in the world are referenced. When daylight saving time comes into effect in certain countries, the time zone offset for that country is adjusted to accomodate the change in time.
For example:
The standard time zone offset for Geneva, Switzerland is UTC +01:00 hour. But when daylight saving time comes into effect the time zone offset changes to UTC +02:00 hours. The time zone offset changes to UTC +01:00 hour again, on the last Sunday in October, when the daylight saving time comes to an end.
Database Time Zone
Database time zone refers to the time zone in which the database is located.
Session Time Zone
Session time zone refers to the user's time zone, from where he or she has logged on to the database. Global Corporation is a finance company with offices around the world. The company head office is located in Barcelona (time zone : +01 hours). The company database is located in New York (time zone : -05 hours). Miguel from Sydney (time zone : +10 hours) has established a connection to the database.
You can set the database's default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone.
SESSIONTIMEZONE
The
SESSIONTIMEZONE
function returns the value of the session's time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent
ALTER SESSION
statement.
Note:
For a listing of valid time zone name values, you can query the
V$TIMEZONE_NAMES
dynamic performance view.
Richard then uses any of the following functions to view the current date and time in the session time zone.
Note:
The following pages contain a detailed explanation of the functions listed above.
Objectives
After completing this topic group, you should be able to: | Calculate the date and time for any time zone region using time zone offsets. |
Time Zones
The hours of the day are measured by the turning of the earth. The time of day at any particular moment depends on where you are.
The earth is divided into twenty four time zones, one for each hour of the day. The time along the prime meridian in Greenwich, England is known as Coordinated Universal Time, or UTC (formerly known as Greenwich Mean Time, or GMT ). UTC is the time standard against which all other time zones are referenced.
Note:
The following topics discuss prime meridian and UTC in more detail.
Coordinated Universal Time
Since time began, the time flow on earth has been ruled by the apparent position of the sun in the sky. In the past, when methods of transportation made even short travels last for several days, no one, except astronomers, understood that solar time at any given moment is different from place to place. Around the 1800s with the development of faster modes of transportation and a need for accurate time references for sea navigation, Greenwich mean time (GMT), which later became known as
Coordinated Universal Time (UTC)
, was introduced. The earth surface is divided into 24 adjacent, equal, and equatorially perpendicular zones, called time zones. Each time zone is delimited by 2 meridians. UTC is the time standard against which all other time zones in the world are referenced. UTC is measured with astronomical techniques at the Greenwich astronomical observatory in England.
Daylight Saving Time
"Just as sunflowers turn their heads to catch every sunbeam, there is a simple way to get more from the sun."
Purpose of Daylight Saving Time
The main purpose of
daylight saving time
(called Summer Time in many places around the world) is to make better use of daylight. By switching clocks an hour forward in summer, we can save a lot of energy and enjoy sunny summer evenings. Today approximately 70 countries use daylight saving time.
When Is Daylight Saving Time Observed Around the World?
| Country | Begin Daylight Saving Time | Back to Standard time |
| US; Mexico; Canada | 2:00 a.m. on the first Sunday of April | 2:00 a.m. on the last Sunday of October |
| European Union | 1:00 a.m. on the last Sunday in March | 2:00 a.m. on the last Sunday of October |
Summary
The key learning points in this topic group included: Coordinated Universal Time: UTC is the time standard against which all other time zones in the world are referenced. UTC Conversion: To convert UTC to local time, you add or subtract hours from it. For regions west of the zero meridian to the international date line (which includes all of North America), hours are subtracted from UTC to convert to local time. Daylight Saving Time: Daylight saving time is used to make better use of daylight hours by switching clocks an hour forward in summer. All this information is necessary to understand how the Oracle9 i server provides support for time zones in its multi geography applications. The next topic group "Database Time Zone Versus Session Time Zone" discusses the difference between Database Time Zone and Session Time Zone.Database Time Zone Versus Session Time Zone
Database Time Zone
Database time zone refers to the time zone in which the database is located.
Session Time Zone
Session time zone refers to the user's time zone, from where he or she has logged on to the database. Global Corporation is a finance company with offices around the world. The company head office is located in Barcelona (time zone : +01 hours). The company database is located in New York (time zone : -05 hours). Miguel from Sydney (time zone : +10 hours) has established a connection to the database.
DBTIMEZONE
The DBTIMEZONE function returns the value of the database time zone. The default database time zone is the same as the operating system's time zone. The return type is a time zone offset (a character type in the format '[+ | -] TZH : TZM ' ) or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.
You can set the database's default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone.
SESSIONTIMEZONE
The
SESSIONTIMEZONE
function returns the value of the session's time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent
ALTER SESSION
statement.
Altering the Session Time Zone
How can I change the session time zone? The session time zone for a session can be changed with an ALTER SESSION command. Syntax ALTER SESSION SET TIME ZONE = '[+ |-] hh:mm'; The key learning points in this topic group included: Database Time Zone: Database time zone refers to the time zone in which the database is located. You can use the DBTIMEZONE function to query the value of the database time zone. Session Time Zone: Session time zone refers to the time zone from which the user has logged on to the database. You can use the SESSIONTIMEZONE function to query the value of the session time zone.TIMESTAMP
The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type; the hour, minute, and second values; as well as the fractional second value. Format TIMESTAMP [(fractional_seconds_precision)] The fractional_seconds_precision is used to specify the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.Grand Prix Qualifying Run
The line-up position for the Formula 1 Grand Prix is determined by the results of the qualifying run. Because the difference between the finishing times of the various drivers is very close, the finishing time of each driver is measured in fractional seconds. To store this kind of information, you can use the new TIMESTAMP data type.TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE is a variant of the TIMESTAMP data type, that includes a time zone displacement in its value. Format TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE Earthquake Monitoring Station Earthquake monitoring stations around the world record the details of tremors detected in their respective regions. The date and time of the occurrence of these tremors are stored, along with the time zone displacement, using the new TIMESTAMP WITH TIME ZONE data type. This helps people who analyze the information from locations around the world obtain an accurate perspective of the time when the event occurred.TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE is another variant of the TIMESTAMP data type. This data type also includes a time zone displacement. Format TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE The TIMESTAMP WITH LOCAL TIME ZONE datatype differs from TIMESTAMP WITH TIME ZONE in that when you insert a value into a database column, the time zone displacement is used to convert the value to the database time zone. Example When a New York client inserts TIMESTAMP'1998-1-23 6:00:00-5:00' into a TIMESTAMP WITH LOCAL TIME ZONE column in the San Francisco database. The inserted data is stored in San Francisco as binary value 1998-1-23 3:00:00. The time-zone displacement is not stored in the database column.When you retrieve the value, Oracle returns it in your local session time zone. When the New York client selects that inserted data from the San Francisco database, the value displayed in New York is `1998-1-23 6:00:00'. A San Francisco client, selecting the same data, gets the value '1998-1-23 3:00:00'. New Year Celebration Broadcast A television company is planning a live broadcast of New Year celebrations across the globe. To schedule a broadcast of the various events from across the globe, they use an application that stores the broadcast time using the TIMESTAMP WITH LOCAL TIME ZONE data type. Reporters located in different time zones can easily query to find out when to start and end their broadcasts, the output of which will be in their respective time zones. TIMESTAMP : With the new TIMESTAMP data type you can store the year, month, and day of the DATE data type; hour, minute, and second values; as well as the fractional second value. TIMESTAMP WITH TIME ZONE : The TIMESTAMP WITH TIME ZONE data type is a variant of the TIMESTAMP data type, that includes a time zone displacement in its value. TIMESTAMP WITH LOCAL TIME ZONE : The data stored in a column of type TIMESTAMP WITH LOCAL TIME ZONE is converted and normalized to the database time zone. Whenever a user queries the column data, Oracle returns the data in the user's local session time zone.TZ_OFFSET
Richard, a marketing executive, travels frequently to cities across the globe. He carries his laptop while travelling and updates the database located at the head office in San Francisco with information about his activities at the end of each day. Since Richard is using a laptop for his work, he needs to update the session time zone every time he visits a new city. Richard uses the TZ_OFFSET function to find the time zone offset for that city. Syntax SELECT TZ_OFFSET('Canada/Pacific') FROM DUAL;
Note:
For a listing of valid time zone name values, you can query the
V$TIMEZONE_NAMES
dynamic performance view.
ALTER SESSION Command
After Richard finds the time zone offset for the city he is visiting, he alters his session time zone using the ALTER SESSION command. ALTER SESSION SET TIME_ZONE = '-08:00';
Richard then uses any of the following functions to view the current date and time in the session time zone.
| CURRENT_DATE |
| CURRENT_TIMESTAMP |
| LOCAL_TIMESTAMP |
CURRENT_DATE
The CURRENT_DATE function returns the current date in the session's time zone.The return value is a date in the Gregorian calendar. (The ALTER SESSION command can be used to set the date format to 'DD-MON-YYYY HH24:MI:SS'.) The CURRENT_DATE function is sensitive to the session time zone. When Richard alters his session time zone to the time zone of the city that he is visiting, the output of the CURRENT_DATE function changes. Example Before the Session Time Zone is Altered After the Session Time Zone is Altered Observe in the output that the value of CURRENT_DATE changes when the TIME_ZONE parameter value is changed to -08:00. Note: The SYSDATE remains the same irrespective of the change in the TIME_ZONE . SYSDATE is not sensitive to the session's time zone.CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of the TIMESTAMP WITH TIME ZONE data type. The time zone displacement reflects the local time zone of the SQL session. Format CURRENT_TIMESTAMP (precision) Where precision is an optional argument that specifies the fractional second precision of the time value returned.LOCALTIMESTAMP
The LOCALTIMESTAMP function returns the current date and time in the session time zone in a value of TIMESTAMP data type. The difference between this function and the CURRENT_TIMESTAMP function is that LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value. Format LOCALTIMESTAMP (TIMESTAMP_precision) Where TIMESTAMP_precision is an optional argument that specifies the fractional second precision of the TIMESTAMP value returned.EXTRACT
So far you have learned how Richard can alter his session date and view the current date and time in the session time zone. Now observe how Richard can query a specified datetime field from a datetime or interval value expression using the EXTRACT function. FormatSELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND]
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1276708
1032
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
