SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    PHP and MySQL date: why not use INT(14)?

    I've been looking at the MySQL date/time fields and also the option of just storing a UNIX timestamp. What I've always done, however, is store as date and time as a 14 digit int (YYYMMDDHHMMSS). It's easy to order by and is human readable.

    From what I can tell the advantage of using a UNIX timestamp is that it's primed for use with PHP's date function and MySQL has the function to query themóbut really formatting YYYMMDDHHMMSS for whatever use you want is pretty easy. Am I missing something other than the int is smaller?

    I can see the advantage of using MySQL's datetime if you're going to be doing more complex date-based queries and need to make use of some of MySQL's date functions. But, aren't they stored as strings and therefore slower than ints?

    I'm not sure whether to carry on as I am or start to use either of the above. I quite like the UNIX timestamp idea as the raw date being readable in the database is not really an argument to keep it that way. And is it safe to say by 2038 UNIX timestamp will still be working?

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,389
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Hi DrQuincy,

    There's a good Stack Overflow discussion on datetime vs timestamp fields. One compelling argument for using a UNIX timestamp is that it represents an absolute moment in time, whereas a datetime is relative to the timezone the user is in.

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    I can see the advantage of using MySQL's datetime if you're going to be doing more complex date-based queries and need to make use of some of MySQL's date functions. But, aren't they stored as strings and therefore slower than ints?
    No they are not stored as strings, they are stored as binary numbers and since they use the full range of values they are smaller numbers than the ones you are using where you would not have 20139597999999. They are only converted to strings when you extract them from the database and after any date functions have been applied.

    Also your way does not provide an easy way to change the timezone so that all users can see the time relative to where they are.

    You are effectively using larger numbers and offloading a lot of the work that SQL can do more efficiently when you use an int instead of a datetime or timestamp for the field type.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Hi DrQuincy,

    There's a good Stack Overflow discussion on datetime vs timestamp fields. One compelling argument for using a UNIX timestamp is that it represents an absolute moment in time, whereas a datetime is relative to the timezone the user is in.
    Quote Originally Posted by felgall View Post
    No they are not stored as strings, they are stored as binary numbers and since they use the full range of values they are smaller numbers than the ones you are using where you would not have 20139597999999. They are only converted to strings when you extract them from the database and after any date functions have been applied.

    Also your way does not provide an easy way to change the timezone so that all users can see the time relative to where they are.

    You are effectively using larger numbers and offloading a lot of the work that SQL can do more efficiently when you use an int instead of a datetime or timestamp for the field type.
    Thanks to you both. felgall, does using a slightly larger int have that much of an impact on performance?

    I'm confused as to how this relative timezone thing works. Surely, if it's only the one server access it, it will always use local time zone, no?

    So basically, would you always use MySQL datetime for dates and times and SELECT with UNIX_TIMESTAMP—even if you don't need most of MySQL's date functions?

  5. #5
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,389
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    I'm confused as to how this relative timezone thing works. Surely, if it's only the one server access it, it will always use local time zone, no?
    I was thinking of scenarios where you get dates as input from users in different timezones, but thinking about it, you could could convert all input to a specific timezone (UTC perhaps) and then store it in a DATETIME field, so I don't suppose it makes that much difference. I guess it comes down to preference at the end of the day. I tend to use DATETIME for the simple reason that it's human readable.

  6. #6
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    426
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'm going to give it a go. when I get the chance I'll create a table with a load of records and see how if differs.

    Thanks.

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    Thanks to you both. felgall, does using a slightly larger int have that much of an impact on performance?
    It does as soon as you need to perform any calculations at all that involve the date - not because of the size of the number though but simply because you can't simply add or subtract a number to get another date and time a fixed distance from the first one.

    Only if you are simply treating the date as a string of numbers that never needs to be changed should you not start by converting it to a date format when you first validate it and only convert back when you need to display it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •