Questions And Answers

More Tutorials

MYSQL Handling Time Zones

Retrieve the current date and time in a particular time zone

This fetches the value of NOW() in local time, in India Standard Time, and then again in UTC.

SET time_zone='Asia/Kolkata';
SET time_zone='UTC';

Convert a stored `DATE` or `DATETIME` value to another time zone

If you have a stored DATE or DATETIME (in a column somewhere) it was stored with respect to some time zone, but in MySQL the time zone is not stored with the value. So, if you want to convert it to another time zone, you can, but you must know the original time zone. Using CONVERT_TZ() does the conversion. This example shows rows sold in California in local time.

SELECT CONVERT_TZ(date_sold,'UTC','America/Los_Angeles') date_sold_local
 FROM sales
WHERE state_sold = 'CA'

Retrieve stored `TIMESTAMP` values in a particular time zone

This is really easy. All TIMESTAMP values are stored in universal time, and always converted to the present time_zone setting whenever they are rendered.

SET SESSION time_zone='America/Los_Angeles';
SELECT timestamp_sold
 FROM sales
WHERE state_sold = 'CA'

Why is this? TIMESTAMP values are based on the venerable UNIX time_t data type. Those UNIX timestamps are stored as a number of seconds since 1970-01-01 00:00:00 UTC.

Notice TIMESTAMP values are stored in universal time. DATE and DATETIME values are stored in whatever local time was in effect when they were stored.


In this page (written and validated by ) you learned about MYSQL Handling Time Zones . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Regular Expressions.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.