如何在Postgres中使用时间戳字段对日期进行分组? [英] How to GROUP BY date with a timestamp field in Postgres?

问题描述

让我们说我有3行数据:

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

我想通过 created_at 列按天对它们进行分组.

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

我希望这会产生2行:

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

但是我实际上得到了3行:

But I actually get 3 rows:

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

我意识到 GROUP BY 仍按细粒度时间戳分组，但是我不确定如何使Postgres使用截断的日期代替.

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.

推荐答案

您还需要在 GROUP BY 中进行截断:

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.

此外，我建议您使用 current_date 而不是 now()，这样就不会截断第一个日期.

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

这篇关于如何在Postgres中使用时间戳字段对日期进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！