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);