Re: [sqlite] DELETE Query Assistance Please

[Don Goyette]

Thank you all for your suggestions.  I've created a set of manual steps to
create the 1000 table delete query that only took me 10 minutes to
accomplish.  A whole lot simpler than trying to figure out how to automate
this entire 1000 table process in SQLite.

 

-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

Re: [sqlite] DELETE Query Assistance Please

[E. Timothy Uy]

If you are trying to purge a huge database and it is not live, another
possibility is to attach a new blank database and SELECT into it. This way
you don't have to waste time with VACUUM.

On Mon, Sep 24, 2012 at 10:04 AM, Clemens Ladisch wrote:

> Don Goyette wrote:
> > > 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'.
>
> Sorry, my explanations were not clear enough.  This is not yet the
> conversion; here I just determined the number of days between the Unix
> epoch and your example timestamp.
>
> That was used to derive the actual conversion formula that you wanted:
>
> > >   (excel_timestamp - 25569) * (60*60*24)
>
> And this was just a check of the formula, using your example timestamp:
>
> > >   > select datetime((41051.395834 - 25569) * (60*60*24),
> 'unixepoch');
> > >   2012-05-22 09:30:00
>
>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] DELETE Query Assistance Please

[Clemens Ladisch]

Don Goyette wrote:
> > 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'.

Sorry, my explanations were not clear enough.  This is not yet the
conversion; here I just determined the number of days between the Unix
epoch and your example timestamp.

That was used to derive the actual conversion formula that you wanted:

> >   (excel_timestamp - 25569) * (60*60*24)

And this was just a check of the formula, using your example timestamp:

> >   > select datetime((41051.395834 - 25569) * (60*60*24), 'unixepoch');
> >   2012-05-22 09:30:00


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE Query Assistance Please

[Gerry Snyder]

On 9/24/2012 9:25 AM, Don Goyette wrote:



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).



I agree with Bart's reply, but to convert epochs, subtract the Excel 
format timestamp of
1970-01-01 (easily found in excel), and to convert units multiply the 
result by seconds per day (86400).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE Query Assistance Please

[Bart Smissaert]

Why you need to convert?
What about the simple SQL I suggested?

RBS


On 9/24/12, Don Goyette <d...@donandcarla.com> 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.395834), 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.395834".  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: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] 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.395834'
> (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.395834 - 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
>
> ___
> 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

Re: [sqlite] DELETE Query Assistance Please

[Don Goyette]

 
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.395834), 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.395834".  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: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] 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.395834'
(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.395834 - 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE Query Assistance Please

[Clemens Ladisch]

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.395834' (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.395834 - 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

Re: [sqlite] DELETE Query Assistance Please

[Bart Smissaert]

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  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 '_intraday'
>
>   where  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
>
>   volumeREAL
>
>
>
> 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.395834' (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 
>
>  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