By default, MemSQL uses the time zone setting on the host (the Linux OS). You can update the time zone on the host or through MemSQL via the default_time_zone configuration option.

Setting the Time Zone on the Host (Linux OS)

If you are setting the time zone on the host (the Linux OS), then you must set the time zone to the same value on every host in your cluster.

Note: It is recommended to execute the commands first on a development or a test cluster.

The following are the steps to set the time zone on RHEL OS:

1. Shutdown the MemSQL cluster.

2. Execute the following commands on each node of the cluster:

   timedatectl status
   timedatectl set-timezone Europe/London
   timedatectl status
   

   Note: “Europe/London” is used as an example in the code block above; however, you can set the time zone to any other location as well.

3. Start the MemSQL cluster.

Setting the Time Zone in MemSQL

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 MemSQL cluster and then restart the cluster. For example, to set the time zone to UTC on a host, run the command:

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

And then restart the cluster by running the following command:

memsql-admin restart-node --all

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

memsql-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 MemSQL is time_zone (which is set in default_time_zone). So, the CURRENT_TIMESTAMP() or LOCALTIMESTAMP() displays the actual time zone used by MemSQL:

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

When you query DATETIME values, MemSQL 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.

Time Zone Engine Variables

system_time_zone variable

This variable stores the time zone set on the host (the Linux OS). It is a read-only variable and read by the engine during startup.

SELECT @@SYSTEM_TIME_ZONE;
****
+--------------------+
| @@SYSTEM_TIME_ZONE |
+--------------------+
| UTC                |
+--------------------+

time_zone variable

This variable exists in MemSQL only to conform to MySQL standards. Setting this variable does not do anything. If MemSQL is using the time zone setting of the host (the Linux OS), then the time_zone variable returns the value SYSTEM. If you set the time zone through MemSQL, via the default_time_zone configuration option, then the time_zone variable returns the value of default_time_zone.

SELECT @@TIME_ZONE;
****
+-------------+
| @@TIME_ZONE |
+-------------+
| SYSTEM      |
+-------------+

Was this Article Helpful?

Thanks For Your Feedback!