PostgreSQL查询近多少年、多少周、根据日期查询这个周的开始时间和结束时间-蒲公英云

PostgreSQL查询近多少年、多少周、根据日期查询这个周的开始时间和结束时间

一、查询连续年的SQL语句

1、查询连接近20年的sql语句

SELECT tab."year" || '年' as text,tab."year" as code FROM (WITH RECURSIVE T ( n ) AS ( 
    SELECT DATE( now( ) - INTERVAL '20 YEAR' ) UNION ALL 
    SELECT n + 1 FROM T  
    WHERE n < DATE ( now( ) ) ) SELECT 
    to_char( n, 'yyyy' ) AS YEAR FROM T GROUP BY YEAR 
ORDER BY YEAR desc) tab

2、查询结果截图

在这里插入图片描述

二、查询某个时间段的周数

1、查询2021-01-01 00：00：00到2021-12-31 23:59:59的周数列表

SELECT tab.times as code,'第'||tab.weeknum||'周' as text FROM (SELECT MIN
        ( times ) times,
        CAST ( date_part( 'week', times ) AS VARCHAR ) weeknum 
    FROM
        (
        SELECT A
            times 
        FROM
            generate_series (
                ( CAST ( ( '2021' || '-01-01 00:00:00' ) AS TIMESTAMP ) ),
                    (SELECT CASE 
                WHEN '2022'=to_char(now(), 'yyyy')    THEN
                    CAST ( to_char( CURRENT_DATE, 'yyyy-MM-dd' ) || ' 23:59:59' AS TIMESTAMP ) + '10 day'
                ELSE
                    CAST ( '2021' || '-12-31 23:59:59' AS TIMESTAMP ) 
            END as b),
                '1 day' 
            ) AS A 
        ) AS b 
    GROUP BY
        weeknum 
    ORDER BY
        times ASC ) tab

2、结果示例

在这里插入图片描述

三、根据传入的一个时间计算本周的开始时间和结束时间

1、查询2020-01-01 00:00:00时间的按周查询开始时间和结束时间

SELECT
    date_trunc( 'week', CAST ( '2020-01-01 00:00:00' AS TIMESTAMP ) ) AS start_time,
    date_trunc( 'week', CAST ( '2020-01-01 00:00:00' AS TIMESTAMP ) ) + INTERVAL '6d' AS end_time