Re: I need a SQL...

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