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..
There is a method strftime in SQLite, Generally group by will work with an Aggregate function like (count, sum, avg …).
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
SELECT COUNT(Id),strftime ('%H',timestamp) hour FROM T GROUP BY strftime ('%H',timestamp)