November 10th, 2012, 11:01 PM
Help with choosing timestamp or date/time
Say there is a movie that is released on 12/15/12 at midnight.
What is the best data type to use to store that date?
I know timestamp wouldn't work, because if your server changes time zones then that date would change.
Would date/time be fine?
November 10th, 2012, 11:45 PM
Actually the whole point of timestamps is that they are independent of time zones. The value is always presented in the server's timezone, but the value always represents exactly the same point in time, it doesn't change if your server changes timezones (unlike a date/time, which would change the point in time it represents if you made the assumption that it was stored in the server's timezone).
For movie release dates I doubt the time actually has much meaning. It seems like it would make most sense to just go with a date.
For simplicity, I highly recommend that you always store dates and times in UTC, always have your server's timezone set to UTC, and always perform calculations in UTC in your application. Only convert to the user's timezone at the very last moment for display purposes.
November 11th, 2012, 12:20 PM
What about a historical date in the past? Say that an event happened on 10/20/1999.
Timestamp wouldn't work for that because if the DB/server settings change, then that date could possibly change, too. Is that right?
Using Date or Date/Time would make it always stay the same no matter what?
Also, when you hardcode the setting of a Date/Time value, then that will always stay the same, right? The only thing to take into account is when you set it to NOW(), which will set it according to the timezone it's in?
Thanks for your help.