Group timestamp by hour SQLite

I am trying to group my timestamp column which has a data type of TimeStamp by hour.

So this works for grouping by day:

SELECT * FROM INFO
GROUP BY
DATE(timestamp)

I have tried the following for grouping by hour:

SELECT * FROM INFO
GROUP BY
HOUR(timestamp)

But this gives me the error: no such function: HOUR:

I have seen similar questions asked, but it was DateTime used, not TimeStamp.

Additionally I would like to know how to group by any time period e.g by 24 hr, by 48 hr, by week, etc..

Answer

There is a method strftime in SQLite, Generally group by will work with an Aggregate function like (count, sum, avg …).

TestDLL

CREATE TABLE T(
  Id INT,
  `timestamp` timestamp
);

insert into t values (1,'2018-01-01 01:01:12');
insert into t values (2,'2018-01-01 02:02:12');
insert into t values (3,'2018-01-01 01:01:12');

Here is a sample for strftime method.

Query

SELECT COUNT(Id),strftime ('%H',timestamp) hour
FROM T
GROUP BY strftime ('%H',timestamp)

sqlfiddle