SQL select & group by a period of time (timestamp)

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 ?

Answers


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


Need Your Help

Go To Definition: "Cannot navigate to the symbol under the caret."

c# visual-studio visual-studio-2015

I suddenly started experiencing a problem today in my project where clicking Go To Definition gives me a strange error: "Cannot navigate to the symbol under the caret."

Why doesn't a compiler optimize floating-point *2 into an exponent increment?

c++ c performance optimization compiler-optimization

I've often noticed gcc converting multiplications into shifts in the executable. Something similar might happen when multiplying an int and a float. For example, 2 * f, might simply increment the