SELECT T
.TIME,
COUNT (b.risk_type_name )
FROM
(
SELECT
to_char( generate_series ( to_timestamp( '2021-06-20 00', 'yyyy-mm-dd HH24' ),
to_timestamp( '2021-06-20 23', 'yyyy-mm-dd HH24' ), '1 hours' ), 'yyyy-mm-dd HH24' ) AS TIME
)
T LEFT JOIN table b ON T.TIME = to_char(b.create_time,'yyyy-mm-dd HH24')
WHERE
b.create_time >= to_timestamp( '2021-06-20 00:00:00', 'yyyy-mm-dd HH24:MI:ss' )
AND b.create_time <= to_timestamp( '2021-06-20 23:59:59', 'yyyy-mm-dd HH24:MI:ss' )
GROUP BY
T.TIME
pgsql generate_series
于 2021-08-10 18:56:06 首次发布