Setting the Time Zone in SingleStore

Use the default_time_zone configuration option to set the time zone by specifying an offset from UTC time or a named time zone. While updating a time zone on a host, it is required to set it identically on all hosts in the SingleStore cluster and then restart the cluster. For example, to set the time zone to UTC on a host, run the command:

Shell

sdb-admin update-config --key default_time_zone --value "+00:00" --all

And then restart the cluster by running the following command:

Shell

sdb-admin restart-node --all

For example, to specify an offset of 01 hour from UTC, run the following command and restart the cluster.

Shell

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.

SQL

SELECT @@TIME_ZONE;

+-------------+
| @@TIME_ZONE |
+-------------+
| UTC         |
+-------------+

The actual time zone used by SingleStore is time_zone (which is set in default_time_zone). So, the CURRENT_TIMESTAMP() or LOCALTIMESTAMP() displays the actual time zone used by SingleStore:

SQL

SELECT CURRENT_TIMESTAMP();

+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2020-05-26 11:27:56 |
+---------------------+

When you query DATETIME values, SingleStore displays the timestamp as it was originally entered. The value is not adjusted 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 remains 2020-07-08 15:40:52.

Set a Named Time Zone

SingleStore supports assigning named time zones in IANA format to default_time_zone. Named time zones use regional time-zone rules, including daylight saving time changes where applicable. The following example sets default_time_zone to a named time zone:

Shell

# Set a named time zone
sdb-admin update-config --key default_time_zone --value "America/New_York" --all

# Restart all the nodes to apply the change
sdb-admin restart-node --all

A fixed offset, such as +09:00 or -05:00, remains constant and does not automatically adjust for daylight saving time. Use a named time zone instead when the workload must follow a region’s time-zone rules, including daylight saving time changes where applicable.

Setting the Time Zone in SingleStore

On this page

Set a Named Time Zone

Was this article helpful?

On this page

Was this article helpful?