PostgreSQL - 按时间戳值分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6832566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - GROUP BY timestamp values?
提问by Shadowman
I've got a table with purchase orders stored in it. Each row has a timestamp indicating when the order was placed. I'd like to be able to create a report indicating the number of purchases each day, month, or year. I figured I would do a simple SELECT COUNT(xxx) FROM tbl_orders GROUP BY tbl_orders.purchase_time and get the value, but it turns out I can't GROUP BY a timestamp column.
我有一张桌子，里面存放着采购订单。每行都有一个时间戳，指示下订单的时间。我希望能够创建一个报告，指出每天、每月或每年的购买次数。我想我会做一个简单的 SELECT COUNT(xxx) FROM tbl_orders GROUP BY tbl_orders.purchase_time 并获取值，但结果我不能 GROUP BY 一个时间戳列。
Is there another way to accomplish this? I'd ideally like a flexible solution so I could use whatever timeframe I needed (hourly, monthly, weekly, etc.) Thanks for any suggestions you can give!
有没有另一种方法来实现这一点？理想情况下，我想要一个灵活的解决方案，这样我就可以使用我需要的任何时间范围（每小时、每月、每周等）。感谢您提供的任何建议！
采纳答案by Anomie
Grouping by a timestamp column works fine for me here, keeping in mind that even a 1-microsecond difference will prevent two rows from being grouped together.
按时间戳列分组在这里对我来说很好用，请记住，即使是 1 微秒的差异也会阻止两行组合在一起。
To group by larger time periods, group by an expression on the timestamp column that returns an appropriately truncated value. date_trunccan be useful here, as can to_char.
要按较大的时间段分组，请按时间戳列上的表达式分组，该表达式返回适当的截断值。date_trunc在这里很有用，也可以to_char。
回答by Bradley
This does the trick without the date_trunc function (easier to read).
这在没有 date_trunc 函数的情况下也能奏效（更容易阅读）。
// 2014
select created_on::DATE from users group by created_on::DATE
// updated September 2018 (thanks to @wegry)
select created_on::DATE as co from users group by co
What we're doing here is casting the original value into a DATE rendering the time data in this value inconsequential.
我们在这里所做的是将原始值转换为 DATE，从而使该值中的时间数据无关紧要。
