Re: Aggregate intraday data

[Rick James]

Another thing... It looks like you are using CHAR for `date` and `time` -- that is (generally) a bad idea. It makes it very difficult to perform arithmetic, and may make it difficult to implement Peter's suggestions.

There are datatypes for DATE, TIME, and DATETIME.

Also, have you thought about what happens on short trading days?

Further, I suggest you do this aggregation every night for a single day's data, then put that into a 'summary' table. Why? Eventually, your table will be huge, and the query will have to hit the disk a lot, and performance will be bad. Such a summary table will be much faster for this particular query.