MySQL查询当周的yearweek从星期四开始，星期三结束 [英] MySQL query yearweek of the current week to start on a Thursday, end on Wednesday

问题描述

我设置了一个 SQL 语句来获取当前日历周的数据.默认情况下，这会抓取从周日开始到周六结束的数据.我希望改变这一点，以便日历周的开始是星期四，星期三结束.以下是我的声明:

I have a SQL statement set up to grab data from the current calendar week. By default, this grabs data starting on Sunday and ending on Saturday. I was hoping to change this so that the start of the calendar week is Thursday and it ends on Wednesday. Below is my statement:

SELECT * 
FROM transactions 
WHERE yearweek(transactionDate) = yearweek(now()) 
ORDER BY transactionDate DESC

有没有办法修改 yearweek 以允许这样做?

Is there a way to modify the yearweek in a way to allow this?

谢谢！

推荐答案

您可以利用 WEEKDAY() 返回一个代表星期几的数字(0 = 星期一，6 = 星期日)和一些简单的数学重写此查询.

You can take advantage of WEEKDAY() which returns a number representing the day of the week (0 = Monday, 6 = Sunday) and some straightforward maths to rewrite this query.

从所选日期减去您希望一周开始的工作日(在您的情况下为 4 = 星期四)，加上 7 并从 7 中取余数.这将为您提供减去以开始的天数你的范围.

Subtract the weekday you want the week to start on (in your case 4 = Thursday) from the selected date, add 7 and take the remainder from 7. This will give you the number of days to subtract to get the start of your range.

类似的逻辑适用于计算范围的结束日期.

A similar logic applies to calculate the end date of the range.

SELECT * 
FROM transactions 
WHERE DATE(transactionDate)
    BETWEEN DATE_SUB(DATE(NOW()), INTERVAL (WEEKDAY(NOW()) - 4 + 7) % 7 DAY)
        AND DATE_ADD(DATE(NOW()), INTERVAL 6 - (WEEKDAY(NOW()) - 4 + 7) % 7 DAY)
ORDER BY transactionDate DESC;

对于不同的开始日期，将查询中的 4 替换为工作日.

For a different starting date, substitute the weekday for 4 in the query.

这篇关于MySQL查询当周的yearweek从星期四开始，星期三结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！