SQL 按日期分组（小时）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5111396/
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
SQL group by date (hour)
提问by kal
I have a Table with a "Date" Column. I want to group by hour for a specific date.
我有一个带有“日期”列的表。我想按小时分组特定日期。
回答by Jeffrey Kemp
You can also do this:
你也可以这样做：
SELECT TRUNC(datecol, 'HH24') FROM mytable
GROUP BY TRUNC(datecol, 'HH24');
回答by rene
Select TO_CHAR(date,'HH24')
from table
where date = TO_DATE('20110224', 'YYYYMMDD')
group by TO_CHAR(date,'HH24')
回答by RichardTheKiwi
select to_char(datecol,'HH24') thehour, count(*) count_in_hour
from tbl
where datecol = date '20110224'
group by to_char(datecol,'HH24')
order by thehour asc
回答by fheub
Alternatively, I would have suggested EXTRACT (datetime), but (empahsis mine):
或者，我会建议EXTRACT (datetime)，但是（强调我的）：
If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
如果请求 HOUR、MINUTE 或 SECOND，则 expr 必须计算为数据类型为 TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE 或 INTERVAL DAY TO SECOND 的表达式。DATE 在这里无效，因为 Oracle 数据库将其视为没有时间字段的 ANSI DATE 数据类型。
Therefore, the result is a little bit ugly:
因此，结果有点难看：
SELECT EXTRACT (HOUR FROM CAST (m.a_date AS TIMESTAMP)) AS hour_,
COUNT (*) AS count_
FROM MY_TABLE m
GROUP BY EXTRACT (HOUR FROM CAST (m.a_date AS TIMESTAMP));
