How to group records by interval in sqlite?

Question

SQLite

There is an author and timestamp column in the table. I need to group records (get their number) by author with an interval of 3 hours.
For example:
User 1: 10 entries
User 2: 1 entries
User 3: 11 entries
User 1: 3 entries
User 3: 5 entries

Answer 1

https://www.sqlite.org/lang_datefunc.html
Using stftime you extract the year, month, day and hour separately. Hours are grouped by 3 pieces. How to do it - you know better. For example, divide the hour by 3 (which is what sqlite does by default with two integers). It remains to make a request of the form

SELECT 
  strftime('%Y', timestamp) as year, 
  strftime('%m', timestamp) as month, 
  strftime('%d', timestamp) as day, 
  strftime('%H', timestamp) / 3 as grouped_hour, 
  author, 
  COUNT(*) 
FROM table 
GROUP BY year, month, day, grouped_hour, author

PS counting in grouped_hour starts from 0. 0 corresponds to the interval 00:00:00 - 02:59:59, 1 - 03:00:00 - 05:59:59, etc.