昨天的所有MySQL记录

Question

如何高效地获取所有日期时间列值在昨天 00:00:00 到昨天 23:59:59 之间的记录？

SQL：

CREATE TABLE `mytable` (
  `id` BIGINT,
  `created_at` DATETIME
);

INSERT INTO `mytable` (`id`, `created_at`) VALUES
  (1, '2016-01-18 14:28:59'),
  (2, '2016-01-19 20:03:00'),
  (3, '2016-01-19 11:12:05'),
  (4, '2016-01-20 03:04:01');

如果在2016年1月20日的任何时间运行此查询，则我希望返回的只有第2行和第3行。

- eComEvo

Answer 1

由于您只需要日期部分，您可以使用MySQL的DATE()函数轻松比较。

SELECT * FROM table WHERE DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY);

请注意，如果您有大量记录，则此方法效率可能不高；使用DATE()的派生值会使索引优势丧失。在这种情况下，您可以使用以下查询：

SELECT * FROM table
    WHERE created_at BETWEEN CURDATE() - INTERVAL 1 DAY
        AND CURDATE() - INTERVAL 1 SECOND;

这是因为像CURDATE()返回的日期值被假设为时间戳为00:00:00。索引仍然可以使用，因为日期列的值根本没有被转换。

- miken32

---

奇怪的是，它只返回今天而不是昨天的记录。 - eComEvo

执行 SELECT @@global.time_zone, @@session.time_zone; 命令，两个值都返回 SYSTEM。当我在数据库系统上检查时区时，它是 UTC，而我的时区是 EST。最终查询将仅从远程系统上的脚本运行，所以我想这应该不是问题。 - eComEvo

这可以解释。对于我们北美人来说，协调世界时目前是明天凌晨1点。 - miken32

尝试更改为此以考虑时区，但现在即使在远程系统上直接执行它，它也会获取今天的记录。有什么想法吗？DATE(CONVERT_TZ(created_at, '+00:00', '-05:00')) = DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00') - INTERVAL 1 DAY) - eComEvo

如果您在远程系统上，则无需更改时区。只需使用答案中的查询即可。或者，如果您不相信我，请尝试 SELECT DATE(NOW() - INTERVAL 1 DAY) ;) - miken32

尝试过那个方法，但没有成功。然而，这个方法可以：DATE(created_at) = DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00') - INTERVAL 1 DAY) - eComEvo

Answer 2

如果您说

，您仍然可以使用索引。

SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
  AND CREATED_AT < CURDATE();

- Alan Hadsell

Answer 3

您可以使用 subdate 表示“昨天”，并使用 date() 表示您想要记录只匹配列的日期部分的情况。因此：

SELECT *
FROM tablename
WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)

- Anthony

Answer 4

这里 是一个有答案的同样问题的链接。总结一下答案，像Sajmon建议的那样使用 subdate() 函数。

subdate(currentDate, 1)

使用您的表格，应该是这样的。

select *
from tablename
where created_at between subdate(CURDATE(), 1)
and date (now() )

- Wilnar Vincent

Answer 5

使用方法: subdate(current_date, 1)

它非常适合你的情况！

- pivanchy

---

这个查询没有返回任何结果：select * from my_table where created_at = subdate(current_date, 1); 你的意思是想如何使用它在查询中？ - eComEvo

select * from my_table where created_at > subdate(current_date, 1); - pivanchy

选择 * 从我的表中，其中创建时间在subdate(current_date, 1)和current_date之间; 这个查询将获取从昨天的00:00:00到今天的00:00:00（即24小时）的所有数据，因为前一个查询将返回从昨天到当前时间的所有数据。 - pivanchy

Answer 6

您可以使用此代码，只需输入tablename和columnName（其中包含2021/01/09或2022-01-11 14:56:07等）

select * from (TABLENAME) where DATE(columnNAME) = TODAY - 1;

- Prathviraj Singh Chouhan

Answer 7

选择子日期(current_date(), 1)

选择*从表 WHERE created_at >= 子日期(current_date(), 1)

- Venkatesh Chakilam

---

你好Venkatesh，欢迎来到StackOverflow！请使用代码片段来展示你的SQL命令，并附上一些解释。这样做可以使它更易读和易懂 ;) - Timo Reymann

虽然这段代码可能解决了问题，但是包括解释它如何以及为什么解决了问题将有助于提高您的帖子质量，并可能导致更多的赞。请记住，您正在回答未来读者的问题，而不仅仅是现在提问的人。请[编辑]您的答案以添加解释并指出适用的限制和假设。 - Kevin M. Mansour

Answer 8

为了确保无论何时运行查询都能获取到昨天的数据，我使用了特定日期上的时间戳：

select timestamp(DATE_SUB(CURDATE(), INTERVAL 1 DAY)), timestamp(CURDATE());

所以查询会是这样的：

select * from (TABLENAME) where  created_at between timestamp(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) and timestamp(CURDATE())

- louigi600