| Prev | SQL Engine Reference | Next |
SET TIME ZONE
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.
Caution
You should always use the default behavior unless you have a specific need to override the time zone setting in your operating system. If you are using Pervasive replication or your application has dependencies on the sequential time order of records inserted, use of SET TIME ZONE to modify your time zone offset is not recommended.
Syntax
SET TIME ZONE < displacement | LOCAL > displacement ::= <+|->hh:mmEither a plus (+) or a minus (-) sign is required as part of the displacement value.
Remarks
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 re-open 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/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() will be 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 displacement is subtracted from it, the result is UTC. Daylight savings is not a consideration since 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 3-19 for example conversions.
A note about TIMESTAMP data type
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.
Examples
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.
SELECT CURRENT_TIMESTAMP(), NOW(), CURRENT_TIME(), CURTIME(), CURRENT_DATE(), CURDATE()2002-01-09 22:35:03.0000000 2002-01-09 16:35:03.0000000 22:35:03 16:35:03 2002-01-09 2002-01-09Note that CST is 6 hours ahead of UTC.
SET TIME ZONE -10:00Now the same SELECT statement above returns the following:
2002-01-09 22:35:03.0000000 2002-01-09 12:35:03.0000000 22:35:03 12:35:03 2002-01-09 2002-01-09Note 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.
CREATE TABLE t1 (c1 TIMESTAMP, c2 TIMESTAMP, c3 TIME, c4 TIME, c5 DATE, c6 DATE) INSERT INTO T1 SELECT CURRENT_TIMESTAMP(), NOW(), CURRENT_TIME(), CURTIME(), CURRENT_DATE(), CURDATE() SELECT * FROM T1c1 c2 --------------------------- --------------------------- 2002-01-09 16:35:03.0000000 2002-01-09 16:35:03.0000000 c3 c4 c5 c6 -------- -------- ---------- ---------- 22:35:03 16:35:03 2002-01-09 2002-01-09Observe 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:
SET TIME ZONE +00:00 SELECT * FROM T1c1 c2 --------------------------- --------------------------- 2002-01-09 22:35:03.0000000 2002-01-09 22:35:03.0000000 c3 c4 c5 c6 -------- -------- ---------- ----------22:35:03 16:35:03 2002-01-09 2002-01-09
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.).
SET TIME ZONE +10:00 SELECT CURRENT_TIMESTAMP(), NOW(), CURRENT_TIME(), CURTIME(), CURRENT_DATE(), CURDATE()2002-01-09 22:35:03.0000000 2002-01-10 08:35:03.0000000 22:35:03 08:35:03 2002-01-09 2002-01-10 INSERT INTO T1 SELECT CURRENT_TIMESTAMP(), NOW(), CURRENT_TIME(), CURTIME(), CURRENT_DATE(), CURDATE() SELECT * FROM T1c1 c2 --------------------------- --------------------------- 2002-01-10 08:35:03.0000000 2002-01-10 08:35:03.0000000 c3 c4 c5 c6 -------- -------- ---------- ---------- 22:59:55 08:59:55 2002-01-09 2002-01-10As 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.
See Also
TIMESTAMP data type
| Prev SET SECURITY |
Contents Up Check for Revisions | Next SET TRUENULLCREATE |