-1

Im using mysql database. I'm having data of each second in a day.So i need to get data for every 5 minutes.But actually I'm getting every 5 minutes data from database between selected Date and time by using this query

select * from tablename where DateTime between '23-01-2018 00:00:00' and '23-01-2018 23:59:59' and MINUTE(DateTime) % "+min+" = 0 order by DateTime"

But the problem is Im retriving data in this way

 22-03-2018 11:05:01, 
 22-03-2018 11:05:02, 
 22-03-2018 11:05:03,
 22-03-2018 11:05:04, 
 22-03-2018 11:05:05, 
 22-03-2018 11:05:06,
 22-03-2018 11:05:07, 
 22-03-2018 11:05:08, 
 22-03-2018 11:05:09

Above datetimes are 5th minute data but again each second data im getting for 5th minute .

I want only 1 record for 5th minute.

I tried Using Group by but it didnt worked for me. Please any one help me.

Thanks in advance.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Badsha
  • 25
  • 1
  • 5
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Mar 23 '18 at 07:15
  • Refer https://stackoverflow.com/questions/10403039/mysql-select-query-5-minute-increment – DAIRAV Mar 23 '18 at 07:17

1 Answers1

0

if you need just a record each 5 minutes you should use an aggregation function (eg : min() ) and group by for every 5 minutes

select min(DateTime) as DateTime
from tablename 
where DateTime between '23-01-2018 00:00:00' and '23-01-2018 23:59:59' 
GROUP BY 
UNIX_TIMESTAMP(DateTime) DIV 300 
order By DateTime
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107