SQL select & group by a period of time (timestamp)
33,071
You can group by the TO_CHAR function.
select to_char(msg_date, 'X')
,count(*)
from msg
group
by to_char(msg_date, 'X')
...where X is the format specification:
- YYYY = 4-digit Year
- MM = Month of year
- DD = Day in month
- WW = Week
- HH24 = Hour of day
You can combine them pretty much however you like
Author by
Raymond Chenon
This is me with Joel Spolsky in Berlin. http://betterapps.io/ portfolio http://www.linkedin.com/in/raychenon https://blog.raychenon.com https://github.com/raychenon
Updated on July 18, 2022Comments
-
Raymond Chenon almost 2 years
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.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 ?