如何在具有“时间”字段的 postgresql 中按小时分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42117796/
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
how do I group by by hour in postgresql with a "time" field?
提问by Dervin Thunk
I have a table with a column ctimeof type time without time zone.
我有一个带有ctime类型列的表time without time zone。
| cdate | ctime
+------------+----------
| 2016-12-24 | 12:02:17
| 2016-12-24 | 12:02:32
| 2016-12-24 | 12:03:00
| 2016-12-24 | 12:02:10
I would like to group by both cdateand ctimebut would like for ctimeto count only hours.
我想按两者分组cdate，ctime但ctime只想计算小时。
回答by Vao Tsun
use date_trunc:
使用date_trunc：
group by cdate, date_trunc('hour', ctime)
group by cdate, date_trunc('hour', ctime)
回答by haoming
I think date_part('hour', ctime)is a better choice if you just want to group based on the hour.
date_part('hour', ctime)如果您只想按小时分组，我认为这是一个更好的选择。
date_part('hour', timestamp '2001-02-16 20:38:40') ---> 20
date_trunc('hour', interval '2 days 3 hours 40 minutes') ---> 2 days 03:00:00
