From: Christoph Haller <ch@rodos.fzk.de>
To: Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
Cc: Octavio Alvarez <alvarezp@tecbc.mx>
Cc: pgsql-sql@postgresql.org
Subject: Re: Your question about date
Date: Tue, 22 Mar 2005 10:38:06 +0100
Message-ID: <423FE77E.6AC055CF@rodos.fzk.de> (raw)
References: <opsnuhhzgwg6g4in@octavio.tecbc.mx>
<423E99E2.7D428966@rodos.fzk.de>
<1111402758.5174.22.camel@nimas.it-raum.ch>
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
> >
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, beatrice.yueksel@it-raum.ch, alvarezp@tecbc.mx
Subject: Re: Your question about date
In-Reply-To: <423FE77E.6AC055CF@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox