[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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.
0
jwzk
Asked:
jwzk
  • 2
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now