MySQL CONVERT_TZ()

convert-tz, mysql, timezone

Solution

If this yields null then the TZ tables have not been set up:

SELECT CONVERT_TZ(now(),'US/Eastern','US/Central');

If you do not have the time zone tables set up you could update the hour offset in the user table and then do:

select utc_timezone() - interval user_timezone_offset_in_hours hour
from userinfo a
where user_id = 999;

You'd still need a way to update the user's time zone however.

If you are writing this for a web application you can get the time zone via javascript, here's an article that describes how (haven't tried this but it looks like it'll work).

A bit of an explanation with respect to 'interval' above...

One of the more trick constructs in MySQL is the use of the `INTERVAL` keyword, best shown by example the (numeric value can be an expression or the field value)

select now() today, now() - interval 1 day yesterday;
+---------------------+---------------------+
| today               | yesterday           |
+---------------------+---------------------+
| 2011-05-26 13:20:55 | 2011-05-25 13:20:55 |
+---------------------+---------------------+

You can add them and subtract them anyway you like, this is why I never bother with the date/time add/subtract/convert functions

select now() a, now() - interval 1 day + interval 4 hour + interval 8 minute b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-05-26 13:24:16 | 2011-05-25 17:32:16 |
+---------------------+---------------------+

You can use negative numbers (should be good for negative time zone offsets) these are the same:

select now() - interval 1 month a, now() + interval -1 month b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2011-04-26 13:38:05 | 2011-04-26 13:38:05 |
+---------------------+---------------------+

Problem

I am trying to set up a database that stores daily alert times as specified by users. For example, the user wants to receive an alert if some criterion is met each day between 7:00 AM and 7:30 AM. In trying to implement this, I need to accommodate daylight saving time. Here's my attempted solution: - Store the users local time zone (in long form, e.g. "US/Eastern") information in one table (say userInfo), and the alarm times in another table (say userAlarms). - When querying the userAlarms table, convert UTC time into the users local time as specified by the tz column stored in the userInfo table via `CONVERT_TZ(UTC_TIME(), 'UTC', userInfo.tz)`. Question 1. From my understanding, specifying the time zone name (like US/Eastern) should take daylight saving time into account. For example, calling `CONVERT_TZ('00:00:00', 'UTC', 'US/EASTERN')` on January 1 should yield '19:00:00', but on July 1 the call should yield '20:00:00'. Am I correct? Question 2. If Q1 is correct, do I need to constantly update MySQL's time zone table to keep the time zone UTC offsets up to date? Question 3. The sample given in the MySQL documentation `SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')` yields "`NULL`" when run on my server. Could this be caused by not having the time zone tables set-up? How can I check this?

Original source