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

2、结果示例