I am trying to group my timestamp column which has a data type of TimeStamp by hour.
So this works for grouping by day:
x
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..
Advertisement
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)