PHP / MySQL：检索最后一个*整个*周的条目 [英] PHP/MySQL: Retrieving the last *full* weeks entries

问题描述

我正在使用以下SQL从表中检索最近七天的条目：

 已购买> ; = date_sub（now（），间隔7天）
  

但是，我需要更改这个检索最后一个星期的星期六（星期六到星期六午夜）。所以基本上，整个星期的结果从来没有改变，但是当有人在星期天上午参观时，他们会刷新。

我不知道如何工作出来的日子等。还有，内置MySQL的功能这样做？

我希望我已经解释了很清楚。
这是一个PHP应用程序。

解决方案

查看MySQL函数。mysql.com / doc / refman / 5.0 / en / date-and-time-functions.html＃function_yearweek =nofollow noreferrer

所以你可以做一些像

  SELECT * FROM table WHERE YEARWEEK（已购买）= YEARWEEK（NOW（））; 
  

您可以使用第二个模式参数

$ b更改星期几然而，最好的方法是计算00:00的上一个星期几的日期，然后数据库不必为每一行运行一个函数，但是我不能看到在MySQL中这样做的一个明显的方法。你可以很容易地在php中生成这样的东西，例如

  $ sunday = date（（'Ymd H：i：s' ），strtotime（'last sunday 00:00'））; 
 $ sql =SELECT * FROM table WHERE purchased> ='$ sunday'; 
  

I'm currently using the following SQL for retrieving the last seven days worth of entries from a table:

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?

I hope I've explained that clearly enough. This is in a PHP application.

解决方案

see the MySQL function 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

$sunday = date(('Y-m-d H:i:s'), strtotime('last sunday 00:00'));
$sql = "SELECT * FROM table WHERE purchased >= '$sunday'";

这篇关于PHP / MySQL：检索最后一个*整个*周的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！