mysql-sql计算

1.计算某个时间的上一周开始和结束时间

select
    DATE_FORMAT(xx.start, '%Y-%m-%d') as startTime,
    DATE_FORMAT(ADDDATE(xx.start, 6), '%Y-%m-%d') as
endTime
from
(
select
    ADDDATE(jan1, (t.week - WEEK(jan1, 5)) * 7 - WEEKDAY(jan1)) as start,
    year,
    WEEK
from
    (
    select
        MAKEDATE((
        select
            SUBSTR(DATE_SUB(SUBSTR(STR_TO_DATE('2020-11-01', '%Y-%m-%d'), 1, 10), interval 1 WEEK), 1, 4)), 1) as jan1,
        SUBSTR(DATE_SUB(SUBSTR(STR_TO_DATE('2020-11-01', '%Y-%m-%d'), 1, 10), interval 1 WEEK), 1, 4) as year,
        WEEKOFYEAR(DATE_SUB(STR_TO_DATE('2020-11-01', '%Y-%m-%d') , interval 1 WEEK)) as WEEK) t )xx

 

 

2.计算某个时间的上一周开始到结束时间

 

 

SELECT 
    date_format(lastWeek.`timeDay`,'%Y-%m-%d') as 'timeDay' 
FROM (
    select DATE_SUB(NOW(),interval 13 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 12 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 11 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 10 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 9 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 8 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 7 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 6 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 5 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 4 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 3 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 2 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 1 day) as 'timeDay'
) as lastWeek 
WHERE YEARWEEK(lastWeek.`timeDay`,1) = YEARWEEK(NOW(),1)-1