Problem 1 seems simple: delete from TableX where timestamp < 41115 I think the table name may need to be produced by code.
RBS On Sun, Sep 23, 2012 at 12:06 PM, Don Goyette <d...@donandcarla.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
