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

迈不过友情╰ 2023-09-25 18:47 14阅读 0赞

#### 一、查询连续年的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、查询结果截图 #####

![在这里插入图片描述][d834ccef227c45ee91b319e9c2a6fba8.png]

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

#### 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、结果示例 #####

![在这里插入图片描述][08546c62e7364976b2ea5d14aabfee88.png]

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

##### 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

##### 2、结果示例 #####

![在这里插入图片描述][226fb01282de465681996bb315124414.png]

[d834ccef227c45ee91b319e9c2a6fba8.png]: https://img-blog.csdnimg.cn/d834ccef227c45ee91b319e9c2a6fba8.png
[08546c62e7364976b2ea5d14aabfee88.png]: https://img-blog.csdnimg.cn/08546c62e7364976b2ea5d14aabfee88.png
[226fb01282de465681996bb315124414.png]: https://img-blog.csdnimg.cn/226fb01282de465681996bb315124414.png