mysql 选择 a 和 b 之间的时间戳，返回全部或 0 个时间戳 [英] mysql select timestamps between a and b returning all or 0 timestamps

问题描述

试过了

select * from table where timestamp_field between 1330560000 and 1336170420

还有这个

select * from table where timestamp_field >=1330560000 and timestamp_field<=1336170420

都返回空结果集.

但是这个

select * from table where timestamp_field >= 1330560000

返回所有行

让事情变得更荒谬

select * from table where timestamp_field <= 1336170420

返回空结果集.

当然，在 1336170420=4.may 2012. 和 1330560000=1.march 2012 之前、之间和之后存在时间戳值.

Of course, there exists timestamp values before, between and after 1336170420=4.may 2012. and 1330560000=1.march 2012.

时间戳值没问题，至少 phpmyadmin 显示正确的(人类可读的)日期时间值.我通过解析字符串创建了时间戳，使用

Timestamp values are ok, at least phpmyadmin shows correct (human-readable) date-time values. I created timestamps by parsing strings, with

UPDATE table SET timestamp_field = STR_TO_DATE(timestamp_string, '%d.%m.%Y')

我猜我遗漏了什么，但找不到什么！?

Guess I'm missing something, but can't find what!?

推荐答案

MySQL 期望 date文字，不是整数:

MySQL expects date literals, not integer ones:

SELECT *
FROM   table
WHERE  DATE(timestamp_field) BETWEEN '2012-03-01' AND '2012-05-04'

要使用整数(假设它们是 UNIX 时代以来的秒数)，首先使用 MySQL 的 FROM_UNIXTIME() 函数:

To use integers (assuming that they are seconds since the UNIX epoch), first convert them using MySQL's FROM_UNIXTIME() function:

SELECT *
FROM   table
WHERE  timestamp_field BETWEEN FROM_UNIXTIME(1330560000)
                           AND FROM_UNIXTIME(1336170420)

或者使用 UNIX_TIMESTAMP() 将您的列转换为其 UNIX 表示形式:

Or else use UNIX_TIMESTAMP() to convert your column to its UNIX representation:

SELECT *
FROM   table
WHERE  UNIX_TIMESTAMP(timestamp_field) BETWEEN 1330560000 AND 1336170420

这篇关于mysql 选择 a 和 b 之间的时间戳，返回全部或 0 个时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！