Re: I need a SQL...

Поиск
Список
Период
Сортировка
От Mattias Kregert
Тема Re: I need a SQL...
Дата
Msg-id 00e101c37860$685e2620$09000a0a@kregert.se
обсуждение исходный текст
Ответ на I need a SQL...  (Bjørn T Johansen <btj@havleik.no>)
Ответы Re: I need a SQL...  (Bjørn T Johansen <btj@havleik.no>)
Список pgsql-general
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 -----
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Mattias Kregert"
Дата:
Сообщение: Re: I need a SQL...
Следующее
От: "Nigel J. Andrews"
Дата:
Сообщение: Re: how to replicate database