Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

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?