Don Goyette wrote:
> 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?
With 60*60*24 seconds per day, the number of days since the Unix epoch is:
sqlite> select strftime('%s', '2012-05-22') / (60*60*24);
15482
So with 41051 - 15482 = 25569, the conversion would be:
(excel_timestamp - 25569) * (60*60*24)
And indeed:
> select datetime((41051.3958333334 - 25569) * (60*60*24), 'unixepoch');
2012-05-22 09:30:00
> 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.
You cannot modify the table name of a DELETE statment from inside SQLite itself.
What you can do is to generate all the DELETE statements from a query:
select 'DELETE FROM ' || Tname || ' WHERE ...' from Tnames;
... redirect the output to a file, and then execute that file.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
