I'm currently using the following SQL for retrieving the last seven days worth of entries from a table:
我目前正在使用以下SQL从表中检索最近七天的条目:
purchased >= date_sub(now() ,interval 7 day)
However, I need to change this so it retrieves the last full weeks worth of entries (midnight Saturday to midnight Saturday). So basically, throughout the week the results never change, but when someone visits on Sunday morning they will have refreshed.
但是,我需要对此进行更改,以便检索最后几周完整的条目(周六午夜到周六午夜)。所以基本上,整个星期结果都不会改变,但是当有人在周日早*问时,他们会精神焕发。
I just can't get my head around how to work out the days etc. Also, are there built in MySQL functions for doing this?
我只是无法理解如何解决这些日子等问题。另外,有没有内置的MySQL函数来做这个?
I hope I've explained that clearly enough. This is in a PHP application.
我希望我已经清楚地解释了这一点。这是在PHP应用程序中。
2 个解决方案
#1
2
see the MySQL function YEARWEEK().
看看MySQL函数YEARWEEK()。
So you could do something like
所以你可以做点什么
SELECT * FROM table WHERE YEARWEEK(purchased) = YEARWEEK(NOW());
You can change the starting day of the week by using a second mode parameter
您可以使用第二个模式参数更改一周的开始日期
What might be better however is to somehow calculate the date of 'last sunday at 00:00', and then the database would not have to run a function for each row, but I couldn't see an obvious way of doing that in MySQL. You could however easily generate this in php and do something like
可能更好的是以某种方式计算'00:00'的最后一个星期日的日期,然后数据库不必为每一行运行一个函数,但我在MySQL中看不到一个明显的方法。但是你可以在php中轻松生成这个并做类似的事情
$sunday = date(('Y-m-d H:i:s'), strtotime('last sunday 00:00'));
$sql = "SELECT * FROM table WHERE purchased >= '$sunday'";
#2
0
MySQL has a WEEK() function, that allows you to get the numeric week of the year from a date. You can get (the week of today) -1, and compare that to the week of your records you are filtering on.
MySQL有一个WEEK()函数,它允许你从一个日期获得一年中的数字周。您可以获得(今天的一周)-1,并将其与您要过滤的记录周比较。
#1
2
see the MySQL function YEARWEEK().
看看MySQL函数YEARWEEK()。
So you could do something like
所以你可以做点什么
SELECT * FROM table WHERE YEARWEEK(purchased) = YEARWEEK(NOW());
You can change the starting day of the week by using a second mode parameter
您可以使用第二个模式参数更改一周的开始日期
What might be better however is to somehow calculate the date of 'last sunday at 00:00', and then the database would not have to run a function for each row, but I couldn't see an obvious way of doing that in MySQL. You could however easily generate this in php and do something like
可能更好的是以某种方式计算'00:00'的最后一个星期日的日期,然后数据库不必为每一行运行一个函数,但我在MySQL中看不到一个明显的方法。但是你可以在php中轻松生成这个并做类似的事情
$sunday = date(('Y-m-d H:i:s'), strtotime('last sunday 00:00'));
$sql = "SELECT * FROM table WHERE purchased >= '$sunday'";
#2
0
MySQL has a WEEK() function, that allows you to get the numeric week of the year from a date. You can get (the week of today) -1, and compare that to the week of your records you are filtering on.
MySQL有一个WEEK()函数,它允许你从一个日期获得一年中的数字周。您可以获得(今天的一周)-1,并将其与您要过滤的记录周比较。