Mysql DateTime vs Unix Timestamp

Posted on 2010-01-05
Last Modified: 2012-05-08
Which one will be faster to compare? I will need to select all records between 2 given dates.

For example:

SELECT * FROM `data` WHERE `date1` BETWEEN '2009-01-01' AND '2009-31-01'


SELECT * FROM `data` WHERE `date1` >= '1234564789' AND `date1` <= '123456789'

date1 will be a primary key.
Question by:jwzk
    LVL 4

    Expert Comment

    select * from data where date1 between '4524523' and '346245325235'

    Make date1 an int ;)
    LVL 4

    Expert Comment

    opps... int means single quotes are not needed:

    select * from data where date1 between 4524523 and 346245325235
    LVL 107

    Accepted Solution

    I recommend that you use the MySQL DATETIME data type.  Here is why - you can do a million queries and never find much difference in speed between the two methods you've shown above.  But the first time you need to know what a date and time was - in human terms - you will be wasting your time trying to figure out how to convert a UNIX timestamp into a usable date / time.  If you use the DATETIME format, it is all right there in ISO8601 layout - easy to see, read, sort and understand.

    If you're really concerned about performance, do not do SELECT * and make sure the DATETIME strings used in your WHERE clause have indexes on them.

    Best regards, ~Ray

    Author Closing Comment

    Being human readable is exactly why I have been leery to use unix timestamps. If the queries are near the same speed it won't matter.Thanks for the advice :)
    LVL 107

    Expert Comment

    by:Ray Paseur
    Thanks for the points.  Even if the queries were much slower (and they will not be if the columns are indexed) I would still go with the readable version.  There is just not a lot of value-added in explaining to your clients why it is taking you so long to figure something out, so I like to have all the data in clear text and as easy to read as possible.

    When I get external dates that I need to put into MySQL queries, I create the db_date like this:

    $db_date = date('c', strtotime($external_date));

    Then you can use $db_date in the query directly.  Some editing to make sure the strtotime() did not return FALSE might be a good idea, too.

    Best regards, ~Ray

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now