MySQL将n天前的日期作为时间戳

In MySQL, how would I get a timestamp from, say 30 days ago?

在MySQL中，如何获得时间戳，比如30天前?

Something like:

喜欢的东西:

select now() - 30

The result should return a timestamp.

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

3 个解决方案

#1

143

DATE_SUB will do part of it depending on what you want

DATE_SUB会根据需要做一部分

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

#2

You could use:

您可以使用:

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

For unix timestamps or:

对于unix时间戳或:

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

For the standard MySQL date format.

对于标准的MySQL日期格式。

#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

如果需要使用x>this_timestamp，会返回负值和正值吗

but this

但这

HOUR( TIMEDIFF( now() , FROM_UNIXTIME( 1364814799 ) ) )

will return only positive, hours

会不会只返回正的，小时

#1

143