The more I think about passwords and data types, the more I started thinking about changing the various data types for the various database fields. My mind thinks all the time, it’s homicidal.
I’m not a database programmer by any means, but I do regret storing date/times as TIMESTAMP. I wish I would’ve used a simple INT populated with Unix time. For one reason only – timezones.
As an example, 2011-08-30 19:20:20 tells me nothing about the timezone. Why is this important? Pretend there’s a worldwide, online discussion starting at 7:20 pm and you’re invited!
…
MySQL stores TIMESTAMP fields based on a timezone setting that you can set and update via MySQL and PHP, etc. Yuck. Why the yuck? Because if I dump the database from a server in PST and import it to a server in CST, the times will be wrong. Yuck.
Maybe there’s a slick workaround for this that I don’t know. Honestly, I just want the time stamp to be reliable. Any programmer looking at 1314705293 will probably guess what it is and know how to work with it.
And since I can’t leave well enough alone, SELECT UNIX_TIMESTAMP(created_on) AS created_on_unix ... UPDATE created_on = created_on_unix has become my new best friend.
Brandon Godwin · Aug 30, 2011 at 10:51 pm
Since MySQL 5 I believe, the TIMESTAMP data type is converted to UTC when it's stored and converted back into the timezone of the server or connection set timezone when retrieved. Storing the date and time as a TIMESTAMP allows you to perform date operations in the query, such as adding or subtracting time from it for a WHERE clause.
However, make sure you use TIMESTAMP and not DATETIME for this. DATETIME does not handle the timezone conversion.
Reply
Post
Thanks for the tip! I didn't know that.
Reply
Post