On 2011-11-24 08:02:21 +0000, Simon Slavin wrote:
>
> 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)
I think it is the same as
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d',date) as Date
FROM eurusd GROUP BY round(STRFTIME('%s',date) / (23 * 60))
>
> Simon.
> _______________________________________________
> 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
