Know Oracle Date And Time Function

Oracle9i 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.

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

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.

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 Oracle9i 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 DBTIMEZONEfunction 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 DATABASEstatement. 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 SESSIONTIMEZONEfunction 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 SESSIONcommand. 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 DATEdata 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 displacementin 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 TIMESTAMPdata 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

Note: The following pages contain a detailed explanation of the functions listed above.

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. Format

SELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND]  [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);

Using the EXTRACT function, Richard can extract any of the components mentioned in the preceding syntax. Example Richard can query the time zone displacement for the current session as follows:

SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) "Hour",                         
EXTRACT(TIMEZONE_MINUTE FROM CURRENT_TIMESTAMP) "Minute" FROM DUAL;

Datetime Functions: Conversion

Now examine some additional functions that help convert a CHAR value to a TIMESTAMP value, a TIMESTAMP value to a TIMESTAMP WITH TIME ZONEvalue, and so on. The functions are:

	TO_TIMESTAMP
	TO_TIMESTAMP_TZ
	FROM_TZ

TO_TIMESTAMP The TO_TIMESTAMP function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMPdata type. Format TO_TIMESTAMP(char,[fmt],['nlsparam']) The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type. The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session. Example SELECT TO_TIMESTAMP('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

TO_TIMESTAMP_TZ

The TO_TIMESTAMP_TZ function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP WITH TIME ZONEdata type. Format TO_TIMESTAMP_TZ (char,[fmt],['nlsparam']) The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type. The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session. Example SELECT TO_TIMESTAMP_TZ('2000-12-01 11:00:00 -08:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL; Note: The TO_TIMESTAMP_TZ function does not convert character strings to TIMESTAMP WITH LOCAL TIME ZONE.

FROM_TZ

The FROM_TZ function converts a timestamp value to a TIMESTAMP WITH TIME ZONEvalue. Format FROM_TZ(timestamp_value, time_zone_value) Time_zone_value can be a character string in the format 'TZH:TZM' format or a character expression that returns a string in TZR (time zone region) format with optional TZD (time zone displacement) format. Example Using the Format TZH:TZM SELECT from_tz(TIMESTAMP '2000-12-01 11:00:00', '-8:00') "FROM_TZ" FROM DUAL; Example Using TZR SELECT FROM_TZ(TIMESTAMP '2000-12-01 11:00:00', 'AUSTRALIA/NORTH') "FROM_TZ" FROM DUAL;

INTERVAL Data Type

The INTERVALdata type is used to represent the precise difference between two datetime values. The two INTERVAL data types introduced in Oracle9i are:

	INTERVAL YEAR TO MONTH
	INTERVAL DAY TO SECOND

Usage of the INTERVAL Datatype

The INTERVAL data type can be used to set a reminder for a time in the future or check whether a certain period of time has elapsed since a particular date. For example: You can use it to record the time between the start and end of a race.

INTERVAL YEAR TO MONTH

You can use the INTERVAL YEAR TO MONTHdata type to store and manipulate intervals of years and months. Format INTERVAL YEAR[(precision)] TO MONTH Where precision specifies the number of digits in the years field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0-4. The default value is 2. Automated Generation of Expiration Date The packaging department of Home Food Products Ltd has decided to automate the generation of the expiration date details of its products.

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECONDstores a period of time in terms of days, hours, minutes, and seconds. Format INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)] Where day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. Fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. Automated Generation of the Arrival Time The Railway Enquiry department wants to automate the generation of the arrival time for all of its trains. You have just learned about the new INTERVAL data types introduced with the Oracle9iserver.

INTERVAL YEAR TO MONTH:

The data type INTERVAL YEAR TO MONTH is used to store and manipulate intervals of years and months. TO_YMINTERVAL function: The TO_YMINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH type, where CHAR is the character string to be converted.

INTERVAL DAY TO SECOND:

The INTERVAL DAY TO SECOND data type stores a period of time in terms of days, hours, minutes, and seconds. TO_DSINTERVAL function: The TO_DSINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND data type.