Sunday, December 5, 2010

MySQL convert time zone from local to UTC

Had to convert a slew of times form local time to UTC time in an MySQL database on a Windows box.  I had pushed a large number of Windows event logs into a MySQL database with LogParser.

While the event log files have the time in UTC format I found that LogParser renders the time as system local time of the computer where it is running. The program doesn’t have any way to know the time zone of the computer where the event log files originated.  I should have originally used TO_UTCTIME in the LogParser queries.

So… on to using MySQL CONVERT_TZ.  Unfortunately, my Windows install of MySQL did not include time zone information needed by the CONVERT_TZ function. I had to download and install the Time zone description tables for the function to work correctly.

The CONVERT_TZ function works with POSIX defined time zone names To avoid the ambiguity of common Time zone abbreviations.  For example I used

UPDATE table_x SET TimeGenerated=CONVERT_TZ(TimeGenerated, ‘America/Chicago’,’UCT’);

To convert a US Central time to UCT/Zulu time.

UPDATE table_x SET TimeGenerated=CONVERT_TZ(TimeGenerated, ‘America/New_York’,’UCT’);

To convert a US Eastern time to UCT/Zulu time.

Now back to event correlation.