shaydez — 2012-01-19T17:03:40-05:00 — #1
So i recently converted my database to use strictly UNIX TIMESTAMP when recording traffic or anything in the database
so i get this 1326988299 I know how to convert it back to readable date through PHP or even through SQL; however i'm not sure how to search by date. For example if i wanted to run a report for everything on Jan 15 2012 (1-15-2012) i'm kind of stuck on that.
Can anyone help me out.
r937 — 2012-01-19T17:12:26-05:00 — #2
WHERE [I]somecolumn[/I] >= UNIX_TIMESTAMP('2012-01-15')
AND [I]somecolumn[/I] < UNIX_TIMESTAMP('2012-01-16')
this not only reads well, but it just happens to be the most efficient SQL possible, assuming your table has an index on somecolumn which i assume is integer
let me know if the january 15/16 boundaries for the range test didn't make sense
jv1 — 2012-01-19T18:15:18-05:00 — #3
You can convert a timestamp back to a readable date by using PHP's date function:
$timestamp = 1326988299;
$readable = date("Y-m-d H:i:s", $timestamp);
above code outputs:
string(19) "2012-01-19 09:51:39"