pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
5+ messages / 3 participants
[nested] [flat]
* (no subject)
@ 2005-03-18 17:57 Octavio Alvarez <alvarezp@tecbc.mx>
0 siblings, 1 reply; 5+ messages in thread
From: Octavio Alvarez @ 2005-03-18 17:57 UTC (permalink / raw)
To: pgsql-sql
Sorry, I tried to make my subject as good as possible.
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.
Any help will be appreciated.
--Octavio
--
Ing. Octavio Alvarez Piza, Jefe de Informática, TBC Universidad.
Tel.: +52 (664) 621-7111 ext. 133; E-mail: alvarezp@tecbc.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re:
@ 2005-03-21 09:54 Christoph Haller <ch@rodos.fzk.de>
parent: Octavio Alvarez <alvarezp@tecbc.mx>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Haller @ 2005-03-21 09:54 UTC (permalink / raw)
To: Octavio Alvarez <alvarezp@tecbc.mx>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Your question about date
@ 2005-03-21 10:59 Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 5+ messages in thread
From: Béatrice Yueksel @ 2005-03-21 10:59 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: Octavio Alvarez <alvarezp@tecbc.mx>; pgsql-sql
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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Your question about date
@ 2005-03-22 09:38 Christoph Haller <ch@rodos.fzk.de>
parent: Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Haller @ 2005-03-22 09:38 UTC (permalink / raw)
To: Béatrice Yueksel <beatrice.yueksel@it-raum.ch>; +Cc: Octavio Alvarez <alvarezp@tecbc.mx>; pgsql-sql
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
> >
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Your question about date
@ 2005-03-22 09:57 Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Béatrice Yueksel @ 2005-03-22 09:57 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: Octavio Alvarez <alvarezp@tecbc.mx>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-03-22 09:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-18 17:57 Octavio Alvarez <alvarezp@tecbc.mx>
2005-03-21 09:54 ` Re: Christoph Haller <ch@rodos.fzk.de>
2005-03-21 10:59 ` Re: Your question about date Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
2005-03-22 09:38 ` Re: Your question about date Christoph Haller <ch@rodos.fzk.de>
2005-03-22 09:57 ` Re: Your question about date Béatrice Yueksel <beatrice.yueksel@it-raum.ch>
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