#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0

    Help with choosing timestamp or date/time


    Hello,

    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?

    Thanks.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,301
    Rep Power
    7170
    I know timestamp wouldn't work, because if your server changes time zones then that date would change.
    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.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    6
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo