选择今天、本周、本月的记录 php mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5293189/
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
Select records from today, this week, this month php mysql
提问by Andrew Samuelsen
I imagine this is pretty simple, but can't figure it out. I'm trying to make a few pages - one which will contain results selected from my mysql db's table for today, this week, and this month. The dates are entered when the record is created with date('Y-m-d H:i:s');. Here's what I have so far:
我想这很简单，但无法弄清楚。我正在尝试制作几页 - 其中包含从我的 mysql db 表中选择的今天、本周和本月的结果。日期是在创建记录时输入的date('Y-m-d H:i:s');。这是我到目前为止所拥有的：
day where date>(date-(60*60*24))
日期>(日期-(60*60*24))
"SELECT * FROM jokes WHERE date>(date-(60*60*24)) ORDER BY score DESC"
week where date>(date-(60*60*24*7))
日期>(日期-(60*60*24*7))的星期
"SELECT * FROM jokes WHERE date>(date-(60*60*24*7)) ORDER BY score DESC"
month (30 days) where date>(date-(60*60*24*30))
月 (30 天) 其中日期>(日期-(60*60*24*30))
"SELECT * FROM jokes WHERE date>(date-(60*60*24*30)) ORDER BY score DESC"
Any ideas would be much appreciated. Thanks!
任何想法将不胜感激。谢谢！
回答by Nathan Ostgard
Assuming your date column is an actual MySQL date column:
假设您的日期列是实际的 MySQL 日期列：
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
回答by mo.
Try using date and time functions (MONTH(), YEAR(), DAY(), MySQL Manual)
尝试使用日期和时间函数（MONTH()、YEAR()、DAY()、MySQL 手册）
This week:
本星期：
SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());
Last week:
上个星期：
SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;
回答by Lukas Brzak
Current month:
这个月：
SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date)=MONTH(NOW());
Current week:
当前周：
SELECT * FROM jokes WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW());
Current day:
当前日期：
SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND DAY(date) = DAY(NOW());
This will select only current month, really week and really only today :-)
这将只选择当月，真的是一周，真的只有今天:-)
回答by David van Geest
Nathan's answer will give you jokes from last 24, 168, and 744 hours, NOT the jokes from today, this week, this month. If that's what you want, great, but I think you might be looking for something different.
Nathan 的回答会给你过去 24、168 和 744 小时的笑话，而不是今天、本周、本月的笑话。如果这就是您想要的，那太好了，但我认为您可能正在寻找不同的东西。
Using his code, at noon, you will get the jokes beginning yesterday at noon, and ending today at noon. If you really want today's jokes, try the following:
使用他的代码，在中午，您将收到昨天中午开始到今天中午结束的笑话。如果你真的想要今天的笑话，试试以下方法：
SELECT * FROM jokes WHERE date >= CURRENT_DATE() ORDER BY score DESC;
You would have to do something a little different from current week, month, etc., but you get the idea.
您将不得不做一些与当前周、月等略有不同的事情，但您明白了。
回答by KarlosFontana
Everybody seems to refer to date being a column in the table.
I dont think this is good practice. The word date might just be a keyword in some coding language (maybe Oracle) so please change the columnname date to maybe JDate.
So will the following work better:
每个人似乎都将日期称为表中的一列。
我不认为这是好的做法。日期一词可能只是某种编码语言（可能是 Oracle）中的关键字，因此请将列名日期更改为 JDate。
那么下面的工作会更好：
SELECT * FROM jokes WHERE JDate >= CURRENT_DATE() ORDER BY JScore DESC;
So we have a table called Jokes with columns JScore and JDate.
所以我们有一个名为 Jokes 的表，其中包含 JScore 和 JDate 列。
回答by JoeGalind
A better solution for "today" is:
“今天”的更好解决方案是：
SELECT * FROM jokes WHERE DATE(date) = DATE(NOW())
回答by Kevin Bowersox
Nathan's answer is very close however it will return a floating result set. As the time shifts, records will float off of and onto the result set. Using the DATE()function on NOW()will strip the time element from the date creating a static result set. Since the date()function is applied to now()instead of the actual datecolumn performance should be higher since applying a function such as date()to a date column inhibits MySql's ability to use an index.
Nathan 的回答非常接近，但它会返回一个浮动结果集。随着时间的推移，记录将在结果集上下浮动。使用DATE()on 函数NOW()将从创建静态结果集的日期中去除时间元素。由于将date()函数应用于now()而不是实际date列，性能应该更高，因为将函数date()应用于日期列会抑制 MySql 使用索引的能力。
To keep the result set static use:
要保持结果集静态使用：
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK)
ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH)
ORDER BY score DESC;
回答by ganji
I think using NOW()function is incorrect for getting time difference. Because by NOW()function every time your are calculating the past 24 hours.
You must use CURDATE()instead.
我认为使用NOW()函数来获取时差是不正确的。因为NOW()每次您计算过去 24 小时时都按功能计算。您必须CURDATE()改用。
function your_function($time, $your_date) {
if ($time == 'today') {
$timeSQL = ' Date($your_date)= CURDATE()';
}
if ($time == 'week') {
$timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
}
if ($time == 'month') {
$timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
}
$Sql = "SELECT * FROM jokes WHERE ".$timeSQL
return $Result = $this->db->query($Sql)->result_array();
}
回答by Sharad
To get last week's data in MySQL. This works for me even across the year boundries.
在 MySQL 中获取上周的数据。即使跨越年份，这对我也有效。
select * from orders_order where YEARWEEK(my_date_field)= YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK));
To get current week's data
获取当前周的数据
select * from orders_order where YEARWEEK(date_sold)= YEARWEEK(CURRENT_DATE());
回答by Mizo Games
Well, this solution will help you select only current month, current week and only today
好吧，此解决方案将帮助您仅选择当前月份、当前周和今天
SELECT * FROM games WHERE games.published_gm = 1 AND YEAR(addedon_gm) = YEAR(NOW()) AND MONTH(addedon_gm) = MONTH(NOW()) AND DAY(addedon_gm) = DAY(NOW()) ORDER BY addedon_gm DESC;
For Weekly added posts:
对于每周添加的帖子：
WEEKOFYEAR(addedon_gm) = WEEKOFYEAR(NOW())
For Monthly added posts:
对于每月添加的帖子：
MONTH(addedon_gm) = MONTH(NOW())
For Yearly added posts:
对于每年添加的帖子：
YEAR(addedon_gm) = YEAR(NOW())
you'll get the accurate results where show only the games added today, otherwise you may display: "No New Games Found For Today". Using ShowIF recordset is empty transaction.
您将获得仅显示今天添加的游戏的准确结果，否则您可能会显示：“今天没有找到新游戏”。使用 ShowIF 记录集是空事务。
