mysql - 按日期选择似乎不起作用

Question

因为我不是世界上最伟大的 SQLer，所以我正在一步一步地做一些大事。

我有一张 table :

mysql> describe taps;
+---------------+-----------+------+-----+-------------------+-------+
| Field         | Type      | Null | Key | Default           | Extra |
+---------------+-----------+------+-----+-------------------+-------+
| tag_id        | int(11)   | YES  |     | NULL              |       |
| time_stamp    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| event_id      | int(11)   | YES  |     | NULL              |       |
| event_station | int(11)   | YES  |     | NULL              |       |
| device_id     | text      | YES  |     | NULL              |       |
| device_type   | text      | YES  |     | NULL              |       |
+---------------+-----------+------+-----+-------------------+-------+
6 rows in set (0.00 sec)

并希望选择给定日期(今天，2017 年 2 月 12 日)的所有条目。

我在努力

mysql> select * from taps WHERE (event_id=4) 
          AND ((time_stamp >= 1486857600000) AND (time_stamp <= 1486944000000));

空集，2 个警告(0.00 秒)

重要提示:我已经简化了事情，因为我想与变量进行比较，这些变量的值是我从另一个表中获得的，也是 timestamp 类型。

嗯，警告....

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '1486857600000' for column 'time_stamp' at row 1 |
| Warning | 1292 | Incorrect datetime value: '1486944000000' for column 'time_stamp' at row 1 |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

所以，我试着转换

select * from taps WHERE (event_id=4) 
   AND ((time_stamp >= CAST(1486857600000 AS DATETIME)) 
   AND (time_stamp <= CAST(1486944000000 AS DATETIME)));
Empty set (0.00 sec)

我不明白，因为我今天的表格确实有一些条目。

mysql> select * from taps order by time_stamp limit 3;
+--------+---------------------+----------+---------------+-----------+-------------+
| tag_id | time_stamp          | event_id | event_station | device_id | device_type |
+--------+---------------------+----------+---------------+-----------+-------------+
|     44 | 2017-02-12 15:10:25 |     NULL |            16 | NULL      | NULL        |
|     37 | 2017-02-12 15:10:27 |     NULL |            14 | NULL      | NULL        |
|     50 | 2017-02-12 15:10:28 |     NULL |            15 | NULL      | NULL        |
+--------+---------------------+----------+---------------+-----------+-------------+
3 rows in set (0.00 sec)

我做错了什么？我的查询应该是什么？

Answer 1

MySQL 在日期/时间方面有非常困惑的术语(参见 here)。 timestamp 本质上是带时区的日期时间，因为该值存储为 UTC，但在本地时区报告。

不要将此时间戳与 Unix 时间戳混淆，后者只是自 1970 年 1 月 1 日以来的秒数(或在某些情况下为毫秒)。

在你的情况下，试试这个:

select t.*
from taps t 
where event_id = 4 and
      time_stamp >= '2017-02-11 07:00:00' and
      time_stamp < '2017-02-12 07:00:00';

您可以使用from_unixtimestamp()。但是，人们通常会发现日期格式更易于阅读。

注意:我将最后一个条件改为严格不等式。这为您提供了 24 小时，没有重复的时间，以防正好在 2017-02-12 07:00:00 发生某些事情。