I think this can work
SELECT STRFTIME('%Y-%m-%d %H %M', date) AS Date,
(SELECT open from eurusd e2
where STRFTIME(e2.date) >= CAST(STRFTIME('%s', e1.date) /
(5 * 60) AS INTEGER) * 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 STRFTIME(e3.date) < (CAST(STRFTIME('%s', e1.date) /
(5 * 60) AS INTEGER) + 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)
On 2011-12-12 15:45:41 +0100, Rafael Garcia Leiva wrote:
> El 24/11/2011 19:18, Rafael Garcia Leiva escribió:
>
> Dear all,
>
> I'm still working on this problem :-(
>
> Now I need the Open and the Close of the 5 minutes interval, where
> Open is the Open of the first minute of the interval, and Close is
> the Close of the last minute of the interval. I know how to get that
> information with individual queries, for example:
>
> SELECT open, date FROM eurusd WHERE date IN
> (select min(date) from eurusd group by STRFTIME("%Y-%m-%d", date));
>
> But it would be very nice (and perhaps more computationally
> efficient?) to get all the information, that is Open, High, Low and
> Close in just one single query. I have tried something like:
>
> SELECT STRFTIME('%Y-%m-%d %H %M', date) AS Date,
> (SELECT open from eurusd e2 where e2.date = MIN(e1.date)) AS Open,
> MAX(high) as High,
> MIN(low) as Low,
> (SELECT close from eurusd e3 where e3.date = MAX(e1.date)) AS Close
> FROM eurusd e1
> GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)
>
> but I have got an error of "misuse of aggregate function MIN()".
>
> Any help would be very welcome. Also I would like to apologize if
> this question is not relevant to the sqlite mailing list.
>
> Best regards
>
> Rafael
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
