Setting the Time Zone in SingleStoreDB
The default_time_zone
configuration option can be used to set the time zone by specifying an offset from UTC time. While updating a time zone on a host, it is required to set it identically on all hosts in the SingleStoreDB cluster and then restart the cluster. For example, to set the time zone to UTC on a host, run the command:
sdb-admin update-config --key default_time_zone --value "+00:00" --all
And then restart the cluster by running the following command:
sdb-admin restart-node --all
For example, to specify an offset of 01 hour from UTC, run the following command and restart the cluster.
sdb-admin update-config --key default_time_zone --value "+01:00" --all
Note: default_time_zone
is a startup configuration option and not engine variable, so you cannot read or write it using the SELECT
or SET
command.
When you set default_time_zone
(for example to the value UTC
), then the time_zone variable is set to the same value as default_time_zone
.
SELECT @@TIME_ZONE; **** +-------------+ | @@TIME_ZONE | +-------------+ | UTC | +-------------+
The actual time zone used by SingleStoreDB is time_zone
(which is set in default_time_zone
). So, the CURRENT_TIMESTAMP()
or LOCALTIMESTAMP()
displays the actual time zone used by SingleStoreDB:
SELECT CURRENT_TIMESTAMP(); **** +---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2020-05-26 11:27:56 | +---------------------+
When you query DATETIME
values, SingleStoreDB displays the timestamp according to the default_time_zone
setting.
For example, assume that the default_time_zone
is set to -4:00
and you insert a DATETIME
value 2020-07-08 15:40:52
. Now, you set the default_time_zone
to -7:00
and then query the same DATETIME
value. You will notice that the value is now 2020-07-08 12:40:52
. The queried value differs from the inserted value by the same difference between the current and previous default_time_zone
values.