>
> One table of the events with fields you need (eg description, start and
> end, repeating rule). A second table with the exceptions, or depending
> on how much you want to normalize a table per exception type.
>
> > Where is this calculation being done? In SQL? At the app level? How?
>
> You have to do the calculation at the application level. There are no
> standard SQL functions for this kind of stuff.
>
> > Is there an SQL query that can retrieve a list of events for a certain
> > time,
> or this not doable sanely in SQL?
>
> The combination of multiple repeating events plus their exceptions is
> not something that SQL remotely supports nor could be expressed in SQL*.
> Quite simply you are going to have to do the hard work yourself.
>
>
I see. This is what I feared.
One other question:
I will likely cache some of the final calculations to the DB for certain time
periods. (Because the schedule items, and their other linked data, will be
queried quite a lot)
Given that:
1) We will have a table of events with start and stop "datetime() timestamp"
columns.
2) Every user can have events that overlap. (a day long & a 2 hours morning
appt.)
What is the query to generate a table that lists each user and the single "most
specific" appointment in effect for the current moment. (The one event with
the shortest duration that is active right now.)
I tried:
sqlite> create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name, kind,
start, end);
sqlite> insert into events values (null, 'tom', 'hour', datetime('now', '+1
day','start of day', '+11 hours'), datetime('now', '+1 day','start of day',
'+12 hours'));
sqlite> insert into events values (null, 'tom', 'hour', datetime('now', '+1
day','start of day', '+9 hours'), datetime('now', '+1 day','start of day', '+10
hours'));
sqlite> insert into events values (null, 'joe', 'hour', datetime('now', '+1
day','start of day', '+9 hours'), datetime('now', '+1 day','start of day', '+10
hours'));
sqlite> insert into events values (null, 'tom', 'day', datetime('now', '+1
day','start of day'), datetime('now', '+1 day','start of day', '+1 day'));
sqlite> select *, (strftime('%s', end) - strftime('%s', start)) as length from
events;
id name kind start
end length
-------------------- -------------------- --------------------
-------------------- -------------------- ----------
1 tom hour 2009-06-13
11:00:00 2009-06-13 12:00:00 3600
2 tom hour 2009-06-13
09:00:00 2009-06-13 10:00:00 3600
3 joe hour 2009-06-13
09:00:00 2009-06-13 10:00:00 3600
4 tom day 2009-06-13
00:00:00 2009-06-14 00:00:00 86400
.... and then, wanting to get the most specific appointments for tomorrow at
9:30 AM, I tried:
sqlite> select *, min((strftime('%s', end) - strftime('%s', start))) as length
from events where start < datetime('now', '+1 day','start of day', '+9
hours','+30 minutes') and end > datetime('now', '+1 day','start of day', '+9
hours','+30 minutes') group by name;
.... but got back:
id name kind start
end length
-------------------- -------------------- --------------------
-------------------- -------------------- ----------
3 joe hour 2009-06-13
09:00:00 2009-06-13 10:00:00 3600
4 tom day 2009-06-13
00:00:00 2009-06-14 00:00:00 3600
sqlite>
I was hoping to get an "id" of 2 in the "tom" row, as that is the most specific
appt's ID.
What am I missing here?
Thank you,
AF
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
