The Perils of MySQL Timestamps

Or, perhaps more accurately, learning about timezone oddities in MySQL the Hard Way


The decision was made and we were ready to switch our entire system to UTC. Operating in a timezone with daylight savings, and one which most of our users didn’t themselves operate in, made it a no-brainer. Making the switch on production was easy: simply modify the local timezone and restart the application stack. Aside from modifying the timing of some cron jobs, the only real headache was how to manage legacy data.

The system had been running in Mountain Time since June of 2008. We had accumulated a large amount of data with timestamps used for all sorts of mission-critical decisions and we naturally wanted those timestamps to be updated to UTC as part of the switch.

My first approach was to write a really simple groovy script (edited for proprietary content and formatted to fit in your browser window):


// the sql var points to the database sql connection info
def startTime = new Date()
sql.eachRow("show tables") { row->
  tables << row['Tables_in_database']
}
sql.eachRow("show columns from " + table) { field->
  if(field['Type']=='timestamp') {
    println "Found timestamp field of ${field['Field']} in ${table}"
    def updateStatement = "update " + table + 
    " set ${field['Field']} = " + 
    "(select date_add(${field['Field']}, INTERVAL 6 HOUR)) " +
    "where ${field['Field']} is not null"
    println "Updated ${sql.executeUpdate(updateStatement)} " +
    "records from ${table}"
    }
}

Integer totalSeconds = ((new Date()).time - startTime.time)/1000
println "\n\nComplete!  Total time: ${totalSeconds} second(s)"

Not bad. The script simply runs through each of the tables with a timestamp and adds 6 hours (the difference between MDT and UTC) to the timestamps. It’s not perfect (we had another time change to think about), but it was deemed good enough since timestamps with dates before the last “spring forward” change from MST to MDT were beyond the point where their hour of innacuraccy would have a measurable affect on the system (we’re all about pragmatism).

However, I kept getting a very odd error when running the script against test data. Data truncation: Incorrect datetime value “2009-03-08 02:08:00” After some research, the reason became obvious: there is no 2am hour on the 8th of March! That hour was skipped when daylight savings took affect. Makes sense, but I was dissappointed that MySQL didn’t just figure that out for me. No big deal … I modified the above script to take that into account. We were off.

Or so we thought.

In testing today, we found out that MySQL stores timestamps internally as UTC times and does the timezone extrapolation dynamically. This meant that I didn’t need to go through the above excercise at all … all we had to do was verify the MySQL server was running in the same timezone as the server itself (select @timezone;), update the server’s timezone, and restart mysqld!

Advertisements

About johnnywey

Welcome to A Regular Expression. This blog is designed to reflect my thoughts on life, music, software design, Apple, faith, philosophy, and whatever else I can think of.

Posted on August 19, 2009, in MySQL, Programming. Bookmark the permalink. 3 Comments.

  1. I’m not sure why, but every time I have to write code that touches time zones, it seems to take about 5x longer than it should. It really doesn’t seem like a difficult problem, but it often turns out to be.

  2. Agreed Tim. I’ve suffered this again lately, seems to bite me once every couple of months. My perfect solution to this problem:

    1) make the earth flat
    2) make the sun stationary
    3) a cloud covers the sun up at night time and moves out of the way during the day

    Cloud computing with timezones maybe…

  3. I totally have a project on Github for that!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: