postgresql按日、周、月分组

按天分组

SELECT date_trunc('day', loggedin) AS "Day" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '3 months' 
GROUP BY 1 
ORDER BY 1;

按周分组

SELECT date_trunc('week', loggedin) AS "Week" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '3 months' 
GROUP BY 1
ORDER BY 1;

按月分组

SELECT date_trunc('month', loggedin) AS "Month" , count(*) AS "No. of users"
FROM logins
WHERE created > now() - interval '1 year' 
GROUP BY 1
ORDER BY 1;

显示近十个月年月

SELECT to_char(date_trunc('month', YearMonth),'YYYY-MM')
FROM generate_series(current_date - interval '10 month',
        current_date, '1 month'::interval) YearMonth;