postgresql - 我如何在 postgresql 中的两个日期之间获取记录月份(按月份分组日期)？

Question

I am using below query for fetching the record month wise but it give wrong data

SELECT 
     (count( server_time::timestamp::date)) , 
     min(server_time::timestamp::date) as "Month Date"
FROM 
     complaint_details_v2 
WHERE 
     server_time between '2018/08/01' and '2018/10/30' 
GROUP BY 
     floor((server_time::timestamp::date  - '2018/08/01'::date)/30)
ORDER BY 
     2 ASC

结果

Count Month Date
2774  2018-08-01
5893  2018-08-31
1193  2018-09-30

但是结果会是

Count Month Date
2774  2018-08-01
5893  2018-09-01
1193  2018-10-01

Answer 1

使用date_trunc

demo:db<>fiddle

SELECT
    count(*), 
    date_trunc('month', servertime)::date as month_date
FROM log
GROUP BY date_trunc('month', servertime)
ORDER BY 2