在 SQL(postgresql) 中，如何根据“没有时区的时间戳”列进行分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28039019/
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
In SQL(postgresql) How to group based on a "timestamp without time zone" column?
提问by user3692521
I am trying to write some queries to group things based on each month in postgresql.
我正在尝试编写一些查询来根据postgresql 中的每个月对事物进行分组。
Say we have a table "crimes" which has 2 columns "activity date"(timestamp without time zone) and "zipcode"(character varying(5)), how to query the number of crimes for each month given a zipcode?
假设我们有一个表“犯罪”，它有 2 列“活动日期”（没有时区的时间戳）和“邮政编码”（字符变化（5）），如何在给定邮政编码的情况下查询每个月的犯罪数量？
eg: table "crimes":
例如：表“罪行”：
activity date zipcode
2014-11-22 00:52:00 12345
2014-10-22 00:52:00 12345
2014-10-24 00:52:00 12345
2014-12-22 00:52:00 54321
input: given zipcode"12345"
输入：给定邮政编码“12345”
output: return
输出：返回
month count
2014-10 2
2014-11 1
回答by Jacob Lambert
Try:
尝试：
select
extract(year from activity_date) as year,
to_char(activity_date, 'Mon') as month,
count(*) as count
from
crimes
group by
1,extract(month from activity_date);
回答by Cornelius Roemer
How to get the month from a timestamp (with/out timezone) in PostgreSQL?
如何从 PostgreSQL 中的时间戳（带/不带时区）中获取月份？
Use the function EXTRACT(field FROM source)
使用函数EXTRACT(field FROM source)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2
Link to documentation: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
文档链接：https: //www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
