PostgreSQL中对日期时间进行分组

PostgreSQL 在PostgreSQL中对日期时间进行分组|极客教程 (geek-docs.com)

# 按年月日时分组

SELECT extract(year from created_time) as year, extract(month from created_time) as month, extract(day from created_time) as day, extract(hour from created_time) as hour, count(*) as count
FROM sc_app
GROUP BY extract(year from created_time), extract(month from created_time), extract(day from created_time), extract(hour from created_time)
ORDER BY extract(year from created_time), extract(month from created_time), extract(day from created_time), extract(hour from created_time);

 # 按年月日分组

SELECT extract(year from created_time) as year, extract(month from created_time) as month, extract(day from created_time) as day, count(*) as count
FROM sc_app
GROUP BY extract(year from created_time), extract(month from created_time), extract(day from created_time)
ORDER BY extract(year from created_time), extract(month from created_time), extract(day from created_time);

 # 按date分组

select date(created_time) as date, count(*) as count from sc_app group by date(created_time);