MySQL Database learning



Home / General Questions / How to manage Time Zone in MySQL?
Share this job:


How to manage Time Zone in MySQL?

MySQL Server allows to set the current time zone on a per-connection basis. Time zone settings are determined by the time_zone system variable. To view current Time Zone run the following query:

SHOW VARIABLES LIKE "time_zone";

Time zone settings are determined by the time_zone system variable. The server maintains a global time_zone value, as well as a session time_zone value for each client that connects to . The session value is initialized for a given client, from the current value of the global time_zone variable, when the client connects.

The default setting for the global value is SYSTEM, which thus also becomes each client's initial session time_zone value. The global and session time zone settings can be retrieved with the following statement:

SELECT @@global.time_zone, @@session.time_zone;

MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval. The standard setting for both the server and the per-client connection is to use the SYSTEM setting, which the server retrieves from the host at startup.

If the time zone setting is the same for both storage and retrieval, you will get back the same value you store. If you store a TIMESTAMP value, and then change the time zone to a different value, the returned TIMESTAMP value will be different from the one you stored.

The following examples demonstrate how to change the session time zone settings to store and retrieve TIMESTAMP data. First, we set the session time zone to UTC, that is, '+00:00':

mysql> SET time_zone = '+00:00';

mysql> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| +00:00 |
+---------------------+

Next, we create a simple table containing just a TIMESTAMP column named ts and insert one record that assigns the current time to ts. Then we retrieve the record:

mysql> CREATE TABLE ts_test (ts TIMESTAMP);

mysql> INSERT INTO ts_test (ts) VALUES (NULL);

mysql> SELECT * FROM ts_test;
+---------------------+
| ts |
+---------------------+
| 2005-01-04 20:50:18 |
+---------------------+

Finally, we change the session time zone twice, each time retrieving the value after the change. This demonstrates that, even though we're retrieving the same TIMESTAMP value, the change in time zone setting causes the "localized" display value to be different each time:

mysql> SET time_zone = '+02:00';

mysql> SELECT * FROM ts_test;
+---------------------+
| ts |
+---------------------+
| 2005-01-04 22:50:18 |
+---------------------+
mysql> SET time_zone = '-05:00';

mysql> SELECT * FROM ts_test;
+---------------------+
| ts | +---------------------+
| 2005-01-04 15:50:18 |
+---------------------+

The per-connection time zone settings also influence other aspects of the MySQL server that depend on the current time, most notably the function NOW().

MySQL Server also supports the CONVERT_TZ() function, which performs time zone conversions of datetime values:

mysql> SELECT CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00');
+-------------------------------------------------------+
| CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00') |
+-------------------------------------------------------+
| 2005-01-27 15:30:00 |
+-------------------------------------------------------+

CONVERT_TZ() assumes that the given datetime value has the time zone represented by the first hour/minute offset argument, and converts it to a value in the time zone represented by the second offset argument. The result is that you get the same datetime value, from the point of view of a different time zone.





General Questions related other questions

How can I detect that MySQL is already installed?

By the following ways, you can detect that MySQL is already installed or not.Firstly, If there is no firewall in between, you may check to see if port 3306 is responding as the MySQL Server uses port 3306 by ...

What is prepared statement in MySQL?

MySQL Server supports prepared statements, which are useful when you want to run several queries that differ only in very small details. For example, you can prepare a statement, and then execute it multiple ...

Maximum number of columns in a table

As of our understanding, The maximum number of columns for any storage engine depends upon the option \"AVG_ROW_LENGTH\". If your length of the column name is large, then number of columns will be less and obvi ...

Can I use FIELD CONSTRAINTS on MySQL?

MySQL doesn\'t support declarative CHECK CONSTRAINTs, you could use triggers as a workaround. However, you can do those checks with triggers. If a BEFORE trigger fails, the operation on the corresponding row is ...

What are reserved words in MySQL?

Complete reserved word list for MySQL 5:http://dev.mysql.com/doc/mysqld ...

How to manage Time Zone in MySQL?

MySQL Server allows to set the current time zone on a per-connection basis. Time zone settings are determined by the time_zone system variable. To view current Time Zone run the following query:SHOW ...

What is History of MySQL?

MySQL is a powerful and the most popular Open Source Software relational database management system (RDBMS) that uses SQL (Structured Query Language). MySQL is officially pronounced \"My esquel\", not \"My se ...

Why values with spaces do not come into search result?

To trim spaces from the value of its both sides, MySQL provides TRIM() function.  You can use TRIM to fix spaces  problem in your select queries.  Take an example:SELECT * FROM tbl_nam ...

How does MySQL manage case sensitivity of identifiers?

A property that affects how you use identifiers is whether they're case sensitive; some identifiers are case sensitive and others are not. You should understand which is which and use them accordingly. ...

What are MySQL connectors or client interfaces?

MySQL AB provides several application programming interfaces (APIs) for accessing the MySQL server. The interface included with distributions of MySQL itself is libmysqlclient, the C client library. This API ...