Postgresql 按30分钟、小时、天分组

按30分钟统计：

shool_time格式为varchar， 例如
201911050808、201911050820、201911050842
分组后结果：
20191105 0800
20191105 0830
20191105 0900
20191105 0930
注：小于30分钟的按00统计，大于30分钟的按30统计

SELECT name,sex,(SELECT (SELECT to_char( to_timestamp( school_time, 'yyyyMMddhh24mi' ), 'yyyyMMdd hh24' ) ) || ':' || to_char(to_timestamp(( SELECT FLOOR ( (SELECT to_char( to_timestamp( school_time, 'yyyyMMddhh24mi' ), 'mi' ) ) :: INTEGER / 30 ) * 30 ),'mi' ),'mi' ) ) school_time 
FROM
	student 
GROUP BY
	name,
	sex,
	school_time

按小时统计：

SELECT
	name,
	sex,
	( SELECT to_char( to_timestamp( school_time, 'yyyyMMdd hh24:mi' ), 'yyyyMMdd hh24' ) ) school_time 
FROM
	student 
GROUP BY
	name,
	sex,
	school_time

按天统计：

SELECT
	name,
	sex,
	( SELECT to_char( to_timestamp( school_time, 'yyyyMMdd hh24:mi' ), 'yyyyMMdd' ) ) school_time 
FROM
	student 
GROUP BY
	name,
	sex,
	school_time

按周统计：
注意:

DOW 周日(0) -> 周一 (6)
isodow 周一(1) -> 周日 (7)

GROUP BY  extract(isodow from timestamp '2019-11-03 20:38:40')
GROUP BY extract(DOW from timestamp '2019-11-03 20:38:40')

postgresql-参考文章