Menu
I use the sql below in sqlite to group by time intervals. The first is group by day and the second group by hour:
select strftime('%Y-%m-%dT%00:00:00.000', date_time),line, count() from entry group by strftime('%Y-%m-%dT%00:00:00.000', date_time)
select strftime('%Y-%m-%dT%H:00:00.000', date_time),line, count() from entry group by strftime('%Y-%m-%dT%H:00:00.000', date_time)
How do I group by 10 minutes interval
544
The SQLite GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The GROUP BY clause a selected group of rows into summary rows by values of one or more columns. The GROUP BY clause returns one row for each group. For each group, you can apply an aggregate function such as MIN , MAX , SUM , COUNT , or AVG to provide more information about each group.
SQLite GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
select ... from entry
group by strftime('%Y%m%d%H0', date_time) + strftime('%M', date_time)/10;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
