Grouping data by year based on two timestamp columns

Question

My data table has the following columns:

id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP

I want to product a report of the count of each name (which can appear several times in the table) that is active each year. (also, the deleted timestamp may be null, if it's currently still active).

So far I’ve managed to do this by manually entering the years in a long list of union statements (see below). I’m sure there’s a better way! And I have many more similar queries to perform. I tried to create a PL/pgSQL function, but couldn’t figure out how to get the year as a variable and also how to get the correct output. I would be very happy with a statement, or PL/pgSQL function to achieve this.

((select '2016' yr, name, count(*) from data
where (((deleted - '2016-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2016-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
union all
((select '2015' yr, name, count(*) from data
where (((deleted - '2015-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2015-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
etc..

I got the years using:

select distinct date_part('year',created) from data order by date_part('year',created);

And then manually entered them in the very long union statement. (In my case 2007-2016!)

score 2 · Accepted Answer · edited May 23 '17 at 12:40

With generate_series() in a LATERAL join (Postgres 9.3+) and date_trunc(), this can be short & simple:

SELECT EXTRACT(YEAR FROM yr)::text AS year, name, count(*) AS ct
FROM   data
     , generate_series(date_trunc('year', created)  -- LATERAL join
                     , COALESCE(deleted, localtimestamp)
                     , interval '1 year') yr
GROUP  BY yr, name
ORDER  BY yr, ct DESC;

That's all. Returns results for all years between the earliest created and the current year.
The trick is to generate one row for each year the base row overlaps with, before aggregating.

Alternative:

SELECT EXTRACT(YEAR FROM yr) AS year, d.name, count(*) AS count
FROM   generate_series ((SELECT date_trunc('year', min(created)) FROM data)
                       , localtimestamp, interval '1 year') yr
JOIN   data d ON d.created < yr::timestamp + interval '1 year'
            AND (d.deleted > yr::timestamp OR d.deleted IS NULL)
GROUP  BY yr, d.name
ORDER  BY count(*) DESC;

This generates the whole range of years before joining. May be more convenient to compute numbers for a manual selection of years.

Optimizing queries on a range of timestamps (two columns)

Grouping data by year based on two timestamp columns

1 Answers1