Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / General Questions / Question No: 115

How to manage Time Zone in MySQL?

Answer No: 115

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.

Related MySQL FAQs to the Above FAQ

How-can-I-detect-that-MySQL-is-already-installed How can I detect that MySQL is already installed?

What-is-prepared-statement-in-MySQL What is prepared statement in MySQL?

Maximum-number-of-columns-in-a-table Maximum number of columns in a table

Can-I-use-FIELD-CONSTRAINTS-on-MySQL Can I use FIELD CONSTRAINTS on MySQL?

What-are-reserved-words-in-MySQL What are reserved words in MySQL?

What-is-History-of-MySQL What is History of MySQL?

Why-values-with-spaces-do-not-come-into-search-result Why values with spaces do not come into search result?

How-does-MySQL-manage-case-sensitivity-of-identifiers How does MySQL manage case sensitivity of identifiers?

What-are-MySQL-connectors-or-client-interfaces What are MySQL connectors or client interfaces?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.