下面的示例是为了说明而故意详细描述的：

with limits as ( -- Determine starting and ending months
  select min(date) as start_date, max(date) as end_date
    from my_table
), months as ( -- Generate month series and explode to month halves
  select gs.month_start, o.offset_start, o.offset_end
    from limits 
         cross join lateral 
           generate_series(
             date_trunc('month', start_date),
             date_trunc('month', end_date),
             interval '1 month'
           ) as gs(month_start)
         cross join (
           values (interval '0 days',  interval '15 days'),
                  (interval '15 days', interval '1 month') 
         ) as o(offset_start, offset_end)
), ranges as ( -- Create tsrange column
  select tsrange(
           month_start + offset_start, 
           month_start + offset_end
         ) as bucket
    from months
) -- Join back to data, aggregate, and create labels
select concat(
         lower(r.bucket)::date,
         ' to ',
         (upper(r.bucket) - interval '1 day')::date
       ) as "Date", 
       count(t.date) as "Count"
  from ranges r
       left join my_table t on t.date <@ r.bucket
 group by bucket;

字符串
工作fiddle