Thread: ...
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
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
Dear Christoph, perhaps you could try something like this example. Regards, Béatrice The table: ---------- # select * from test; date ------------2005-02-022005-03-052005-04-072005-05-02 (4 rows) The query ---------- SELECT(( select test1.date from test test1 where test1.date > test.date limit 1) - test.date ) AS resultfrom 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 --
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 > >
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