使用Unix时间戳在最近5分钟内选择行 [英] selecting rows in the last 5 minutes using unix time stamp

问题描述

我想弄清楚哪些数据是在5分钟内保存到数据库的.

Iam trying to figure out whetere data was save to the db in the las 5 minutes.

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

timestamp是Unix时间戳(1970年的毫秒).

timestamp is a unix timestamp (millisecs from 1970).

不起作用，我得到的是null num_rows而不是0，如果我使用

doesn't work, I get nullnum_rows instead of 0 and if I use

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

要执行操作，代码不会进入循环.

to do actions, the code does not enter the loop..

我也尝试过

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

推荐答案

Current_timestamp以SQL TIMESTAMP(而不是UNIX时间戳)返回当前日期，因此您必须使用

Current_timestamp returns the current date as a SQL TIMESTAMP, not a UNIX timestamp, so you'd have 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

由于您的timestamp列包含Unix时间，因此您还必须使用

As your timestamp column contains unix time, you're also going to have to use from_unixtime to format the date.

在MySQL UNIX中，时间是从纪元(1/1/70)起的秒数，因此，如果您的timestamp列包含毫秒数，则也必须除以1000.

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'll have to divide by 1000 as well.

这篇关于使用Unix时间戳在最近5分钟内选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！