MySQL CONVERT_TZ()

Question

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:

1. 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).
2. 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?

Answer 1

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 |
+---------------------+---------------------+