Обсуждение: how to get a number of seconds in some interval ?
Hello, Is there a good method of knowing how mutch seconds does some interval have?? I tried my_interval / (interval '1 second'), but there is no operator interval / interval defines (I wonder why). Is there a method to do so? -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050
Στις Monday 12 May 2008 16:44:16 ο/η Julius Tuskenis έγραψε: > Hello, > > Is there a good method of knowing how mutch seconds does some interval > have?? I tried my_interval / (interval '1 second'), but there is no > operator interval / interval defines (I wonder why). > Is there a method to do so? > smth like dynacom=# SELECT extract(epoch from '49 hours 0 mins 12 seconds'::interval); > -- > Julius Tuskenis > Programavimo skyriaus vadovas > UAB nSoft > mob. +37068233050 > > -- Achilleas Mantzios
Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε: > Thank you, Achilleas. > > do you know why division of intervals was not implemented? As I see it - > there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = > 1.5 .... Seems straight forward... > No idea why. However as you pose it,it makes sense. > > Achilleas Mantzios rašė: > > Στις Monday 12 May 2008 16:44:16 ο/η Julius Tuskenis έγραψε: > > > >> Hello, > >> > >> Is there a good method of knowing how mutch seconds does some interval > >> have?? I tried my_interval / (interval '1 second'), but there is no > >> operator interval / interval defines (I wonder why). > >> Is there a method to do so? > >> > >> > > smth like > > dynacom=# SELECT extract(epoch from '49 hours 0 mins 12 seconds'::interval); > > > > > >> -- > >> Julius Tuskenis > >> Programavimo skyriaus vadovas > >> UAB nSoft > >> mob. +37068233050 > >> > >> > >> > > > > > > > > > > -- Achilleas Mantzios
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε: >> do you know why division of intervals was not implemented? As I see it - >> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = >> 1.5 .... Seems straight forward... >> > No idea why. However as you pose it,it makes sense. No, it doesn't really, because intervals contain multiple components. What would you define as the result of '1 month 1 hour' / '1 day' bearing in mind that the number of days per month is not fixed, and neither is the number of hours per day? You can derive a number if you're willing to make arbitrary assumptions about the relationships of the different units (as indeed extract(epoch) does), but hard-wiring such assumptions into a basic arithmetic operator doesn't seem like a good idea to me. regards, tom lane
Thank you, it's now clear to me. Tom Lane rašė: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > >> Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε: >> >>> do you know why division of intervals was not implemented? As I see it - >>> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = >>> 1.5 .... Seems straight forward... >>> >>> >> No idea why. However as you pose it,it makes sense. >> > > No, it doesn't really, because intervals contain multiple components. > What would you define as the result of > '1 month 1 hour' / '1 day' > bearing in mind that the number of days per month is not fixed, and > neither is the number of hours per day? > > You can derive a number if you're willing to make arbitrary assumptions > about the relationships of the different units (as indeed extract(epoch) > does), but hard-wiring such assumptions into a basic arithmetic operator > doesn't seem like a good idea to me. > > regards, tom lane > > -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050
Στις Monday 12 May 2008 18:09:11 ο/η Tom Lane έγραψε: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > > Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε: > >> do you know why division of intervals was not implemented? As I see it - > >> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = > >> 1.5 .... Seems straight forward... > >> > > No idea why. However as you pose it,it makes sense. > > No, it doesn't really, because intervals contain multiple components. > What would you define as the result of > '1 month 1 hour' / '1 day' > bearing in mind that the number of days per month is not fixed, and > neither is the number of hours per day? > SELECT extract(epoch from '1 month'::interval)/(60*60*24) as "Days in 1 month interval"; Days in 1 month interval -------------------------- 30 (1 row) So it seems that the arbitary assumption of (1 month=30 days, and 1 day = 24 hours) has already been made in the extract function. > You can derive a number if you're willing to make arbitrary assumptions > about the relationships of the different units (as indeed extract(epoch) > does), but hard-wiring such assumptions into a basic arithmetic operator > doesn't seem like a good idea to me. > > regards, tom lane > -- Achilleas Mantzios
On Tue, May 13, 2008 at 12:56 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > Στις Monday 12 May 2008 18:09:11 ο/η Tom Lane έγραψε: >> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: >> > Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε: >> >> do you know why division of intervals was not implemented? As I see it - >> >> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = >> >> 1.5 .... Seems straight forward... >> >> >> > No idea why. However as you pose it,it makes sense. >> >> No, it doesn't really, because intervals contain multiple components. >> What would you define as the result of >> '1 month 1 hour' / '1 day' >> bearing in mind that the number of days per month is not fixed, and >> neither is the number of hours per day? >> > > SELECT extract(epoch from '1 month'::interval)/(60*60*24) as "Days in 1 month interval"; > Days in 1 month interval > -------------------------- > 30 > (1 row) > So it seems that the arbitary assumption of (1 month=30 days, and 1 day = 24 hours) has already been made > in the extract function. True. But that's only because it doesn't have a date to work against. If you run: select '2007-02-01 12:00:00'::timestamp + '1 month'::interval; you get: 2007-03-01 12:00:00 If you run: select '2007-03-01 12:00:00'::timestamp + '1 month'::interval; you get: 2007-04-01 12:00:00 Then, if we run: select ('2007-03-01 12:00:00'::timestamp + '1 month'::interval) - '2007-03-01 12:00:00'::timestamp; we get: 31 days But if we run: select ('2007-02-01 12:00:00'::timestamp + '1 month'::interval) - '2007-02-01 12:00:00'::timestamp; we get: 28 days So, I'm not sure how many days a month has. But your point is valid that given no date to compare to, an interval of 1 month gets translated to 30 days. I can also see a lot of people showing up doing fancy interval math THEN wondering why it changes when you put a timestamp in the middle of it.
Στις Tuesday 13 May 2008 11:22:28 ο/η Scott Marlowe έγραψε: > On Tue, May 13, 2008 at 12:56 AM, Achilleas Mantzios > <achill@matrix.gatewaynet.com> wrote: > > ���� Monday 12 May 2008 18:09:11 �/� Tom Lane ������: > >> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > >> > ���� Monday 12 May 2008 17:32:39 �/� Julius Tuskenis ������: > >> >> do you know why division of intervals was not implemented? As I see it - > >> >> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' = > >> >> 1.5 .... Seems straight forward... > >> >> > >> > No idea why. However as you pose it,it makes sense. > >> > >> No, it doesn't really, because intervals contain multiple components. > >> What would you define as the result of > >> '1 month 1 hour' / '1 day' > >> bearing in mind that the number of days per month is not fixed, and > >> neither is the number of hours per day? > >> > > > > SELECT extract(epoch from '1 month'::interval)/(60*60*24) as "Days in 1 month interval"; > > Days in 1 month interval > > -------------------------- > > 30 > > (1 row) > > So it seems that the arbitary assumption of (1 month=30 days, and 1 day = 24 hours) has already been made > > in the extract function. > > True. But that's only because it doesn't have a date to work against. > If you run: > > select '2007-02-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-03-01 12:00:00 > > If you run: > select '2007-03-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-04-01 12:00:00 > > Then, if we run: > select ('2007-03-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-03-01 12:00:00'::timestamp; > we get: 31 days > > But if we run: > select ('2007-02-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-02-01 12:00:00'::timestamp; > we get: 28 days > > So, I'm not sure how many days a month has. > > But your point is valid that given no date to compare to, an interval > of 1 month gets translated to 30 days. I can also see a lot of people > showing up doing fancy interval math THEN wondering why it changes > when you put a timestamp in the middle of it. > Thanx for this well written explanation! -- Achilleas Mantzios
Scott Marlowe wrote: > True. But that's only because it doesn't have a date to work against. > If you run: > > select '2007-02-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-03-01 12:00:00 > > If you run: > select '2007-03-01 12:00:00'::timestamp + '1 month'::interval; > you get: 2007-04-01 12:00:00 > > Then, if we run: > select ('2007-03-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-03-01 12:00:00'::timestamp; > we get: 31 days > > But if we run: > select ('2007-02-01 12:00:00'::timestamp + '1 month'::interval) - > '2007-02-01 12:00:00'::timestamp; > we get: 28 days > > So, I'm not sure how many days a month has. Try looking at a calendar.;-) You will find that these are the exact days between the two dates. Feb has 28 days, so 1st of feb plus 1 month puts you at 1st of march march has 31 days so 1st of march plus 1 month puts you at 1st of april. Try a leap year - select ('2008-02-01 12:00:00'::timestamp + '1 month'::interval) - '2008-02-01 12:00:00'::timestamp; ?column? ---------- 29 days (1 row) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Tue, May 13, 2008 at 2:42 AM, Shane Ambler <pgsql@sheeky.biz> wrote: > Scott Marlowe wrote: > >> True. But that's only because it doesn't have a date to work against. >> If you run: >> >> select '2007-02-01 12:00:00'::timestamp + '1 month'::interval; >> you get: 2007-03-01 12:00:00 >> >> If you run: >> select '2007-03-01 12:00:00'::timestamp + '1 month'::interval; >> you get: 2007-04-01 12:00:00 >> >> Then, if we run: >> select ('2007-03-01 12:00:00'::timestamp + '1 month'::interval) - >> '2007-03-01 12:00:00'::timestamp; >> we get: 31 days >> >> But if we run: >> select ('2007-02-01 12:00:00'::timestamp + '1 month'::interval) - >> '2007-02-01 12:00:00'::timestamp; >> we get: 28 days >> >> So, I'm not sure how many days a month has. > > Try looking at a calendar.;-) Sorry, didn't realize I'd need to explain my joke. Thought it was pretty obvious. Next time I'll throw a smiley in there.
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > So it seems that the arbitary assumption of (1 month=30 days, and 1 day = 24 hours) has already been made in the extractfunction. Right, as I said: >> You can derive a number if you're willing to make arbitrary assumptions >> about the relationships of the different units (as indeed extract(epoch) >> does), The point is that we don't want to propagate that bogus assumption further than we have to. EXTRACT() already implies loss of information, so it's reasonable for it to deliver a simplified view of reality, but it doesn't follow that division should. regards, tom lane