Re: I need a SQL...

Поиск
Список
Период
Сортировка
От Mattias Kregert
Тема Re: I need a SQL...
Дата
Msg-id 015901c37868$1c262660$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
Very strange indeed!

This is my output.
------------------------------------

Welcome to psql 7.3.3, the PostgreSQL interactive terminal.


Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit


test=# create table mytable (starttime time, stoptime time);
CREATE TABLE
test=# insert into mytable values ('10:45', '22:30');
INSERT 103688 1
test=# insert into mytable values ('19:45', '04:30');
INSERT 103689 1
test=# insert into mytable values ('00:00', '00:00');
INSERT 103690 1
test=# insert into mytable values ('23:59', '00:01');
INSERT 103691 1
test=# insert into mytable values ('00:01', '23:59');
INSERT 103692 1
test=# select starttime,stoptime,(case when stoptime-starttime >= 0 then stoptime-starttime else stoptime-starttime+'24
hours'end) as timediff from mytable; 
 starttime | stoptime | timediff
-----------+----------+----------
 10:45:00  | 22:30:00 | 11:45
 19:45:00  | 04:30:00 | 08:45
 00:00:00  | 00:00:00 | 00:00
 23:59:00  | 00:01:00 | 00:02
 00:01:00  | 23:59:00 | 23:58
(5 rows)


test=#
------------------------------------

As you see, it all works as it should. Can you do exactly the same and send me the complete output? If you get a
differentresult, then it's time to send in a bug report... 

/Mattias


----- Original Message -----
From: "Bjørn T Johansen" <btj@havleik.no>
> Yes, I am sure, I just use copy-and-paste and I have double checked....
> I am running on 7.3.4 but that shouldn't make any difference?
>
> BTJ
>
> On Thu, 2003-09-11 at 14:56, Mattias Kregert wrote:
> > 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" <btj@havleik.no>
> > To: "Mattias Kregert" <mattias@kregert.se>
> > Cc: <pgsql-general@postgresql.org>
> > 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
> > >
> --
> -----------------------------------------------------------------------------------------------
> Bjørn T Johansen (BSc,MNIF)
> Executive Manager
> btj@havleik.no                  Havleik Consulting
> Phone : +47 67 54 15 17         Conradisvei 4
> Fax : +47 67 54 13 91           N-1338 Sandvika
> Cellular : +47 926 93 298       http://www.havleik.no
> -----------------------------------------------------------------------------------------------
> "The stickers on the side of the box said "Supported Platforms: Windows
> 98, Windows NT 4.0,
> Windows 2000 or better", so clearly Linux was a supported platform."
> -----------------------------------------------------------------------------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

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

Предыдущее
От: Vivek Khera
Дата:
Сообщение: Re: State of Beta 2
Следующее
От: "Nigel J. Andrews"
Дата:
Сообщение: Re: I need a SQL...