Re: [sqlite] tricky date time problem

[Dennis Cote]

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