Unix TimeStamp SQL

PHP
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.

Thanks!

2 
SELECT [I]stuff[/I]
  FROM [I]tablez[/I]
 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

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);

var_dump($readable);

above code outputs:

string(19) “2012-01-19 09:51:39”