I have added a new column with the date in seconds, created an index
over this column and modified the query a little bit. Now the query
takes less than 20 seconds. Since this is the worst case I'm happy with
the result.
Just for reference, the final query is:
SELECT STRFTIME('%Y-%m-%d %H %
El 13/12/2011 17:44, Igor Tandetnik escribió:
On 12/13/2011 11:31 AM, Rafael Garcia Leiva wrote:
The problem is that is very slow. 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
On 13 Dec 2011, at 5:38pm, jr wrote:
> 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?
Actually, with this much use of the date/time information I'd be storing it all
as numbers. And if you're wedd
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
behalf of Rafael Garcia Leiva [rafael.gar...@entropycs.com]
Sent: Tuesday, December 13, 2011 10:31 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Aggregating Forex data
Yes, that works, even with missing data. The final query is:
SELECT STRFTIME('%Y-%m-%d %H %M'
On 12/13/2011 11:31 AM, Rafael Garcia Leiva wrote:
The problem is that is very slow. 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.
I suspect these two days
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
El 12/12/2011 17:13, Igor Tandetnik escribió:
On 12/12/2011 9:45 AM, Rafael Garcia Leiva wrote:
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.
Something like thi
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
On 12/12/2011 9:45 AM, Rafael Garcia Leiva wrote:
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.
Something like this:
select strftime('%Y-%m-%d %H:%M', min(date)
El 24/11/2011 19:18, Rafael Garcia Leiva escribió:
El 24/11/2011 14:49, 雷钦 escribió:
On 2011-11-24 08:02:21 +, 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 NO
On 24 Nov 2011, at 6:18pm, Rafael Garcia Leiva wrote:
>> 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))
>
> Many thanks for the answers. That's exactly what I was looking for!
>
> Just one fina
El 24/11/2011 14:49, 雷钦 escribió:
On 2011-11-24 08:02:21 +, 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,
lowREAL NOT NULL,
close REAL NO
On 2011-11-24 08:02:21 +, 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,
> >lowREAL NOT NULL,
> >close REAL NOT NULL
> > )
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,
>lowREAL NOT NULL,
>close REAL NOT NULL
> );
>
> The granularity of the data is one minute, for example:
>
> IN
Dear all,
This is my first post to this mailing list, I hope it is a relevant
question.
I have a table with Forex (currency exchange) data with the following
schema:
CREATE TABLE eurusd (
date TEXT NOT NULL PRIMARY KEY,
open REAL NOT NULL,
high REAL NOT NULL,
lowRE
16 matches
Mail list logo
