Hello Everyone,
I'm using what has become a huge (3.5 GB) SQLite3 database that was created
by an investment (stocks) tracking program I use. The program does not have
a database purge function to delete old data, and it's no longer supported.
So I'm trying to do this manually with SQL, via a Windows program called
RazorSQL.
The database contains 1,034 tables, one for each stock ticker being tracked.
Each table contains up to 60,000 rows. These tables store historical
intraday stock prices at five minute intervals, so there are about 150 rows
for each day that is stored.
Some of the tables contain up to 388 days of data, but I only need 60 days.
Thus, a LOT of data needs to be deleted from these tables and then the
database needs to be compacted.
The database file name is 'Don.db'
The history tables are named '<ticker>_intraday'
where <ticker> is a stock ticker, such as 'EMC' or 'ORCL'
The history table columns are...
timestamp REAL NOT NULL (Primary Key)
open REAL
high REAL
low REAL
close REAL
volume REAL
The table of table names is 'Tnames' (created during run)
The only column in Tnames is 'Tname' (names of all the intraday tables)
The first problem I'm running into is that the timestamp in these tables is
NOT a standard Unix timestamp. Rather, it's an Excel timestamp, which is
the number of Days since Jan 1, 1900. An example is '41051.3958333334' (May
22, 2012), but the DELETE query will only use the integer portion.
QUESTION #1: How do I convert this Excel timestamp value into a Unix
timestamp value that SQLite understands and can work with?
Next, I've managed to create a table of table names to be worked on (Tnames)
and have gotten a DELETE query running for a single table, with the table
name hard-coded into the query (ie. 'EMC_intraday'). But I've not been able
to figure out how to accomplish the DELETE query for ALL of the history
table names in Tnames. And I've Googled until my hands and eyes are just
plain sore.
Here's what I'm trying to do...
========================================================
-- Create a table of table names to work on (this works)...
CREATE TABLE Tnames (
Tname nvarchar(50) );
INSERT INTO Tnames --(this works)
SELECT name
FROM sqlite_master
WHERE type='table' and name LIKE '%intraday';
-- BEGIN processing all results
-- Delete rows > 60 days old (this does not work)...
-- Convert the Excel timestamp to a Unix timestamp...
-- Not sure how to do this
-- DELETE FROM 'Don.db'||Tnames does not work
-- DELETE FROM Don.db.Tnames.Tname does not work
-- DELETE FROM Tnames.Tname does not work
-- What I want to do is...
DELETE FROM <tablename entry in Tnames table>
WHERE timestamp < 41109; --(need to convert this)
-- END processing all results
-- Compact the database...
VACUUM
========================================================
QUESTION #2: How do I code the DELETE query so it will use each of the Tname
values in the Tnames table?
Thank you all, in advance, for your assistance. I appreciate your help very
much.
-Don
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
