[sqlite] Maximum number of precompiled queries
Hello all, Does anyone know if there is a maximum number of precompiled queries you can attach to a database? I only ask as some of my precompiled queries are not working (ie nothing happens to the database, but no errors are reported), however if I just execute the same SQL query, everything works as expected. It is only happening on one table at the moment, but I can't see anything different or incorrect and the operations I am attempting are very simple insert, select and delete queries. Thanks a confused jake :)
Re: [sqlite] Is this an in-memory database too
Cory Nelson wrote: nope, runs entirely off disk. On Wed, 6 Oct 2004 23:18:12 -0700, R S <[EMAIL PROTECTED]> wrote: I was trying to find info for the same, but couldn't. Do let me know. Ummm actually SQLite can be an in-memory database. Check this out: http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase jake
Re: [sqlite] some optimisation help?
Darren Duncan wrote:
At 6:10 PM +1030 3/19/04, Jake Skinner wrote:
The following query is very slow. I don't know how to improve my
query, and I'm sure I'm doing this just about the slowest way
possible!! :(
begin sql query
==
select t1.time, t1.units_processed,
round((t1.units_processed+t2.units_processed+t3.units_processed+t4.units_processed)/4)
as ave
from location_stats as t1,
location_stats as t2,
location_stats as t3,
location_stats as t4
where t1.time=t2.time
and t3.time=t4.time
and t4.time=t2.time
and t1.date=date("2004-02-24",'-7 days')
and t2.date=date("2004-02-24",'-14 days')
and t3.date=date("2004-02-24",'-28 days')
and t4.date=date("2004-02-24",'-35 days')
===
end sql query
I'm not quite sure if this is what you want, but it should be in the
right ball park:
SELECT time, AVG(units_processed) AS ave
FROM location_stats
WHERE date=DATE("2004-02-24",'-7 DAYS')
OR date=DATE("2004-02-24",'-14 DAYS')
OR date=DATE("2004-02-24",'-28 DAYS')
OR date=DATE("2004-02-24",'-35 DAYS')
GROUP BY time
If it is correct, then it should run a lot faster, as it is simpler.
If you were wanting to compare your number of units just this last
week to the average of the previous several weeks, then you may have
to take the whole expression I wrote and use it as a sub-query in the
FROM clause of a larger one that joins the results with a simpler
select just fetching the newer day's numbers.
Thanks for the help, the revised select runs about 10 times faster.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] some optimisation help?
The following query is very slow. I don't know how to improve my query,
and I'm sure I'm doing this just about the slowest way possible!! :(
begin sql query
==
select t1.time, t1.units_processed,
round((t1.units_processed+t2.units_processed+t3.units_processed+t4.units_processed)/4)
as ave
from location_stats as t1,
location_stats as t2,
location_stats as t3,
location_stats as t4
where t1.time=t2.time
and t3.time=t4.time
and t4.time=t2.time
and t1.date=date("2004-02-24",'-7 days')
and t2.date=date("2004-02-24",'-14 days')
and t3.date=date("2004-02-24",'-28 days')
and t4.date=date("2004-02-24",'-35 days')
===
end sql query
If anyone has any sort of suggestions I would be very greatful...
cheers
Jake
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time functions - expanding
D. Richard Hipp wrote:
D. Richard Hipp wrote:
Jake Skinner wrote:
Could anyone suggest other ways of achieving summing of times ie
sum(end_time - start_time). At the moment I have a rather unwieldy
sql query (see below).
sum(
(strftime("%H",end_time)*60+strftime("%M",end_time)) -
(strftime("%H",start_time)*60+strftime("%M",starttime)) )
sum(end_time - start_time)*1440
Actually, depending on what format you use to store your times,
you might need to do this:
sum(julianday(end_time)-julianday(start_time))*1440
The julianday() function will convert from whatever time format
you are using into the julian day number format that I assumed
you were using in my original email.
end_time and start_time are both in (fractional) days since
the julian epoch (-4713-11-24 12:00:00 UTC). The "*1440" at
the end converts from days into minutes. If you want an
integer number of minutes, do this:
round(sum(end_time-start_time)*1440)
I am so glad I asked the question! Thank you
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] time functions - expanding
What has been done with date and time functions is great.
I would like to suggest a further extension to these functions, at the
moment (I'm really only talking about the time functions however it
could also apply to the date functions as well) it is possible to select
either the hour or minute portion of the time via %H or %M, I would like
to be able to format the total time in minutes. ie 04:00 is 240 minutes,
for the purposes of summing.
I am unsure whether this is possible due to the typeless nature of
sqlite - which is a much bigger boon than some tiny obscure time\date
functions.
Could anyone suggest other ways of achieving summing of times ie
sum(end_time - start_time). At the moment I have a rather unwieldy sql
query (see below).
sum(
(strftime("%H",end_time)*60+strftime("%M",end_time))
-
(strftime("%H",start_time)*60+strftime("%M",starttime))
)
cheers
Jake
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Time functions
I am have declared a column in a table as 'text'. I am storing times as strings in this column in the format "HH:MM". I have "09:00" as a time - meant to be representing 9 AM. I would like to retrieve the hour, which I attempt with "hour(time_col_name)", what is returned is "21" - I am expecting "9" I have tried three different SQLite browsers (sqliteCC, SQLiteDBBrowser and sqliteplus) to check my sql. All three of these return "21". In func.c, 'getDigits' returns the expected value. ie val = 9, so how does it get changed to 21? Thanks Jake - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Problem with time
Hello, I seem to be having some problems with time. I am storing "17:30" into SQLite. When I do a 'select time(time_col) from table' I am receiving "05:29:59". Being one second out, I can kind of live with but, not returning the correct 24 hour format I can't. I'm sure I'm doing something wrong, however I am completely stumped as to what! Any ideas? Cheers Jake - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] DATEADD?
This is a MySQL function, is there an equivalent? I couldn't find anything that resembles it. I expect that it is a MySQL specific thing but I thought I would ask people to be sure. Thanks Jake - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] list problem?
I don't know - seems to be ok? jake - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
