||02-04-2013 03:36 AM
Finding rows younger than certain date?
Just wondering which data type would be more efficient when querying to find all records in a table that are less than 31 days old? DATE, DATETIME, TIMESTAMP or INT (Unix time stamp)?
ie how should I store my data for the fastest retrieval on that query?
||02-04-2013 10:21 PM
DATE, DATETIME, TIMESTAMP should all be the same:
SELECT * FROM table WHERE datefield >= DATE_SUB( CURDATE(), INTERVAL 31 DAY )
If you use a UNIX timestamp (that is, an INT to hold a UNIX/Linux timestamp value) then you have to convert it:
SELECT * FROM table WHERE FROM_UNIXTIME(intfield) >= DATE_SUB( CURDATE(), INTERVAL 31 DAY )
though realistically you *can* use the INT and get the same performance by just inverting that:
SELECT * FROM table WHERE intfield >= UNIX_TIMESTAMP( DATE_SUB( CURDATE(), INTERVAL 31 DAY ) )
See it? By converting the DATE_SUB result to a unix time, you only have to do that conversion *ONCE* when the query is built and so the time for the WHERE clause is just as fast as when using a DATETIME, et al.
There are other cases, though, where you really would have to use FROM_UNIXTIME() on each INT field value, and so then you do pay a penalty.
|All times are GMT +1. The time now is 11:04 PM.
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.