The SET TIME ZONE keyword allows you to specify a current time offset from Coordinated Universal Time (UTC) for your locale, overriding the operating system time zone setting where the database engine is located.

Any SET TIME ZONE statement remains in effect until the end of the current database session, or until another SET TIME ZONE statement is executed.

Valid range of hh is 00 - 12.

Valid range of mm is 00 - 59.

Either a plus (+) or a minus (-) sign is required as part of the displacement value.

Default Behavior – SET TIME ZONE LOCAL is the default behavior, which is the same as not using the SET TIME ZONE command at all. Under the default behavior, the database engine establishes its time zone based on the operating system where it is running. For example, SELECT CURTIME( ) returns the current local time, while SELECT CURRENT_TIME( ) returns the current UTC time, both based on local system time and the time zone setting in the operating system.

The LOCAL keyword allows you to restore default behavior after specifying a displacement value, without having to terminate and reopen the database session.

Under default behavior, literal time and date values, such as 1996-03-28 and 17:40:46 are interpreted as current local time and date. In addition, during inserts, TIMESTAMP literal values are interpreted as specifying current local time. TIMESTAMP values are always adjusted and stored internally using UTC time, and converted to local time upon retrieval.

Behavior When Interval Specified – If a valid displacement value is specified, then that value is used instead of the operating system time zone offset when generating values for NOW ( ), CURTIME( ), or CURDATE( ). For example, if a displacement of -02:00 is specified, then the local time value of CURDATE() is calculated by adding -02:00 to the UTC time returned from the operating system.

Under this behavior, time and date literals are interpreted as local time, at their face values. TIMESTAMP literals are interpreted as specifying a time such that if the displacement value is subtracted from it, the result is UTC. Daylight savings is not a consideration since the displacement explicitly takes it into account. TIMESTAMP values are always stored internally using UTC time.

To convert a given local time value to UTC, you must subtract your time zone displacement value from the local time value. In other words,

UTC time = local time – time zone displacement

See Table 42 for example conversions.

Because TIMESTAMP data is always stored as UTC, and literal Timestamp values (including values stored on disk) are always converted to local time when retrieved, the behavior of NOW ( ) and CURRENT_TIMESTAMP( ) values can be confusing. For example, consider the following table, assuming the database engine is located in Central Standard Time, U.S.

It is important to note that the value displayed by a direct SELECT NOW ( ) is not the same as the value stored on disk by the syntax INSERT SELECT NOW ( ). Likewise, note that the display value of SELECT CURRENT_TIMESTAMP( ) is not the same value that you will see if you INSERT the value of CURRENT_TIMESTAMP() then SELECT it, because the literal value stored in the data file is adjusted when it is retrieved.

In this example, no SET TIME ZONE statement has been issued yet, and the computer on which the database engine is running has its clock set to January 9, 2002, 16:35:03 CST (U.S.). Recall that CURRENT_TIMESTAMP() and the other CURRENT_ functions always return UTC time and/or date based on the system clock and locale settings of the computer where the database engine is running.

Results:

Note that CST is 6 hours ahead of UTC.

Now the same SELECT statement above returns the following:

Note that the value of NOW() changed after the SET TIME ZONE statement, but the value of CURRENT_TIMESTAMP() did not.

The following example demonstrates the difference between TIMESTAMP values that are stored as UTC values then converted to local values upon retrieval, and TIME or DATE values that are stored and retrieved at their face value. Assume that the system clock currently shows January 9, 2002, 16:35:03 CST (U.S.). Also assume that no SET TIME ZONE statement has been issued.

Results:

Observe that NOW() and CURRENT_TIMESTAMP() have different values when displayed to the screen with SELECT NOW(), CURRENT_TIMESTAMP(), but once the literal values are saved to disk, UTC time is stored for both values. Upon retrieval, both values are converted to local time.

By setting the time zone interval to zero, we can view the actual data stored in the file, because it is adjusted by +00:00 upon retrieval:

Results:

The following example demonstrates the expected behavior when the local date is different than the UTC date (for example, UTC is past midnight, but local time is not, or the reverse). Assume that the system clock currently shows January 9, 2002, 16:35:03 CST (U.S.).

Results:

As you can see, the UTC time and date returned by CURRENT_DATE() and CURRENT_TIME() are stored as literal values. Since they are not TIMESTAMP values, no adjustment is made to these values when they are retrieved from the database.

TIMESTAMP data type

Time and Date Functions