MySQL 获取几天前的日期 n 作为时间戳

在 MySQL 中，如何获取30天前的时间戳？

比如:

select now() - 30

结果应该返回一个时间戳。

100176 次浏览
小开

You could use:

SELECT unix_timestamp(now()) - unix_timestamp(maketime(_,_,_));

For unix timestamps or:

SELECT addtime(now(),maketime(_,_,_));

For the standard MySQL date format.

小开
最佳答案

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
小开

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


提交答案