Mysql 时间查询语句

Mysql 时间查询语句

获取前12天

		SELECT
        curdate( ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 1 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 2 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 3 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 4 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 5 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 6 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 7 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 8 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 9 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 10 DAY ) AS click_date UNION ALL
        SELECT
        date_sub( curdate( ), INTERVAL 11 DAY ) AS click_date

获取前12周

SELECT
        ( @s := @s + 1 ) AS _index,
        WEEK ( DATE( DATE_SUB( curdate( ), INTERVAL @s WEEK ) ) ) AS _date
        FROM
        information_schema.CHARACTER_SETS,
        ( SELECT @s :=- 1 ) AS init
        WHERE
        @s < 11
        ORDER BY
        _date

获取当天24小时

SELECT
        0 HOUR UNION ALL
        SELECT
        1 HOUR UNION ALL
        SELECT
        2 HOUR UNION ALL
        SELECT
        3 HOUR UNION ALL
        SELECT
        4 HOUR UNION ALL
        SELECT
        5 HOUR UNION ALL
        SELECT
        6 HOUR UNION ALL
        SELECT
        7 HOUR UNION ALL
        SELECT
        8 HOUR UNION ALL
        SELECT
        9 HOUR UNION ALL
        SELECT
        10 HOUR UNION ALL
        SELECT
        11 HOUR UNION ALL
        SELECT
        12 HOUR UNION ALL
        SELECT
        13 HOUR UNION ALL
        SELECT
        14 HOUR UNION ALL
        SELECT
        15 HOUR UNION ALL
        SELECT
        16 HOUR UNION ALL
        SELECT
        17 HOUR UNION ALL
        SELECT
        18 HOUR UNION ALL
        SELECT
        19 HOUR UNION ALL
        SELECT
        20 HOUR UNION ALL
        SELECT
        21 HOUR UNION ALL
        SELECT
        22 HOUR UNION ALL
        SELECT
        23 HOUR

获取12月

SELECT
        ( @s := @s + 1 ) AS _index,
        MONTH ( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s MONTH ) ) ) AS _date
        FROM
        information_schema.CHARACTER_SETS,
        ( SELECT @s :=- 1 ) AS init
        WHERE
        @s < 11
        ORDER BY
        _date

获取最近12个月

select date_format(curdate(),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 1 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 2 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 3 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 4 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 5 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 6 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 7 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 8 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 9 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 10 month),'%Y-%m') AS `mon` union

        select date_format((curdate() - interval 11 month),'%Y-%m') AS `mon`

获取时间段的年月

select 
DATE_FORMAT(m1, '%Y-%m') as mon
from
(select (CONCAT('2019-06','-01')  - INTERVAL DAYOFMONTH(CONCAT('2019-06','-01'))-1 DAY) + INTERVAL m MONTH as m1
    from
    (
    select @rownum:=@rownum+1 as m from
    (select 1 union select 2 union select 3 union select 4) t1,
    (select 1 union select 2 union select 3 union select 4) t2,
    (select 1 union select 2 union select 3 union select 4) t3,
    (select 1 union select 2 union select 3 union select 4) t4,
    (select @rownum:=-1) t0
    ) d1
) d2 
where m1<=CONCAT('2020-06','-01')
order by m1