Postgres group by quarter
If someone also needs the year, they can use this:
SELECT (extract(year from published_date)::text || '.Q' || extract(quarter from published_date)::text) as quarter
This will return the value in the form 2018.Q1
.
How does it work? It extracts both year and quarter (which are both of type double precision
), casts them into strings, and concatenates the whole in something readable.
If you want both quarter and year you can use date_trunc:
SELECT
date_trunc('quarter', published_date) AS quarter
This gives the date rounded to the start of the quarter, e.g. 2020-04-01
, and has the advantage that subsequent steps in the pipeline can read it like a normal date.
(This compares to extract (extract(quarter FROM published_date)
), which gives the number of the quarter, i.e. 1, 2, 3, or 4.)
Assuming that the published_date
is a date type column you can use the extract
function like this:
select
topic,
person,
extract(quarter from published_date) as quarter,
count(*)
from
table1
group by
topic,
person,
extract(quarter from published_date)
order by
extract(quarter from published_date) asc
Sample SQL Fiddle
If the dates can fall into different years you might want to add the year to the select and group by.