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.