When i run it, it works as intended (on pg 7.3.3). Which version do you use?
Are you absolutely sure you copied it exactly? You typed in '>=' and not '=', right?
/Mattias
----- Original Message -----
From: "Bj�rn T Johansen" <[EMAIL PROTECTED]>
To: "Mattias Kregert" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 11, 2003 3:02 PM
Subject: Re: [GENERAL] I need a SQL...
> Well, it's close... :)
>
> But it looks like the case doesn't work..
> If I run your sql, the timediff is negative.
>
> But if I run this:
> SELECT (stoptime-starttime+'24 hours') as timediff FROM mytable
> the timediff has correct value..
>
> Do you see any error in the case, cause I don't?
>
>
> BTJ
>
> On Thu, 2003-09-11 at 14:29, Mattias Kregert wrote:
> > Solution:
> >
> > SELECT starttime, stoptime, (CASE WHEN stoptime-starttime >= 0 THEN
> > stoptime-starttime ELSE stoptime-starttime+'24 hours' END) as timediff
> > FROM mytable;
> >
> > /Mattias
> >
> > ----- Original Message -----
> > From: Bj�rn T Johansen
> > To: Andrew L. Gould
> > Cc: PostgreSQL general list
> > Sent: Thursday, September 11, 2003 2:12 PM
> > Subject: Re: [GENERAL] I need a SQL...
> >
> > On Thu, 2003-09-11 at 14:07, Andrew L. Gould wrote:
> > > On Thursday 11 September 2003 06:25 am, Bj�rn T Johansen wrote:
> > > > I need to write a SQL that calculates the interval between a start time
> > > > and a stop time. This is the easy part. The problem is that I only have
> > > > the time part, i.e. no date, so how can I be sure to also calculate the
> > > > interval if the start time is before midnight and the stop time is
> > after
> > > > midnight?
> > > >
> > > >
> > > > Regards,
> > > >
> > > > BTJ
> > >
> > > If the activity or period you are measuring can equal or exceed 12
> > hours, you
> > > won't be able to calculate it reliably without a start date and a stop
> > date.
> > > If the periods are always less than 12 hours (and you assume all the
> > data is
> > > good), then stop times that are less than start times would indicate an
> > > intervening midnight.
> > >
> > > The dates do not have to be in the same fields as the times, since you
> > can add
> > > date and time data to create a timestamp for datetime calculations:
> > >
> > > (stop_date + stop_time) - (start_date + start_time)
> > >
> > > Best of luck,
> > >
> > > Andrew Gould
> >
> >
> >
> > Yes, the period can exceed 12 hours, so what you are saying is that this
> > is not possible to solve
> > without the date part? I can write this logic in my business logic but I
> > was hoping to
> > solve this in my database layer...
> >
> >
> > BTJ
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
