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.
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.
PostgreSQL provides you with two temporal data types for handling timestamp: timestamp : a timestamp without timezone one. timestamptz : timestamp with a timezone.
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.
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) .
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With