Re: Your question about date - Mailing list pgsql-sql

Perhaps it will be better if you put an 'order by' in the select.



SELECT (( select test1.date           from test test1           where test1.date > test.date order by date limit 1)   -
test.date) AS result from test order by date;
 
result
--------    42    31    33    25

Regards,
Beatrice

Am Dienstag, den 22.03.2005, 10:38 +0100 schrieb Christoph Haller:
> That's a good one, but it has the disadvantage of 
> giving a null result row: 
> insert into test values ( '2004-12-22' ) ;
> The SELECT from below gives 
>  result 
> --------
>      31
>      33
>      25
>  (null)
>      42
> (5 rows)
> Also, I am not sure about the order of values. 
> Anyway, Béatrice, thanks for your time. 
> Regards, Christoph 
> 
Am Montag, den 21.03.2005, 06:06 -0800 schrieb Octavio Alvarez Piza: 
> YES! It definitely should work. You guys are good! I'll make it
> first thing to try after vacation. I think I will only need to
> add an ORDER BY test1.date ASC clause.
> 
> Heh... This method could also work for getting next/last-record on
> serials...
> 
> Octavio.
> 
> 
> Béatrice Yueksel wrote:
> > 
> > Dear Christoph,
> > perhaps you could try something like this example.
> > Regards,
> > BĂŠatrice
> > 
> > The table:
> > ----------
> > # select * from test;
> >     date
> > ------------
> >  2005-02-02
> >  2005-03-05
> >  2005-04-07
> >  2005-05-02
> > (4 rows)
> > 
> > The query
> > ----------
> > 
> > SELECT
> >  (( select test1.date
> >            from test test1
> >            where test1.date > test.date limit 1)
> >    - test.date ) AS result from test;
> > 
> > t1.date >
> > RESULT:
> > -------
> >  result
> > --------
> >      31
> >      33
> >      25
> > 
> > Am Montag, den 21.03.2005, 10:54 +0100 schrieb Christoph Haller:
> > > Octavio Alvarez wrote:
> > > >
> > > > Sorry, I tried to make my subject as good as possible.
> > >
> > > Ahem, what subject?
> > > >
> > > > I have a table where I store the dates in which I take out of my inventory
> > > > (like "installation dates")
> > > >
> > > > table
> > > > ---------------
> > > > row_id   SERIAL
> > > > date     DATE
> > > > fk_item  INTEGER
> > > >
> > > > and that's pretty much it.
> > > >
> > > > I want to have a query returning how long have been certain items lasting.
> > > >
> > > > Say I have:
> > > >
> > > > SELCT date FROM table WHERE fk_item = "INKJET_INK-BW"
> > > >
> > > > date
> > > > -------------
> > > > 2005-02-02
> > > > 2005-03-05
> > > > 2005-04-07
> > > > 2005-05-02
> > > >
> > > > I need something to calculate the intervals between those dates, like this:
> > > >
> > > > intervals (in days)
> > > > --------------------
> > > >   31
> > > >   34
> > > >   25
> > > >
> > > > So I can get the stddev and stuff from the "duration" of the items.
> > > >
> > > > I've been having a hard time with it. I'm trying NOT to program new
> > > > functions.
> > >
> > > I cannot see how this could be achieved without the use of a function.
> > > But if there is a way after all, I would be interested in learning it.
> > > >
> > > > Any help will be appreciated.
> > > >
> > > > --Octavio
> > > > --
> > >
> > > Regards, Christoph
> > >
-- 
Béatrice Yueksel
Softwareentwicklung  
it-raum
Sperrstrasse 91
4057 Basel
Telefon +41 061 683 05 05
Fax     +41 061 683 93 50

beatrice.yueksel@it-raum.ch
http://www.it-raum.ch - eine Unternehmung von Kiebitz