On 13/12/11 16:31, Rafael Garcia Leiva wrote:
>
> Yes, that works, even with missing data. The final query is:
>
> SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) AS Date,
> (SELECT open from eurusd e2
> where CAST(STRFTIME('%s', e2.date) AS INTEGER) >=
> CAST(STRFTIME('%s', e1.date) AS INTEGER) / (5 * 60) * 5 * 60
> order by e2.date asc limit 1) AS Open,
> MAX(high) as High,
> MIN(low) as Low,
> (SELECT close from eurusd e3
> where CAST(STRFTIME('%s', e3.date) AS INTEGER) <
> (CAST(STRFTIME('%s', e1.date) AS INTEGER) / (5 * 60) + 1) * 5 * 60
> order by e3.date desc limit 1) AS Close
> FROM eurusd e1
> GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)
>
> The problem is that is veeeeery slooooow. It takes nearly 24 hours to
> query 1 year of Forex data in my laptop (and I have to work with 10
> years periods). I will spend a couple of days learning about sqlite
> optimization.
>since much of that time will be spent in the strftime() calls and calculations, how about adding a lookup table with pre-computed date/times?
jr. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
