Why not use MySQL's TIMESTAMP across the board? - 【StackMirror】|php|mysql|timezone

If you need your web application to translate between timezones on a per-user basis, why not use TIMESTAMP across the board for all date/time fields? Consider that TIMESTAMP values are stored in UTC and converted to the timezone set for the connection when retrieved.

I have asked this question on IRC, read the MySQL documentation, searched Google extensively, and asked my co-workers, and I have yet to find a compelling reason to not use TIMESTAMP.

Note: I understand TIMESTAMP has a limited range of 1970 - 2038; that is not going to be an issue in my case. Also, I am using PHP with MySQL.

2009-06-16 18:28
by Chad Johnson

DATETIME is for arbitrary dates and times that you utilize in your data.

TIMESTAMP is for when you want the time to be automatically updated. If you want to know when the row was inserted/updated, use a TIMESTAMP.

Also, keep in mind that a TIMESTAMP is only stored in UTC -- it is converted to the local timezone of the server before it is transmitted back as part of a query.

In your case, you're better off using a DATETIME. If you're worried about UTC, that's fine -- just create your dates in UTC rather than using the local time. Use UTC_TIMESTAMP.

2009-06-16 18:37
by Randolpho

Thank you for the response, but I am lost as to why I am better off using DATETIME, considering I would have to manually do the conversions 100s of places throughout my codebase, wherever I use PHP date functions, when the database manager could do this work for me - Chad Johnson 2009-06-16 18:44

You have to do that date conversion anyway. TIMESTAMP will return converted to the local server time. That's not the user's timezone. You should store your dates in UTC and convert them to the user's timezone at the UI level - Randolpho 2009-06-16 19:10

What if I run "SET time_zone='the users timezone'" whenever a database connection is made within the user's session? Would that be a good solution, in your mind - Chad Johnson 2009-06-16 19:17

Yes, that would work. But I still suggest that you handle it in your UI layer - Randolpho 2009-06-16 19:25

Is your recommendation based solely on the fact that TIMESTAMP is only intended for tracking record creation and update dates/times? Are there any other compelling reasons - Chad Johnson 2009-06-16 19:54

It's a logical/layering thing. You don't want to make what is essentially a UI/display decision at your data layer level. I prefer to have the actual data in my hands at the business layer level. If you specify timezone at the data level, you lose having that information at the business layer level. Odds are it won't affect things that much in your case, but you never know when some day down the line that decision might cause you pain. Better to pass the full UTC date to your UI layer and have any displays of that date convert to the user's timezone - Randolpho 2009-06-16 20:21

I think your answer is here:

I understand TIMESTAMP has a limited range of 1970 - 2038; that is not going to be an issue in my case.

I would be careful making assumptions about the longevity of projects especially when it comes to database schemas. Databases have a tendency to remain in place and in use long after the applications that used them have gone away.

2009-06-16 18:31
by Andrew Hare

Surely MySQL would have released a patch for the 2038 issue--especially considering the vast majority of computers will be 64-bit by then - Chad Johnson 2009-06-16 18:39

Perhaps they will, but part of being a successful developer is to plan for the worst rather than hope for the best - Andrew Hare 2009-06-16 18:50

If figuring out how to alter a table in 25 years makes it onto your list of things worth spending your time on, you sir are in a far, far better shape than most of us - DougW 2012-10-05 00:19

Randalpho's answer is wrong on many facts!

Timestamps do not need to be automatically updated on creation OR updates.

Also, timestamps are translated to the CLIENT's local time, not the serever's.

Just look at the MySQL docs for datetime.

2012-01-27 03:47
by Chaz Gilbert

This is really a comment, not an answer. With a bit more rep, you will be able to post comments - Emil 2012-10-26 22:50