数据库(postgresql和mysql)统计一个范围内的日期，没有当天日期的数据统计补0

说明

在项目开发里面，有时候经常要根据时间对数据做统计返回给前端，如果只取一段范围内的时间查询，只返回数据库中查询到的到还简单，如果要把没有的日期也统计出来那么就感觉稍微麻烦了一点。

pgsql语句写法：
一般sql语句查询如下:
其中我的starttime在数据库中类型是timestamp
to_char()将日期转换为字符串

我这里查询的时间是2020-06-20到2020-06-30的时间段，发现只查到了三天的数据:

很明显这样写不是我所期望的结果，我期望的是2020-06-20到2020-06-27号的也能查出来并补0。

如果要实现我的那种期望结果，需要用到这个函数，如下：

补充

select date(t) as day
from 
generate_series('2020-06-20'::date,'2020-06-30', '1 days') as t

它能将你的开始日期到结束日期之间的日期都能显示出来

实现

将上面的二者结合，联表查询发现就查询到了自己期望的结果。

在实际开发中只需要将2个日期2020-06-20和2020-06-30换成对应的开始日期参数和结束日期参数，那么这个统计结果就是符合期望的结果的了。

SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char(b,'yyyy-MM-dd') AS time
FROM
generate_series('2020-06-20'::date,'2020-06-30','1 days') AS b
GROUP by time 
ORDER BY time
) as a
LEFT JOIN
(
select to_char(starttime,'yyyy-MM-dd') AS starttime, count(starttime) AS counts 
from rnodbv2.v2_m_ctest_log_info
where to_char(starttime,'yyyy-MM-dd')>='2020-06-20' and to_char(starttime,'yyyy-MM-dd')<='2020-06-30'
GROUP BY to_char(starttime,'yyyy-MM-dd')
) as b
on a.time=b.starttime
order by a.time

结果如下：

mysql语句写法：
一般sql语句查询如下:
其中我的create_time在数据库中类型是datetime

– 将日期转化为字符串
DATE_FORMAT(CREATE_TIME,’%Y-%m-%d %H:%i:%s’)
– 将字符串转化为日期
STR_TO_DATE(‘2020-09-04 16:49:05’, ‘%Y-%m-%d %H:%i:%s’)

我这里查询的时间是2020-10-20到2020-10-30的时间段，发现只查到了二天的数据:

这里我使用这个语句来做一个虚拟的日期表

SELECT @cdate := DATE_ADD(@cdate,INTERVAL - 1 DAY) date
FROM (SELECT @cdate :=DATE_ADD('2020-10-29', INTERVAL + 1 DAY) FROM sys_user) t1 
WHERE @cdate>'2020-10-01'

注意：这个sql有个局限性，那就是你查的表的数据行数必须要大于你的日期天数，它才能显示你时间范围内的所有日期，因为我的sys_user表里面只有9条数据，所以它只会返回最后9天的日期，如果这个表数据有29条以上，那么就能显示1-29号的日期

sys_user表：

实现

将上面的二者结合，联表查询发现就查询到了自己期望的结果。

select a.date, ifnull(b.counts,0) as counts
from 
(select @cdate := date_add(@cdate,INTERVAL - 1 DAY) as date
from (select @cdate :=date_add('2020-10-29', INTERVAL + 1 DAY) from sys_user) t1 
where @cdate>'2020-10-01') as a
left join 
(select date_format(create_time,'%Y-%m-%d') create_time,count(create_time) as counts
from sys_user
where date(create_time)>='2020-10-01' and date(create_time)<='2020-10-29'
group by date_format(create_time,'%Y-%m-%d')
order by date_format(create_time,'%Y-%m-%d')) as b
on a.date=date(b.create_time)
order by a.date

结果如下:

当然前提是sys_user表的数据行数大于29条日期才能显示完全。实际开发一般情况下表数据不会像我这么少吧，我这里只是测试。我只是不想在创建一个日期表才这样处理，为什么mysql就没有pgsql那样的一个系统函数呢，给一个开始日期和结束日期就能生成一个虚表日期。