User Tools

Site Tools


sql_basic_stats

Number of messages for a given month

(including days with no message). Example for July, 2015

SELECT day::date, coalesce(cnt,0) as cnt FROM
 (select date_trunc('day',msg_date) as day, count(*) as cnt
  from mail 
  where msg_date>='2015-07-01'::timestamptz
    and msg_date<'2015-07-01'::timestamptz+interval '1 month'
    -- and other optional conditions
  group by 1
 ) as list
RIGHT JOIN
   generate_series('2015-07-01'::timestamptz,
                   '2015-07-01'::timestamptz+interval '1 month'-interval '1 day',
                    interval '1 day') as d(day)
   USING (day)
ORDER BY 1 
sql_basic_stats.txt · Last modified: 2015/11/18 04:58 by daniel