postgresql取 一个月每天的的数据，没有数据 补0

数据库存的数据：
select to_char ( start_time, ‘YYYY-MM-DD’ ) AS starttime, “count”(*) from warn_content GROUP BY start_time

这里先要创建一个所有时间格式的 中间表 ：通过函数 generate_series （）
SELECT
to_char ( b, ‘YYYY-MM-DD’ ) AS time
FROM
generate_series ( to_timestamp ( ‘2019-11-01’, ‘YYYY-MM-DD hh24:mi:ss’ ), to_timestamp ( ‘2019-11-30’, ‘YYYY-MM-DD hh24:mi:ss’ ), ‘1 days’ ) AS b
GROUP BY
time ORDER BY time asc

然后在和需要查询的 数据 连表查

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char ( b, ‘YYYY-MM-DD’ ) AS time
FROM
generate_series ( to_timestamp ( ‘2019-11-01’, ‘YYYY-MM-DD hh24:mi:ss’ ), to_timestamp ( ‘2019-11-30’, ‘YYYY-MM-DD hh24:mi:ss’ ), ‘1 days’ ) AS b
GROUP BY
time ORDER BY time asc

) as a

FULL OUTER JOIN

(
select to_char ( start_time, ‘YYYY-MM-DD’ ) AS starttime, “count”(*) as counts from warn_content GROUP BY start_time
) as b

on a.time=b.starttime
order by a.time asc