SQL 按时间段（时间戳）选择和分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5297372/
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 select & group by a period of time (timestamp)
提问by Raymond Chenon
I want to select the count number and group by a period of time (month, week, day, hour , ...) . So for example I want to select the number of rows and group them by 24h.
My table is created as follow. The date is timestamp.
我想按时间段（月，周，日，小时，...）选择计数和分组。例如，我想选择行数并将它们按 24 小时分组。
我的表创建如下。日期是时间戳。
CREATE TABLE MSG
(
MSG_ID decimal(22) PRIMARY KEY NOT NULL,
MSG_DATE timestamp,
FILE_REF varchar2(32),
FILE_NAME varchar2(64),
MSG_TYPE varchar2(2),
);
CREATE UNIQUE INDEX PK_FEI_MSG ON MSG(MSG_ID);
I tried with this query. With length depending on the time period. But how can I group from now .
我试过这个查询。长度取决于时间段。但是我怎么能从现在开始分组。
SELECT substr(MSG_DATE, 1,length),COUNT(*) as total FROM MSG GROUP BY substr(MSG_DATE, 1, length)
But how can I group the date from now + time_period ?
但是如何将日期从 now + time_period 分组？
回答by Ronnis
You can group by the TO_CHAR function.
您可以按 TO_CHAR 函数进行分组。
select to_char(msg_date, 'X')
,count(*)
from msg
group
by to_char(msg_date, 'X')
...where X is the format specification:
...其中 X 是格式规范：
- YYYY = 4-digit Year
- MM = Month of year
- DD = Day in month
- WW = Week
- HH24 = Hour of day
- YYYY = 4 位数字年份
- MM = 一年中的月份
- DD = 月中的第几天
- WW = 周
- HH24 = 一天中的一个小时
You can combine them pretty much however you like
你可以随意组合它们
