SQL select & group by time (timestamp)

Question

I want to select the quantity and counting group by the time period (month, week, day, hour, ...). So, for example, I want to select the number of rows and combine them for 24 hours.
My table is created as follows. Date is a 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 request. The length depends on the period of time. But how can I group 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?

Answer 1

You can group 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 the year
• DD = Day of the month
• WW = Week
• HH24 = Hour of the day

You can combine them to a large extent, but you like