SQLite get data within X minutes range

user_7164581 0
Viewed 132

I am creating an application which is using SQLite to store messages from users which has the following format.

uuid | date | message

Where uuid is an INTEGER, date is a DATETIME and message is just a string. How the problem that I am curious about is... I have multiple messages sent at the same period of time, for example at 2020-11-30 02:00:01 were send 2 messages, at 2020-11-30 02:00:05 another 5, and 2020-11-30 02:00:50 another 10.

How can I make a query that is going to return me a list of all the dates which are have the time delta as X minutes?

For example, let's say I have the falling list of data in my database: [2020-11-30 02:00:01, 2020-11-30 02:00:11, 2020-11-30 02:01:51, 2020-11-30 02:01:40, 2020-11-30 02:00:32] and my time delta is 1 minutes, the output that I want to have will be [2020-11-30 02:00:01, 2020-11-30 02:01:01] or if I will have my time delta as 5 minutes then the output will be just [2020-11-30 02:00:01]

I'm not sure if my question is clear enough so if it please let me know how can I do such a query, if is not, please tell me that and I will try to explain it again.

sqlite sql
1 Answers

You can do it with a recursive CTE:

WITH cte(date) AS (
  SELECT MIN(date) FROM tablename
  UNION ALL
  SELECT datetime(date, '+1 minute')
  FROM cte
  WHERE datetime(date, '+1 minute') < (SELECT MAX(date) FROM tablename)
)
SELECT date FROM cte

Or if you want to apply this logic to a custom list of datetimes:

WITH 
  list(date) AS (
    VALUES ('2020-11-30 02:00:01'), ('2020-11-30 02:00:11'), ('2020-11-30 02:01:51'), 
           ('2020-11-30 02:01:40'), ('2020-11-30 02:00:32')
  ),
  cte(date) AS (
    SELECT MIN(date) FROM list
    UNION ALL
    SELECT datetime(date, '+1 minute')
    FROM cte
    WHERE datetime(date, '+1 minute') < (SELECT MAX(date) FROM list)
  )
SELECT date FROM cte

See the demo.

user_104988280
Related