MySQL CONVERT_TZ() help

Question

Greetings all (from a MySQL beginner)

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?

Thanks to all for the help.

--John

Answer 1

Q1: Yes, the CONVERT_TZ takes daylight savings time into account for you. This information and the time that DST starts/ends for each time zone is stored in the time_zone_* tables.

Q2: Yes, as stated in the mysql docs, the time zone information changes per the politics of each area. You'll have to update the time_zone_* tables every time a change occurs. Sucks to be IT sometimes, this is one of them.

Q3: These are the 5 timezone tables, query them to see if they have anything in them:

select * from time_zone_transition_type;
select * from time_zone_transition;
select * from time_zone_name;
select * from time_zone_leap_second;
select * from time_zone;