MySQL MySQL选择昨天的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7146828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL selecting yesterday's date
提问by PinoyStackOverflower
How can I display and count the values whose dates are yesterday?
I used time()to insert date in the database. Example:
如何显示和计算日期为昨天的值？我曾经time()在数据库中插入日期。例子：
URL: google.com youtube.com google.com youtube.com test.com youtube.com
DateVisited: 1313668492 1313668540 1313668571 13154314
I want do display how many URLs that have multiple existed in the table and also how many of that URL have been visited yesterday. Example result:
我想显示有多少个 URL 存在于表中，以及昨天访问了多少该 URL。结果示例：
LINK | timesExisted | timesVisitedYesterday
Google.com | 2 | 2
youtube.com| 3 | 3
I already have the idea on getting yesterday's date, but I don't have an idea on counting how many times a URL has existed for yesterday and counting how many times a URL has existed in the table.
我已经有了获取昨天日期的想法，但我不知道如何计算昨天某个 URL 存在的次数以及计算表中某个 URL 存在的次数。
回答by Bohemian
The simplest and best way to get yesterday's date is:
获取昨天日期的最简单和最好的方法是：
subdate(current_date, 1)
Your query would be:
您的查询将是：
SELECT
url as LINK,
count(*) as timesExisted,
sum(DateVisited between UNIX_TIMESTAMP(subdate(current_date, 1)) and
UNIX_TIMESTAMP(current_date)) as timesVisitedYesterday
FROM mytable
GROUP BY 1
For the curious, the reason that sum(condition)gives you the countof rows that satisfy the condition, which would otherwise require a cumbersome and wordy casestatement, is that in mysql boolean values are 1for true and 0for false, so summing a condition effectively counts how many times it's true. Using this pattern can neaten up your SQL code.
出于好奇，sum(condition)为您提供满足条件的行数的原因case是，在 mysql 中布尔值1用于 true 和0false，因此对条件求和有效地计算了多少次这是真的。使用此模式可以整理您的 SQL 代码。
回答by Romancha KC
SELECT SUBDATE(NOW(),1);
where now() function returs current date and time of system in Timestamp...
其中 now() 函数在 Timestamp 中返回系统的当前日期和时间...
you can use:
您可以使用：
SELECT SUBDATE(CURDATE(),1)
回答by simhumileco
You can use:
您可以使用：
SELECT SUBDATE(NOW(), 1);
or
或者
SELECT SUBDATE(NOW(), INTERVAL 1 DAY);
or
或者
SELECT NOW() - INTERVAL 1 DAY;
or
或者
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
回答by cdhowie
You can get yesterday's date by using the expression CAST(NOW() - INTERVAL 1 DAY AS DATE). So something like this might work:
您可以使用表达式获取昨天的日期CAST(NOW() - INTERVAL 1 DAY AS DATE)。所以这样的事情可能会奏效：
SELECT * FROM your_table
WHERE DateVisited >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
AND DateVisited <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));
回答by NCrash
Query for the last weeks:
查询最近几周：
SELECT *
FROM dual
WHERE search_date BETWEEN SUBDATE(CURDATE(), 7) AND CURDATE()
回答by Atequer Rahman
Last or next date, week, month & year calculation. It might be helpful for anyone.
上一个或下一个日期、周、月和年计算。它可能对任何人都有帮助。
Current Date:
当前的日期：
select curdate();
Yesterday:
昨天：
select subdate(curdate(), 1)
Tomorrow:
明天：
select adddate(curdate(), 1)
Last 1 week:
过去 1 周：
select between subdate(curdate(), 7) and subdate(curdate(), 1)
Next 1 week:
未来 1 周：
between adddate(curdate(), 7) and adddate(curdate(), 1)
Last 1 month:
最近 1 个月：
between subdate(curdate(), 30) and subdate(curdate(), 1)
Next 1 month:
未来 1 个月：
between adddate(curdate(), 30) and adddate(curdate(), 1)
Current month:
这个月：
subdate(curdate(),day(curdate())-1) and last_day(curdate());
Last 1 year:
过去 1 年：
between subdate(curdate(), 365) and subdate(curdate(), 1)
Next 1 year:
未来 1 年：
between adddate(curdate(), 365) and adddate(curdate(), 1)
回答by trclark81
While the chosen answer is correct and more concise, I'd argue for the structure noted in other answers:
虽然选择的答案是正确且更简洁的，但我认为其他答案中提到的结构是：
SELECT * FROM your_table
WHERE UNIX_TIMESTAMP(DateVisited) >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
AND UNIX_TIMESTAMP(DateVisited) <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));
If you just need a bare date without timestamp you could also write it as the following:
如果您只需要一个没有时间戳的裸日期，您也可以将其编写如下：
SELECT * FROM your_table
WHERE DateVisited >= CAST(NOW() - INTERVAL 1 DAY AS DATE)
AND DateVisited <= CAST(NOW() AS DATE);
The reason for using CASTversus SUBDATEis CASTis ANSI SQL syntax. SUBDATEis a MySQL specific implementation of the date arithmetic component of CAST. Getting into the habit of using ANSI syntax can reduce headaches should you ever have to migrate to a different database. It's also good to be in the habit as a professional practice as you'll almost certainly work with other DBMS' in the future.
之所以使用CAST与SUBDATEISCAST是ANSI SQL语法。 SUBDATE是日期算术组件的 MySQL 特定实现CAST。如果您不得不迁移到不同的数据库，养成使用 ANSI 语法的习惯可以减少麻烦。将这种习惯作为一种专业实践也很好，因为您将来几乎肯定会与其他 DBMS 一起工作。
None of the major DBMS systems are fully ANSI compliant, but most of them implement the broad set of ANSI syntax whereas nearly none of them outside of MySQL and its descendants (MariaDB, Percona, etc) will implement MySQL-specific syntax.
没有一个主要的 DBMS 系统完全符合 ANSI，但它们中的大多数都实现了广泛的 ANSI 语法，而 MySQL 及其后代（MariaDB、Percona 等）之外的几乎没有一个系统会实现特定于 MySQL 的语法。
回答by John-Paul Stanford
I adapted one of the above answers from cdhowie as I could not get it to work. This seems to work for me. I suspect it's also possible to do this with the UNIX_TIMESTAMP function been used.
我改编了 cdhowie 的上述答案之一，因为我无法让它工作。这似乎对我有用。我怀疑使用 UNIX_TIMESTAMP 函数也可以做到这一点。
SELECT * FROM your_table
WHERE UNIX_TIMESTAMP(DateVisited) >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
AND UNIX_TIMESTAMP(DateVisited) <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));
