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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to