如何设置SQL以查找从上周日到星期日（1周）的记录 [英] How to set SQL to find records from last sunday to this sunday (1 week)

问题描述

这与我现在相似，这是：

This is similar to what I have now, which is:

SELECT COUNT（author）FROM`posts` WHERE`date`> ; = DATE_SUB（CURDATE（），INTERVAL 1 WEEK）AND author ='FooBar'

这将给我一个计数多少次一个作者在过去的一个星期发布了。

This will give me a count of how many times an author has posted in the past 1 week.

如果我要在星期天下午5:30运行程序来寻找帖子，我想要这样做从上午12:00 AM到本周日中午12:00。同样，如果我忘了在星期天跑步，现在是星期一。我仍然希望它从上周日12:00 AM到星期天运行，就在上午12:00之前

I want to instead have it so if I am to run the program on Sunday at 5:30 PM to look for posts from last Sunday 12:00 AM to this Sunday 12:00 AM. Likewise, if I forget to run it on Sunday and it's monday now. I still want it to run from last Sunday 12:00 AM to the Sunday that just past 12:00 AM

编辑：

我已经使用PHP来形成正确的SQL语句，但我仍然很好奇如何在SQL中执行此操作。

I have done what I needed using PHP to form the correct SQL statement, but I am still curious how to do this in just SQL.

<?php
    $dayofweek = strftime("%A",time());
    if($dayofweek == "Sunday") {
        $last_sunday = date('Y-m-d h:i:s',strtotime('Last Sunday'));
        $this_sunday = date('Y-m-d h:i:s',strtotime('Sunday'));
    } else {
        $last_sunday = date('Y-m-d h:i:s',strtotime('Last Sunday',strtotime('Last Sunday')));
        $this_sunday = date('Y-m-d h:i:s',strtotime('Last Sunday'));
    }
    print "last_sunday={$last_sunday}<br>";
    print "this_sunday={$this_sunday}<br>";
    print "SELECT COUNT(author) FROM `posts` WHERE `date` <= '$this_sunday' AND `date` >= '$last_sunday' AND author='FooBar'";
?>

推荐答案

为了获得最新的星期日午夜，它。将 NOW（）的两个实例替换为您的日期时间以检查另一个日期。

To get the latest preceding sunday midnight, this should do it. Replace both instances of NOW() with your datetime to check another date.

SELECT DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY) latest_sun

为了一星期前得到星期日，而是使用 DAYOFWEEK（NOW（））+ 6天。

To get the sunday one week earlier, instead use DAYOFWEEK(NOW())+6 DAY.

编辑：这将使您的查询;

That'd make your query;

SELECT COUNT(author)
FROM `posts` 
WHERE author='FooBar'
  AND `date` >= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6 DAY)
  AND `date` <  DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY)

这篇关于如何设置SQL以查找从上周日到星期日（1周）的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！