Thank you for your reply and suggestions, Clemens.
> 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
The timestamp in the tables I'm reading is not in the format of
'2012-05-22'. It's in the Excel format (ie. 41051.3958333334), which is why
I need to convert it for use with SQLite. When I read a row of data, I do
not get "2012-05-22", I get a value such as "41051.3958333334". So, I am
not able to perform the strftime() function you suggest, until I convert the
Excel timestamp to a Unix epoch timestamp. For now, I'm still hard-coding
the value I need.
> ... redirect the output to a file, and then execute that file.
Great idea! I had not thought of copying the output from the Tnames table
to a text file and simply adding the DELETE query text around the table
names. Then copying it back into the SQL GUI that I use and execute 10 or
so DELETE queries at a time.
So, I still need to know how to convert the Excel format timestamp (Days
since 1900-01-01) into a Unix Epoch format timestamp (Seconds since
1970-01-01).
Thank you all,
-Don
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Clemens Ladisch
Sent: Sunday, September 23, 2012 6:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DELETE Query Assistance Please
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
