Re: [sqlite] tricky date time problem

[Lloyd Thomas]

Dennis, syntax error somewhere.
'group by minute union  select 0, 0 where not exists (select * from 
event_data)'

Lloyd
----- Original Message ----- 
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, February 24, 2005 1:31 AM
Subject: Re: [sqlite] tricky date time problem


On Wed, 23 Feb 2005 23:12:41 -0000, Lloyd Thomas
<[EMAIL PROTECTED]> wrote:
Dennis,
        Thanks for you help so far. I think it is easier for PHP to 
select
the MAX event. The problem I now have is if there is no records for an 
hour,
PHP will through up an error because MAX must have at least one record to
process, even if it is 0.

Lloyd,
You can force a zero record if there no events using a union like this:
select minutes.i as minute, count(*) as events
from
   (select (event_time / 60) % 60 as begin_minute,
   ((event_time + duration) / 60) % 60 as end_minute
   from event_data)
outer join integers60 as minutes
where
   case
       when begin_minute <= end_minute then
          begin_minute <= minutes.i and minutes.i <= end_minute
       else
         begin_minute <= minutes.i or minutes.i <= end_minute
   end
group by minute
union
select 0, 0 where not exists (select * from event_data)
This will give a single row with a count of zero if there are no
events, or the usual set of 60 rows if there are one or more events.
Dennis Cote