Re: 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