C. Seon C. Seon - 4 months ago 21
SQL Question

how to output in 5-minute interval?(mysql )

I want to output a time interval.

Stores at one minute intervals.

query is:

select datetime + INTERVAL 5 MINUTE
from ch1
where datetime between '2016-07-12 17:26:00' and '2016-07-13 09:17:00'


but output is

2016-07-12 17:31:00
2016-07-12 17:32:00
2016-07-12 17:33:00
2016-07-12 17:34:00
2016-07-12 17:35:00
2016-07-12 17:36:00 ...


I want to output.

2016-07-12 17:26:00
2016-07-12 17:31:00
2016-07-12 17:36:00
2016-07-12 17:41:00
2016-07-12 17:46:00
2016-07-12 17:51:00 ...

Answer

It appears that your ch1 table already has the timestamp data you want. If this be the case, then you can simply choose records from every 5 minutes by using the MINUTE() function along with a modulus:

SELECT datetime + INTERVAL 5 MINUTE
FROM ch1
WHERE datetime BETWEEN '2016-07-12 17:26:00' AND '2016-07-13 09:17:00' AND
      MINUTE(datetime) % 5 = 0

Actually, if you want 2006-07-12 17:26:00 to be the first record in the result set, then you can offset the modulus calculation:

SELECT datetime + INTERVAL 5 MINUTE
FROM ch1
WHERE datetime BETWEEN '2016-07-12 17:26:00' AND '2016-07-13 09:17:00' AND
      (MINUTE(datetime) - 1) % 5 = 0