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