SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Deciding on date/time storage

    When I inherited the code base I'm working on, everything was using unix timestamps, and all the converting that goes along with that. I do quite a lot of direct database reading/manipulation, as well as scripting, so it became convenient for me to start using datetime or timestamp formats, as they're human readable.

    Now, I'm starting to doubt this decision. I read this useful-if-inconclusive sitepoint article on date formats: http://www.sitepoint.com/whats-the-best-date-format/

    I agree with a lot of the points. ie that a date time should be human readable (which a uts is not), and also that it should contain timezone information (which datetime does not).

    Now, I'm not that bothered about internationalisation, but time zones do become an issue with daylight saving time. Ie when the clocks go back, the datetime 2011-10-30 01:30 does not tell you if this was the 1:30 before the clocks when back, or after. So don't the datetime/timestamp types miss a vital piece of information?

    Am I missing something here? Or am I asking for too much out of a format?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the issue with the daylight savings time, as well as with timezone offset, is easily resolved by simply storing datetimes in GMT

    this means converting on the way in, if your users are in different timezones, and of course converting on the way out again, too

    how does storing unix epoch seconds as integers solve those problems? still gotta do conversion on the way in, and on the way out, right? i mean, in addition to converting to/from integer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    522
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the issue with the daylight savings time, as well as with timezone offset, is easily resolved by simply storing datetimes in GMT
    hmmm. You mean like this?
    Code:
    SELECT CONVERT_TZ(NOW(),@@global.time_zone,'GMT');
    Sure, I guess that would work. (not sure if I've used the correct system variable, though).

    Quote Originally Posted by r937 View Post
    how does storing unix epoch seconds as integers solve those problems?
    Sure, you've still got to convert, but at least it's possible to retrieve from epoch seconds, the correct datetime, taking into account daylight saving. In my above example 2011-10-30 01:30 could actually be one of two times (as in the UK the clocks will have gone back an hour at 2am), but 1319938200 is unambiguous.
    However, storing everything as GMT would, indeed, solve the problem, albeit it might require a slight change of mindset!
    Cheers


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •