SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 42
  1. #1
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    datetime vs int(12)

    for storing dates in a mysql db, which is the recommended method and why?

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I think DateTime is the best to use. It is made for displaying and ordering time correctly, after all.

    Once it's retrieved from the database, you can simply use strtotime() to convert it back to an ordinary timestamp, which you can then use the date() function to turn it into a date/time, formatted to your choice.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I personally use INT for storing times
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  4. #4
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like to use INT(10) to store the unix timestamp that can be generated using time() All of the time comparrisons I do, I like to use the timestamp itself.

  5. #5
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wonshikee View Post
    for storing dates in a mysql db, which is the recommended method and why?
    DATE or DATETIME, because that's what they are for. This is the same reason why you wear your shoes on your feet and not on your head.

  6. #6
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stereofrog View Post
    DATE or DATETIME, because that's what they are for. This is the same reason why you wear your shoes on your feet and not on your head.
    Writing code in PHP, the output of time() is the easiest way to handle timestamps. While PHP and MySQL are great together for almost everything, the difference in how they handle timestamps is a big hurdle. I do my coding and diff'ing in PHP not on the MySQL end. This is my reason for doing the int(10), otherwise, you have to convert the DateTime into something that PHP can function with every time you pick a date and time from a database.

    This is why I love PHP/MySQL. There are limitless ways to do what ever it is that you want. The only "Best" way to do something is how you decide to once you know the ramifications of each way you are considering doing it.

    Nice analogy though...

  7. #7
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, of course, you have the full right to wear your shoes on your head, if you want

    Seriously, what exactly are the advantages of using time() and storing timestamps in DB (except that it is "easy")? Can you provide a real-life example?

  8. #8
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Best to use Date or Datetime. This data type is to store datetime related data so why u want to store into int(12).

    Thanks
    Ohid
    ------------------------------
    www.bdwebservice.com - Manual Directory Submission Service, Article Submission Service, Link Building Service.

  9. #9
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi ohid, welcome to the forums.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  10. #10
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could never see any benefit to using the DATETIME format in MySQL. I may have lacked a mentor to show me the way with that format.

    A few examples... As a little fun, I'd love to see your rebuttal using the DATETIME format.

    <background info>
    I've put timers on sites that display how long it has been since somthing occured or how long it will be until it occurs
    </background info>
    PHP Code:
    $now time();
    $RedSoxWonTheWorldSeries '1098932400';  //had to look it up... and convert it...

    //How long has it been since the Sox won the world series?
    $itsBeenAWhile $now $RedSoxWonTheWorldSeries;

    //We now have the number of seconds that it has been since the Sox won the World Series

    //And Minutes, hours, days, weeks...
    $minutes $itsBeenAWhile 60;
    $hours $minutes 60;
    $days $hours 24;
    $weeks $days 7;


    //   Of course the timestamp for when the Sox, or any item for that 
    //   matter could be stored in the DB.  I'm not retrieving it here because the 
    //   debate is not over how you'd retrieve it. 

    Next Example...

    <background info>
    Selecting all events that have happened between certain dates/times
    </background info>
    PHP Code:
    //  Use your favorite method of selecting a date and time 
    //  on a previous page and convert them to unix timestamps using your 
    //  favorite PHP method.

    //Store the result in $startTime and $endTime
    $startTime strtotime(''); //you'd convert your $_POST or $_GET data here
    $endTime strtotime('');  //and here

    //Compile query
    $query 'SELECT * FROM Table WHERE timestamp > '.$startTime.' AND timestamp < '.$endTime;

    //  The returned results will be only the times that happened 
    //  between the date and time you specify for the start time and the end time. 
    Last edited by tbakerisageek; Oct 8, 2007 at 08:25. Reason: shorten comment blocks so they don't scroll wide

  11. #11
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    Hi ohid, welcome to the forums.
    Gotta give us your input on the topic too...

  12. #12
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So I guess my question is a pretty valid one. I've always preferred to simply store the time() or UNIX_TIMESTAMP(), because I believed forcing the db to output time in a specific format is extra work for db that php could do, and also pulling it out and using a function like strtotime just to get a timestamp that i'm going to redump back into another function date() when it could be brought straight in from db seemed like killing 1 bird with 2 stones.

  13. #13
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stereofrog View Post
    Yes, of course, you have the full right to wear your shoes on your head, if you want

    Seriously, what exactly are the advantages of using time() and storing timestamps in DB (except that it is "easy")? Can you provide a real-life example?
    What is a real-life example that shows datetime is better to use?

    Memory wise, is datetime <= to an int?

  14. #14
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wonshikee View Post
    What is a real-life example that shows datetime is better to use?

    Memory wise, is datetime <= to an int?
    I'm waiting on what he comes back with too.

    BTW the OP was wrong, it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    while both are perfectly valid approaches, DATE and DATETIME have the distinct advantage of being usable with the various date and time functions in mysql. if you use unix timestamps (i.e., the INT you keep referring to) then you have to convert that data before you can use any of these functions.

    tbakerisageek- yes, you may have to convert the dates and times as you extract them from the database, bu tin general i find that i only ever need 2 conversions for entire applications that i can just copy-and-paste in to the relevant queries: one to convert DATE, and the other to convert DATETIME.

  16. #16
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Gotta give us your input on the topic too...
    I did, I was the first to answer.

    Memory wise, is datetime <= to an int?
    Actually, I think it is. Otherwise, there would be no need for it as a field type.

    I've noticed more and more recently that every feature of mysql is thought through thoroughly (to many "th"s!). There are alot of benefits to using the default ways.

    I feel a metapor coming on... If you were given legs to run with, why run on your hands?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  17. #17
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    I did, I was the first to answer.


    Actually, I think it is. Otherwise, there would be no need for it as a field type.

    I've noticed more and more recently that every feature of mysql is thought through thoroughly (to many "th"s!). There are alot of benefits to using the default ways.
    Sorry, must have missed the second post, or not realized it was by you by the time I read your post I commented on.

    It appears to me that one must select a location to process their dates. Either in PHP or in MySQL.

    I'm more comfortable using PHP to do any data processing as I tend just to use MySQL for a data-repository. Maybe it's that I'm not familiar enough with what MySQL has to offer for data processing features like you're reccomending or it's just that I learned how to do what I need in PHP first.

    @Arkinstall, Care to take stereofrog's place in the debate and post real-world code to the MySQL DATE/DATETIME format for us?

  18. #18
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wonshikee View Post
    Memory wise, is datetime <= to an int?
    • DATE is 3 bytes (supported range '1000-01-01' to '9999-12-31')
    • DATETIME is 8 bytes (supported range '1000-01-01 00:00:00' to '9999-12-31 23:59:59')
    • INT is 4 bytes (supported range when unsigned '1970-01-01 00:00:01' UTC to partway through the year 2038)

    Quote Originally Posted by tbakerisageek View Post
    BTW the OP was wrong, it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!
    sorry, you're wrong. the (12) and (10) only affect display formatting and is completely unrelated to storage requirements. an INT is only 4 bytes.

  19. #19
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    DATE is 3 bytes (supported range '1000-01-01' to '9999-12-31')
    DATETIME is 8 bytes (supported range '1000-01-01 00:00:00' to '9999-12-31 23:59:59')
    INT is 4 bytes (supported range when unsigned '1970-01-01 00:00:01' UTC to partway through the year 2038)

    sorry, you're wrong. the (12) and (10) only affect display formatting and is completely unrelated to storage requirements. an INT is only 4 bytes.
    I guess I'm not understanding what you mean by "the (12) and (10) only affect display formatting and is completely unrelated to storage requirements"

    I've never expanded my INT's to 12 from 10 and I've never had a problem storing a unix timestamp in the format of "1098932400" which == "October 27, 2004 at 10:00:00 PM" stored as 10 sequential characters (in timestamp format, which is what I store)

  20. #20
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    I feel a metapor coming on... If you were given legs to run with, why run on your hands?
    I'm sure there are plenty of examples of creators giving you features you can use that ended up being bad idea.

    Not that datetime is a bad features, but making the argument that just cuz they say to use it, you should use it is a fallacy.

    I could think of an easy counter argument - they offered you register_globals, so many developers used it - and it turned out to be a bad idea right?

  21. #21
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't mean to indicate that it is a bad option either. I was asked what I do, and why.

    I'm not familiar enough with the benefit of using a DATE or DATETIME format in MySQL so I choose not to use it as I have something else that works just as well for me.

    and Yes, Register globals is a bad idea!

  22. #22
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes - however I'm sure DateTime has no security problems

    Anyway, I cast you all back to a thread started 2 years (just 3 days out) ago, when a similar conversation was to be discussed (Longneck was there!):
    http://www.sitepoint.com/forums/showthread.php?t=306686

    (Yes, that's 20,1035 threads in two years! Go SITEPOINT!)
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  23. #23
    SitePoint Zealot
    Join Date
    Dec 2005
    Posts
    117
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Definitely DATE and DATETIME. Afterall, the mySQL (and Oracle, etc.) development team put those data types in for a reason, and it wasn't just to fool us.

    A good real-life example though is, say you have a table of transactions, and you want to get the sum of all transactions in July, 2007. With DATE / DATETIME, you'd just do:

    Code:
    mysql_query("SELECT sum(amount) FROM transaction WHERE MONTH(trans_date) = 7 AND YEAR(trans_date) = 2007");
    Simple as that. Bit tougher screwing around with timestamps though, not to mention the whole formatting issue.

    Just my two cents.
    Kiopa Software -- Demo Now Online! Check it out!
    Goal: Consolidate all data & tools you use on a daily basis.
    Grand opening special, licenses FREE for a limited time.

  24. #24
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tbakerisageek View Post
    I could never see any benefit to using the DATETIME format in MySQL. I may have lacked a mentor to show me the way with that format.
    The conclusive advantages are the ability to query date parts and to perform date arithmetics, e.g.
    Code:
    select * from users where year(birthdate) < 1989
    update orders set expiry_date = date_add(now(), interval 2 month)
    In fact, mysql date module is far more powerful than that of php (see http://dev.mysql.com/doc/refman/5.0/...unctions.html), so there's no reason to rely on the latter when working with mysql.

    I've put timers on sites that display how long it has been since somthing occured or how long it will be until it occurs
    Code:
    select datediff(date1, date2)
    Selecting all events that have happened between certain dates/times
    PHP Code:
    $db->query("SELECT ... where event_date between 
       str_to_date(?, '%m/%d/%Y') and
       str_to_date(?, '%m/%d/%Y')
    "
    $_GET['start_date'], $_GET['end_date']); 

  25. #25
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    Yes - however I'm sure DateTime has no security problems

    Anyway, I cast you all back to a thread started 2 years (just 3 days out) ago, when a similar conversation was to be discussed (Longneck was there!):
    http://www.sitepoint.com/forums/showthread.php?t=306686

    (Yes, that's 20,1035 threads in two years! Go SITEPOINT!)
    Thanks for that link, although the discussion is actually about the column type TIMESTAMP vs DATETIME - which isn't what this is about, there was a post in there that actually highlighted more differences. One being that timestamp has the int range that limits itself to 1970 to 2037 while datetime can be from year 0 to year 9999 and the difference in bytes

    So perhaps the conclusion is both are good as long as they fit their purpose - and it really comes down to what you're used to? I guess for my purpose, storing int always made sense - but for something like a calendar application, datetime has some good advantages to ease coding and logic.


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
  •