El 24/11/2011 14:49, 雷钦 escribió:
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))
Many thanks for the answers. That's exactly what I was looking for!
Just one final remark, the round() function groups minutes from, from
example, 3 to 7, but I really want to group minutes from 0 to 4. That
should be the work of the floor() function, but unfortunately it is not
part of the standard sqlite distribution (I have to learn this loadable
extensions mechanism). But I think that I can get the same result
performing a CAST(... AS INTEGER).
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d %H', date) as Date
FROM eurusd GROUP BY CAST(STRFTIME('%s', date) / (5 * 60) AS INTEGER);
Best regards
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
