Mysql DateTime vs Unix Timestamp

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'

or

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

date1 will be a primary key.
jwzkAsked:
Who is Participating?
 
Ray PaseurCommented:
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
0
 
igni7eCommented:
select * from data where date1 between '4524523' and '346245325235'

Make date1 an int ;)
0
 
igni7eCommented:
opps... int means single quotes are not needed:

select * from data where date1 between 4524523 and 346245325235
0
 
jwzkAuthor Commented:
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 :)
0
 
Ray PaseurCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.