如何使用mysql在每个星期五获取每周六到周四的数据

I have date column with list of dates stored i want to get every Saturday to Thursday data on every friday using mysql how can i do this?

我有日期列,其中存储了日期列表我希望每周五使用mysql获取每周六到周四的数据我该怎么做?

i tried with this query but don't know how to do with my requirements

我试过这个查询,但不知道如何处理我的要求

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT [...]
AND WorkDate >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
AND WorkDate <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

1 个解决方案

#1

You can refer this query for get data from Saturday to Thursday.

您可以参考此查询以获取星期六到星期四的数据。

1 Day= 86400 seconds // so you can set INTERVAL according to day range

1天= 86400秒//因此您可以根据日期范围设置INTERVAL

Note: This is logic only, you can manage your query. MySQL already having functions like DAYOFWEEK(), DATE(NOW())

注意:这只是逻辑,您可以管理您的查询。 MySQL已经具有DAYOFWEEK(),DATE(NOW())等功能

I'm refering this link for futher details: Date & Time Function in MySQL

我正在引用此链接以获取更多详细信息:MySQL中的日期和时间功能

SELECT
    satwk_beg + INTERVAL 0 second sat_beg,
    satwk_beg + INTERVAL 518399 second thu_end
   FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) satwk_beg
   FROM (SELECT SUBSTR('1234560',wkndx,1) daysbacktothursday
   FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Just execute above query and check you will get output from Saturday to Thursday.

只需执行以上查询并检查您将从周六到周四获得输出。

Here is range of other weeks,

这是其他几周的范围,

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun

(SELECT SUBSTR('6012345',wkndx,1)执行从周一结束太阳开始的一周

(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon

(SELECT SUBSTR('5601234',wkndx,1)在星期二结束星期一开始

(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue

(SELECT SUBSTR('4560123',wkndx,1)在星期三结束星期二开始

(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed

(SELECT SUBSTR('3456012',wkndx,1)做周开始周四截至周三

(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu

(SELECT SUBSTR('2345601',wkndx,1)从星期五开始结束星期四

(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri

(SELECT SUBSTR('1234560',wkndx,1)从周六开始周五开始

(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat

(SELECT SUBSTR('0123456',wkndx,1)执行从太阳结束星期六开始的一周

#1