Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
02-04-2013, 02:36 AM #1
- Join Date
- May 2012
- Thanked 0 Times in 0 Posts
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, 09:21 PM #2
DATE, DATETIME, TIMESTAMP should all be the same:
SELECT * FROM table WHERE datefield >= DATE_SUB( CURDATE(), INTERVAL 31 DAY )
SELECT * FROM table WHERE FROM_UNIXTIME(intfield) >= DATE_SUB( CURDATE(), INTERVAL 31 DAY )
SELECT * FROM table WHERE intfield >= UNIX_TIMESTAMP( DATE_SUB( CURDATE(), INTERVAL 31 DAY ) )
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.An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post: