Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to GROUP BY date with a timestamp field in Postgres?

Lets say that I have 3 rows of data:

id  product_uuid                version_uuid                created_at
22  586d8e21b9529d14801b91bd    5a711a0094df04e23833d8ef    2018-02-10 19:51:15.075-05
23  586d8e21b9529d14801b91bd    5a711a0094df04e23833d8ef    2018-02-10 19:51:16.077-07
24  586d8e21b9529d14801b91bd    5a711a0094df04e23833d8ef    2018-02-11 19:51:15.077-05

And I want to group them by day via the created_at column.

SELECT created_at::date, COUNT(*)
FROM table_name
WHERE product_uuid = '586d8e21b9529d14801b91bd'
AND created_at > now() - interval '30 days'
GROUP BY created_at
ORDER BY created_at ASC

I would expect this to yield 2 rows:

created_at   count
2018-02-10   2
2018-02-11   1

But I actually get 3 rows:

created_at   count
2018-02-10   1
2018-02-10   1
2018-02-11   1

I realize that GROUP BY is still grouping by the fine-grain timestamp, but I'm not sure how to make Postgres use the truncated date instead.

like image 718
Jack Slingerland Avatar asked Feb 17 '18 13:02

Jack Slingerland


People also ask

How do I sort by date in PostgreSQL?

If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case. ORDER BY exam_date DESC ; Note that in PostgreSQL and in Oracle, NULL s are displayed last when sorting in ascending order and first when sorting in descending order.

Does Postgres support timestamp?

PostgreSQL provides you with two temporal data types for handling timestamp: timestamp : a timestamp without timezone one. timestamptz : timestamp with a timezone.

How is timestamp stored in PostgreSQL?

By casting "TimeStamp" to date you throw away the time part of the timestamp, so all values within one day will be considered equal and are returned in random order. It is by accident that the first rows appear in the order you desire. Don't cast to date in the ORDER BY clause if the time part is relevant for sorting.

How do I cast a date in PostgreSQL?

The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date. The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text) .


1 Answers

You need to truncate in the GROUP BY as well:

SELECT created_at::date, COUNT(*)
FROM table_name
WHERE product_uuid = '586d8e21b9529d14801b91bd' AND
      created_at > now() - interval '30 days'
GROUP BY created_at::date
ORDER BY created_at::date ASC;

Your version is aggregating by each date/time value but only showing the date component.

Also, I would recommend that you use current_date rather than now() so the first date is not truncated.

like image 85
Gordon Linoff Avatar answered Oct 05 '22 11:10

Gordon Linoff