按 Postgresql 日期时间分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17183977/
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
Group by on Postgresql Date Time
提问by IConfused
Hy. There are employee records in my postgresql database something like
嗨。我的 postgresql 数据库中有员工记录，例如
CODE DATE COUNT
"3443" "2009-04-02" 3
"3444" "2009-04-06" 1
"3443" "2009-04-06" 1
"3443" "2009-04-07" 7
I want to use a query "SELECT ALL CODES AND COUNT THEM THAT OCCURRED IN THE MONTH"
我想使用查询“SELECT ALL CODES AND COUNT THEM THAT OCCURRED IN THE MONTH”
RESULT:
结果：
CODE DATE COUNT
"3443" "2009-04" 3
"3441" "2009-04" 13
"3442" "2009-04" 11
"3445" "2009-04" 72
I did use a query i.e.
我确实使用了一个查询，即
SELECT CODE,date_part('month',DATE),count(CODE)
FROM employee
where
group by CODE,DATE
The above query runs fine but the months listed in the records are in form of numbers and its hard to find that a month belongs to which year. In short I want to get the result just like mention above in the RESULT section. Thanks
上面的查询运行良好，但记录中列出的月份是数字形式，很难找到月份属于哪一年。简而言之，我想得到结果，就像上面在 RESULT 部分中提到的那样。谢谢
回答by Szymon Lipiński
Try this:
试试这个：
SELECT CODE, to_char(DATE, 'YYYY-MM'), count(CODE)
FROM employee
where
group by CODE, to_char(DATE, 'YYYY-MM')
回答by Jimmy Stenke
Depending on whether you want the result as text or a date, you can also write it like this:
根据你想要结果是文本还是日期，你也可以这样写：
SELECT CODE, date_trunc('month', DATE), COUNT(*)
FROM employee
GROUP BY CODE, date_trunc('month', DATE);
Which in your example would return this, with DATE still a timestamp, which can be useful if you are going to do further calculations on it since no conversions are necessary:
在您的示例中哪个会返回这个，DATE 仍然是一个时间戳，如果您要对其进行进一步计算，这可能很有用，因为不需要转换：
CODE DATE COUNT
"3443" "2009-04-01" 3
"3441" "2009-04-01" 13
"3442" "2009-04-01" 11
"3445" "2009-04-01" 72
date_trunc()also accepts other values, for instance quarter, yearetc.
See the documentationfor all values
date_trunc()也接受其他的值，例如quarter，year等看到文档的所有值
回答by Martin Strejc
Try any of
尝试任何
SELECT CODE,count(CODE),
DATE as date_normal,
date_part('year', DATE) as year,
date_part('month', DATE) as month,
to_timestamp(
date_part('year', DATE)::text
|| date_part('month', DATE)::text, 'YYYYMM')
as date_month
FROM employee
where
group by CODE,DATE;
