问题描述
我设置了一个 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从星期四开始,星期三结束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!