>
> You could be storing event duration, not stop time. Or perhaps store
> both.
>
Here is what I have so far:
sqlite> create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name, kind,
start, end);
# Now add some events for "tomorrow"
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'));
# Now add an all-day event for tomorrow. (It overlaps a couple of above
events.)
sqlite> insert into events values (null,
'tom', 'day', datetime('now', '+1 day','start of day'), datetime('now',
'+1 day','start of day', '+1 day'));
# Show all events and duration:
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 now, I want to get a result table with one row per user showing the
"shortest active at 9:30 AM event" for each user.
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;
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>
However this result returned is very wrong. The length col is correct but
the other cols for "tom" are wrong. (It should be "2 | tom | hour | 2009-06-13
09:00:00 | 2009-06-13 10:00:00 | 3600" )
What am I missing here? Am I doing the query wrong?
Thank you,
:)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
