MySQL: daylight savings time support

Time zone support is a typical part of many web applications. But not every application takes into account daylight savings time when +1 is added to hour difference during summer time only.

When PHP allows to identify DST simply by calling something like:

print date('I', strtotime('2009-03-11 00:00:00'));

it may not be obvious that:

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'EST');

and

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'US/Eastern');

return different results:

  • ‘2009-03-11 05:00:00’ in case of ‘EST’ and
  • ‘2009-03-11 04:00:00’ in case of ‘US/Eastern’.

This article explains how to make work CONVERT_TZ() function (which returns NULL by default) and stay current with time zone changes.

P.S.: There are some time zones with minute adjustment like UTC+5:45 or UTC+9:30 as well as with difference more than 12 hours from GMT like UTC+13 or UTC+14.

No Comment

No comments yet

Leave a reply