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