Re: Aggregate intraday data
Rick James Wrote:
-------------------------------------------------------
> 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.
Hi Rick,
creating separate daily tables was a solution, maybe the best solution for the reasons you just mentioned, but this would introduce some other problems;
1) I should not only create daily tables, but weekly and monthly too: I have now 54 intraday tables, and this way I will come to have 54*4 = 216 tables
2) If I should have a data error in an intraday data table, this error will be also in daily, weekly and monthly related tables.
3) I should have to run every night a query to update the D, W and M tables
4) I will also implement in my JavaFX 2 project the DDE live data feed, and I should keep 216 tables update in real time
For what about short trading days (I think you mean when a trading day session ends before usual closing time), this is not a problem since if market close before (let's suppose it close @ 1600 instead close @ 1730), this is the last trading time so I consider 1600 as my daily close.