MySQL SELECT all rows between date time with interval

Question

I have a column in my sql table called loggedTime which is a datetime field and I want to select between two dates startDate and endDate along with the interval may be 5 minutes, 10 minutes, 1 hour etc. I tried to write the SQL query but it says You have syntax error next interval, I am not sure what wrong with my query. If I remove INTERVAL 5 MINUTE my query works fine but I want to pass the Interval along with the date so it will select all rows between two dates and also with interval

Here is SQL

SELECT * FROM mytable WHERE loggedTime BETWEEN '2021-06-01' and '2021-06-03' INTERVAL 5 MINUTE

Does this answer your question? [MySQL: select query, 5 minute increment](https://stackoverflow.com/questions/10403039/mysql-select-query-5-minute-increment) — AcidResin, Jun 22 '21 at 12:38
What do you mean with the interval maybe 5 minutes,..? In your query you are using date, not time. With all respect, I think you have miss-understood INTERVAL usage. — alirakiyan, Jun 22 '21 at 13:14

score 1 · Accepted Answer · edited May 14 '22 at 21:13

1

If you have any unique consecutively increasing column like id, then you can use an INNER JOIN as done followingly:

SELECT *
FROM       mytable a
INNER JOIN mytable b 
        ON a.ID = b.ID + 1
WHERE TIMESTAMPDIFF(minute, a.timestamp, b.timestamp) = 5;

If you do not have that column in your table then use this code :

SELECT * 
FROM (SELECT mt.*,
             TIMESTAMPDIFF(minute, @prevTS, `loggedTime`) AS timeinterval,
             @prevTS:=mt.`loggedTime`
      FROM mytable mt, 
           (SELECT @prevTS := (SELECT MIN(`loggedTime`) 
                               FROM yourTable)) vars
      ORDER BY ID)subquery_alias
WHERE loggedTime BETWEEN '2021-06-01' AND '2021-06-03' 
  AND timeinterval = 5

Check this thread as reference too.

edited May 14 '22 at 21:13

lemon

14,875
6
18
38

answered Jun 22 '21 at 13:12

Syed Ahmad

82
3

Your answer does not have anything to do with INTERVAL keyword, which clearly is what questioner wants to read about. – alirakiyan Jun 22 '21 at 13:18
@Syed Ahmed, it works fine but the only issue is that BETWEEN ending date isn't getting results from todays date i.e. 2021-06-22 but if I use 2021-06-23 it works can you help me with that? – Brajman Jun 22 '21 at 14:34

MySQL SELECT all rows between date time with interval

1 Answers1