On 24 Nov 2011, at 7:42am, Rafael Garcia Leiva wrote:
> CREATE TABLE eurusd (
> date TEXT NOT NULL PRIMARY KEY,
> open REAL NOT NULL,
> high REAL NOT NULL,
> low REAL NOT NULL,
> close REAL NOT NULL
> );
>
> The granularity of the data is one minute, for example:
>
> INSERT INTO eurusd (date, open, high, low, close) VALUES (
> '2011-11-01 00:01:00', '1.1212', '1.2323', '1.3434', '1.4545'
> );
>
> For the analysis of the data it is important to aggreate table rows using
> other timeframes. If I want to do analysis of data aggregated by months I can
> use the following query:
>
> SELECT MAX(high) AS High, MIN(low) as Low,
> STRFTIME("%Y-%m-%d", date) as Date
> FROM eurusd GROUP BY STRFTIME("%Y-%m-%d", date);
>
> In the same way I can aggregate the data by days and hours.
>
> The problem is that I have to aggregate and analyze the data with other less
> conventional time frames, like 5 minutes, 15 minutes, or even 23 minutes.
I recommend that you store the datestamp in a numeric form. You can do either
keep your existing column and add a new one, writing the data to both columns,
or replace the existing text datestamp.
Two easy-to-convert formats would be Julian Day and Unix Epoch. Julian Days
are floats where 1 = 1 dayr; Unix Epochs generated by SQLite are floats where 1
= 1 second. It appears that you're interested in sub-day units so the unix
format might be most useful for you.
See
<http://www.sqlite.org/lang_datefunc.html>
Your SELECT would be something like
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME("%Y-%m-%d", date) as Date
FROM eurusd GROUP BY round(timestamp / 23 * 60)
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
