X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1831556390
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Mar 2005 09:40:11 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36134-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Mar 2005 09:40:09 +0000 (GMT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 0FC80563C0
	for <pgsql-sql@postgresql.org>; Tue, 22 Mar 2005 09:40:07 +0000 (GMT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Tue Mar 22 10:38:15 2005 +0100
Received: from rodos.fzk.de (resy6.fzk.de [141.52.68.10])
	by resy5.fzk.de (8.12.7/8.12.7/SuSE Linux 0.6) with ESMTP id
	j2M9c7sG013082; Tue, 22 Mar 2005 10:38:12 +0100
Message-ID: <423FE77E.6AC055CF@rodos.fzk.de>
Date: Tue, 22 Mar 2005 10:38:06 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: =?iso-8859-2?Q?B=E9atrice?= Yueksel <beatrice.yueksel@it-raum.ch>
Cc: Octavio Alvarez <alvarezp@tecbc.mx>, pgsql-sql@postgresql.org
Subject: Re: Your question about date
References: <opsnuhhzgwg6g4in@octavio.tecbc.mx>
	<423E99E2.7D428966@rodos.fzk.de>
	<1111402758.5174.22.camel@nimas.it-raum.ch>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS - amavis-milter (http://www.amavis.org/)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.031 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/300
X-Sequence-Number: 20779

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 

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