0

I need an SQL query to aggregate a time series from a Postgres table. I would like the time series to show the amount of rows created in a given time frame (i.e. weekly or daily).

The table data looks like this:

id dateCreated(timestamp)
1 2020-10-15 14:47:39
2 2020-10-15 14:47:39
3 2020-10-15 14:47:39
4 2020-10-16 14:47:39
5 2020-10-16 14:47:39
6 2020-10-17 14:47:39
7 2020-10-17 14:47:39
8 2020-10-17 14:47:39
9 2020-10-17 14:47:39

And the time series I would like as an out put would be some thing like this:

date(timestamp) numberOfRowCreated
2020-10-15 14:47:39 3
2020-10-16 14:47:39 2
2020-10-16 14:47:39 4
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Cooper
  • 1
  • 1

1 Answers1

2

Basic aggregation:

SELECT dateCreated, count(*) AS nr_of_rows
FROM   tbl
GROUP  BY dateCreated
ORDER  BY dateCreated;

To group by day / week / month / ... , use date_trunc():

SELECT date_trunc('day', dateCreated) AS day, count(*) AS nr_of_rows
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Related:

About the short syntax with ordinal number:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228