Why you need to convert? What about the simple SQL I suggested? RBS
On 9/24/12, Don Goyette <[email protected]> wrote: > > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
