Row selection in the last 5 minutes using unix timestamp

Question

I am trying to figure out what data was stored in db in 5 minutes.

SELECT COUNT(id), DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i') FROM `table` WHERE `timestamp` >= CURRENT_TIMESTAMP - INTERVAL 5 MINUTE 

timestamp is a unix timestamp (milliseconds since 1970).

not working, I get null num_rows instead of 0, and if I use

 if (!isset($resultHistory->num_rows) || !$resultHistory->num_rows) 

to perform actions, the code is not included in the loop.

I also tried

 SELECT COUNT(id), DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i') FROM `table` WHERE DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i') >= CURRENT_TIMESTAMP - INTERVAL 5 MINUTE 

Answer 1

Current_timestamp returns the current date as SQL TIMESTAMP, not a UNIX timestamp, so you will need to convert using the unix_timestamp function:

 SELECT COUNT(id), from_unixtime(`timestamp` / 1000, '%Y-%m-%d %H:%i') FROM `table` WHERE `timestamp` >= unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 5 MINUTE) * 1000 

EDIT:

Since your timestamp column contains unix time, you will also have to use from_unixtime to format the date.

EDIT2:

In MySQL, UNIX time is the number of seconds since epoch (1/1/70), so if your timestamp column contains the number of milliseconds, you also need to divide by 1000.

Answer 2

You could do this:

 SELECT COUNT(id), DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i') FROM `table` WHERE `timestamp` BETWEEN (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND NOW()