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 DataExchange 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 < offset | LOCAL >
offset ::= <+|->hh:mm
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 offset 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 offset 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, time stamp literal values are interpreted as current local time. Time stamp values are always adjusted and stored internally using UTC time, and converted to local time upon retrieval. For more information, see Time Stamp Values.
Table 40 Time/Date Functions with SET TIME ZONE Default
If no time zone is specified, or TIME ZONE LOCAL is specified...
CURDATE(), CURTIME(), NOW(), SYSDATETIME()
Return current local time and date based on system clock.
Return current UTC time and date based on system clock and operating system locale setting.
Behavior When Offset is Specified – If a valid offset value is specified, then that value is used instead of the operating system time zone offset to generate values for CURDATE(), CURTIME(), NOW(), or SYSDATETIME(). For example, if a offset 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. Time stamp literals are interpreted as specifying a time such that if the offset value is subtracted from it, the result is UTC. Daylight savings is not a consideration, since the offset explicitly takes it into account. Time stamp values are always stored internally using UTC time.
Table 41 Time/Date Functions with SET TIME ZONE Specified
If a valid offset value is specified...
CURDATE(), CURTIME(), NOW(), SYSDATETIME()
These functions return current local time and date values by adding offset to the current UTC time/date values.
Because time stamp data is always stored as UTC, and literal time stamp 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.
Table 43 Time Stamp Data Type Examples
Statement
Value
SELECT NOW()
2001-10-01 12:05:00.123 displayed.
INSERT INTO t1 (c1) SELECT NOW()
2001-10-01 18:05:00.1234567 stored on disk.
SELECT * from t1
2001-10-01 12:05:00.123 displayed.
SELECT CURRENT_TIMESTAMP()
2001-10-01 18:05:00.123 displayed.
INSERT INTO t2 (c1) SELECT CURRENT_TIMESTAMP()
2001-10-01 18:05:00.1234567 stored on disk.
SELECT * from t2
2001-10-01 12:05:00.123 displayed.
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.
Now the same SELECT statement above returns the following:
2002-01-09 22:35:03.000 2002-01-09 12:35:03.000
22:35:03 12:35:03
2002-01-09 2002-01-09
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 time stamp 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 t1
Results:
c1 c2
----------------------- -----------------------
2002-01-09 16:35:03.000 2002-01-09 16:35:03.000
c3 c4 c5 c6
-------- -------- ---------- ----------
22:35:03 16:35:03 01/09/2002 01/09/2002
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 offset 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 t1
Results:
c1 c2
----------------------- -----------------------
2002-01-09 22:35:03.000 2002-01-09 22:35:03.000
c3 c4 c5 c6
-------- -------- ---------- ----------
22:35:03 16:35:03 01/09/2002 01/09/2002
============
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()
Results:
2002-01-09 22:35:03.000 2002-01-10 08:35:03.000
22:35:03 08:35:03
01/09/2002 01/10/2002
INSERT INTO t1 SELECT CURRENT_TIMESTAMP(), NOW(), CURRENT_TIME(), CURTIME(), CURRENT_DATE(), CURDATE()
SELECT * FROM t1
Results:
c1 c2
----------------------- -----------------------
2002-01-10 08:35:03.000 2002-01-10 08:35:03.000
c3 c4 c5 c6
-------- -------- ---------- ----------
22:59:55 08:59:55 01/09/2002 01/10/2002
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 time stamp values, no adjustment is made to them when they are retrieved from the database.