Postgres count number or rows and group them by timestamp

Question

Let's assume I have one table in postgres with just 2 columns:

ID which is PK for the table (bigint)
time which is type of timestamp

Is there any way how to get IDs grouped by time BY YEAR- when the time is date 18 February 2005 it would fit in 2005 group (so result would be)

year  number of rows
1998  2
2005  5

AND if the number of result rows is smaller than some number (for example 3) SQL will return the result by month

Something like

month            number of rows
(February 2018)  5
(March 2018)     2

Is that possible some nice way in postgres SQL?

Not easily (other posters, feel free to correct me). This is something that is easier to handle in the presentation layer outside of SQL. Your SQL query would pass the results by month to whatever tool you are using to display this data and the tool would decide how to display it (either by grouping it into year or displaying individual by month) — Twelfth
– Twelfth, Commented Mar 14, 2018 at 18:17
Okay, thank you for the answer. But how can I get that result for a years in the postgres? (then I can check in PHP how many rows I got and run SQL for months maybe) — Angelux
– Angelux, Commented Mar 14, 2018 at 18:19
Google on Postgres questions works pretty well. Google 'Postgres year only from date stackoverflow'. Answer there refers to postgresql.org/docs/8.0/static/… under year is this example : SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') Change TIMESTAMP '2001-02-16 20:38:40' to your field name. Link has instructions for pretty close to any other date extract you may need. — Twelfth
– Twelfth, Commented Mar 14, 2018 at 18:23
@Twelfth: please do not post links to completely outdated Postgres versions. It's better to replace the version number in the URL with current: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Mar 14, 2018 at 18:28

Laurenz Albe · Accepted Answer · 2018-03-14 19:30:33Z

1

You can do it using window functions (as always).

I use this table:

TABLE times;

 id |               t               
----+-------------------------------
  1 | 2018-03-14 20:04:39.81298+01
  2 | 2018-03-14 20:04:42.92462+01
  3 | 2018-03-14 20:04:45.774615+01
  4 | 2018-03-14 20:04:48.877038+01
  5 | 2017-03-14 20:05:08.94096+01
  6 | 2017-03-14 20:05:16.123736+01
  7 | 2017-03-14 20:05:19.91982+01
  8 | 2017-01-14 20:05:32.249175+01
  9 | 2017-01-14 20:05:35.793645+01
 10 | 2017-01-14 20:05:39.991486+01
 11 | 2016-11-14 20:05:47.951472+01
 12 | 2016-11-14 20:05:52.941504+01
 13 | 2016-10-14 21:05:52.941504+02
(13 rows)

First, group by month (subquery per_month).
Then add the sum per year with a window function (subquery with_year).
Finally, use CASE to decide which one you will output and remove duplicates with DISTINCT.

SELECT DISTINCT
   CASE WHEN yc > 5
        THEN mc
        ELSE yc
   END AS count,
   CASE WHEN yc > 5
        THEN to_char(t, 'YYYY-MM')
        ELSE to_char(t, 'YYYY')
   END AS period
FROM (SELECT
         mc,
         sum(mc) OVER (PARTITION BY date_trunc('year', t)) AS yc,
         t
      FROM (SELECT
               count(*) AS mc,
               date_trunc('month', t) AS t
            FROM times
            GROUP BY date_trunc('month', t)
           ) per_month
     ) with_year
ORDER BY 2;

 count | period  
-------+---------
     3 | 2016
     3 | 2017-01
     3 | 2017-03
     4 | 2018
(4 rows)

answered Mar 14, 2018 at 19:30

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Angelux Over a year ago

Hello. It works like charms but I have a one small problem. I had to modify your code like r.added BETWEEN '2018-03-10'::timestamp AND '2018-03-19'::timestamp INSIDE the per_month subquery cause I sometimes want to get these stats just for entries in some time interval. (So I want maybe get there time records for entries from 2016-05 to 2017-05- and result would be 2016-05 5, 2017-5 etc.) I added this where clause there but script returns only one record every time. How can I modify that?

Laurenz Albe Over a year ago

Perhaps that additional clause filters out all rows except for a single year (or month)? Try running only the inner query and check.

Thorsten Kettner · Accepted Answer · 2018-03-15 12:05:34Z

0

Just count years. If it's at least 3, then you group by years, else by months:

select
  case (select count(distinct extract(year from time)) from mytable) >= 3 then
    to_char(time, 'yyyy')
  else
    to_char(time, 'yyyy-mm')
  end as season,
  count(*)
from mytable
group by season
order by season;

(Unlike many other DBMS, PostgreSQL allows to use alias names in the GROUP BY clause.)

answered Mar 15, 2018 at 12:05

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Postgres count number or rows and group them by timestamp

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related