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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users