SitePoint Sponsor

User Tag List

View Poll Results: MySQL or Unix Timestamp

Voters
24. You may not vote on this poll
  • MySQL

    17 70.83%
  • Unix

    7 29.17%
Results 1 to 20 of 20
  1. #1
    SitePoint Addict
    Join Date
    Aug 2002
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL or Unix Timestamp?

    In creating your applications, do you store your date fields using MySQL Timestamp field or as Unix timestamp (as an INT field type) ?

  2. #2
    SitePoint Zealot Mau's Avatar
    Join Date
    Jan 2006
    Location
    California, USA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I store as MySQL but switch to and from unix automatically in my ActiveRecord implementation.

  3. #3
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use DATETIME only, and treat the timestamp as a string in application code. As for using the MySql unix_timestamp function, there are a number of issues in regards to internationalisation, and localisation (Google as I don't have the links on me).

    I used to use unix_timestamp myself, but not anymore, so beware of the big bad wolf

    Merry Xmas to you all, best of luck in 2007.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    DATETIME

    and happy birthday, dr livingston

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DATETIME is most scalable. The UNIX timestamp has well known limitations.

  6. #6
    SitePoint Guru
    Join Date
    May 2005
    Location
    Finland
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by d11wtq View Post
    DATETIME is most scalable. The UNIX timestamp has well known limitations.
    Those limitations being what exactly? What do you mean by "scalability" in this context?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    limitations such as if you want to use it for birthdays, you cannot have anyone who was born before 1970

    and you cannot use a date after 2038, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Aug 2002
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but if we manipulate date, we still convert them to unix stamp format right? the only benefit I see for storing as MySQL timestamp is that it's easier to read than unix timestamp, aside from that is there anything else?

  9. #9
    SitePoint Enthusiast Silverhawk's Avatar
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    limitations such as if you want to use it for birthdays, you cannot have anyone who was born before 1970

    and you cannot use a date after 2038, either
    actually, it supports from 1901 till 2038, so you're pretty safe using it. Unless of course you're on windows, the minimum limit is then 1970.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kabatak View Post
    but if we manipulate date, we still convert them to unix stamp format right?
    no, a DATETIME datatype requires no conversion

    Quote Originally Posted by Silverhawk View Post
    actually, it supports from 1901 till 2038,
    according to the manual, 1970 through 2038
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast Silverhawk's Avatar
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    according to the manual, 1970 through 2038
    When the person said UNIX timestamp, I'm pretty sure he was referring to the unix timestamp not mysql's date/time functions. You can store the unix timestamp in an INT field and manipulate it via PHP if you need to.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    and so was i

    and what is the lowest unix timestamp value that you can store in an integer column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast Silverhawk's Avatar
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and so was i

    and what is the lowest unix timestamp value that you can store in an integer column?
    -2147483648

    Unix timestamps starts at 1970, which means 0 is 1970. Since you can have negative values, you can go backwards too

    try it
    Code:
    <?php
    echo date('Y', -2147483648);
    ?>
    Wont work on windows if your PHP version is < 5.1

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    hey, how about that

    unfortunately, if you store a negative integer, mysql can't figure it out, which to me pretty much negates why you'd be using a database system...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ezku View Post
    Those limitations being what exactly? What do you mean by "scalability" in this context?
    1970 to 2038* = Limitation

    * Note that the 2038 limit is likely not going to be an issue since by 2038 we'd expect more people to be using 64 bit and higher computers. Of course, it still affects apps which currently need to look at dates in the future.

    I wasn't aware the UNIX timestamp could be negated.... pretty neat.

    By scalability I was referring to how far you could extrapolate date ranges with either of the formats. Because DATETIME is a structured format rather than just an incremental value it's not limited in what date ranges your application (say for example a planner) can handle.

  16. #16
    SitePoint Enthusiast Silverhawk's Avatar
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    hey, how about that

    unfortunately, if you store a negative integer, mysql can't figure it out, which to me pretty much negates why you'd be using a database system...
    That is true, but MySQL isn't the only database. Some people use the timestamp for portability reasons. Sometimes timestamps are easier to manipulate too depending on the problem.

  17. #17
    Resident Code Monkey Chris Corbyn's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    713
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Silverhawk View Post
    That is true, but MySQL isn't the only database. Some people use the timestamp for portability reasons. Sometimes timestamps are easier to manipulate too depending on the problem.
    I actually like to use an integer representation of ISO-8601. YYYYMMDD for dates alone (e.g. Christmas = 20061225) or YYYYMMDDHHmmss for times (e.g. 5PM on Christmas 20061225170000)

  18. #18
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My web application runs on Lynix - not Windows. So I don't have the Windows restriction of not being able to convert a negative timestamp value to text. Also, I think MySQL 5.x supports 64 bit timestamp values that go past 2038 - just like the Lynix systems that they run on.

    My application supports users in multiple timezones. My PHP code always uses timestamp values. It also uses timestamp values to write and read MySQL DateTime fields. I use DateTime fields because it makes it easier to answer support questions if I can read the date/time values. Of course, the DateTime values are in the database server's time zone. Since a timestamp value is time zone neutral I don't have to worry about the database server's time zone.

    My PHP code encodes and decodes user interface date/time values based upon the user's time zone. I find these techniques work very well for my web application.

  19. #19
    SitePoint Wizard dreamscape's Avatar
    Join Date
    Aug 2005
    Posts
    1,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Silverhawk View Post
    Some people use the timestamp for portability reasons.
    Actually for portability, I find the standard ISO format to be the most portable.
    <.smarter.web.development.>
    PHP Stuff: Plexus | Chocolate (BDD Framework... coming soon)
    Graphite

  20. #20
    SitePoint Addict
    Join Date
    Aug 2002
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, a DATETIME datatype requires no conversion
    that is true if we manipulate using SQL commands in DB side, but in PHP side, we still convert to unix right?


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
  •