Re: Aggregate records by time

[Alberto Pedroni]

Found a solution that seems working properly

SELECT
Sub1.timeInterval,
a1.date,
MIN(a1.time),
MAX(a1.high) AS high,
MIN(a1.low) as low,
MIN(a2.open) as open,
MAX(a3.close) as close
FROM atlantia a1
INNER JOIN
(
SELECT floor( (cast( SUBSTRING(time,1,2) AS SIGNED ) * 60 + cast( SUBSTRING(time,3,2) AS SIGNED )) /60 ) AS timeInterval, MIN(time) AS minTime, MAX(time) AS maxtime
FROM atlantia
GROUP BY timeInterval
) Sub1
ON floor( (cast( SUBSTRING(a1.time,1,2) AS SIGNED ) * 60 + cast( SUBSTRING(a1.time,3,2) AS SIGNED )) /60 ) = Sub1.timeInterval
INNER JOIN atlantia a2 ON a2.time = Sub1.minTime AND a1.date = a2.date
INNER JOIN atlantia a3 ON a3.time = Sub1.minTime AND a1.date = a3.date
GROUP BY a1.date , timeInterval