MySQL get the date n days ago as a timestamp
Solution 1:
DATE_SUB will do part of it depending on what you want
mysql> SELECT DATE_SUB(NOW(), INTERVAL 30 day);
2009-06-07 21:55:09
mysql> SELECT TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day));
2009-06-07 21:55:09
mysql> SELECT UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day));
1244433347
Solution 2:
You could use:
SELECT unix_timestamp(now()) - unix_timestamp(maketime(_,_,_));
For unix timestamps or:
SELECT addtime(now(),maketime(_,_,_));
For the standard MySQL date format.
Solution 3:
If you need negative hours from timestamp
mysql>SELECT now( ) , FROM_UNIXTIME( 1364814799 ) , HOUR( TIMEDIFF( now( ) , FROM_UNIXTIME( 1364814799 ) ) ) , TIMESTAMPDIFF( HOUR , now( ) , FROM_UNIXTIME( 1364814799 ) )
2013-06-19 22:44:15 2013-04-01 14:13:19 1904 -1904
this
TIMESTAMPDIFF( HOUR , now( ) , FROM_UNIXTIME( 1364814799 ) )
will return negative and positive values, if you need to use x>this_timestamp
but this
HOUR( TIMEDIFF( now() , FROM_UNIXTIME( 1364814799 ) ) )
will return only positive, hours