⭐PostgreSQL统计日、周、月、年的数据⭐

1、统计本年的数据,按月份展示

SELECT COUNT
	( tfn.* ),
	EXTRACT ( MONTH FROM tfn.uploadtime ) log_month
FROM
	tablex tfn
WHERE
	EXTRACT ( YEAR FROM tfn.uploadtime ) = EXTRACT ( YEAR FROM NOW( ) )
GROUP BY
	EXTRACT ( MONTH FROM tfn.uploadtime )

2、统计本月的数据,按天(1-31)展示

SELECT COUNT
	( tfn.* ),
	EXTRACT ( DAY FROM tfn.uploadtime ) log_day
FROM
	tablex tfn
WHERE
	EXTRACT ( YEAR FROM tfn.uploadtime ) = EXTRACT ( YEAR FROM NOW( ) )
	AND EXTRACT ( month FROM tfn.uploadtime ) = EXTRACT ( month FROM NOW( ) )
GROUP BY
	EXTRACT ( DAY FROM tfn.uploadtime )

3、统计本周的数据,按星期几展示

SELECT COUNT
	( tfn.* ),
	EXTRACT ( isodow FROM tfn.uploadtime ) log_isodow
FROM
	tablex tfn
WHERE
	EXTRACT ( YEAR FROM tfn.uploadtime ) = EXTRACT ( YEAR FROM NOW( ) )
	AND EXTRACT ( WEEK FROM tfn.uploadtime ) = EXTRACT ( WEEK FROM NOW( ) )
GROUP BY
	EXTRACT ( isodow FROM tfn.uploadtime )

注意：
DOW 周日(0) -> 周一 (6) ；
isodow 周一(1) -> 周日 (7)。
4、统计今天的数据,按小时展示

SELECT COUNT
   ( tfn.* ),
   EXTRACT ( HOUR FROM tfn.uploadtime ) log_hour
FROM
   tablex tfn
WHERE
   DATE ( tfn.uploadtime ) = CURRENT_DATE
GROUP BY
   EXTRACT (
   HOUR
   FROM
   tfn.uploadtime )