Category Archives: MySQL

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!