how to get a number of seconds in some interval ?
This is a discussion on how to get a number of seconds in some interval ? within the pgsql Admins forums, part of the PostgreSQL category; --> Hello, Is there a good method of knowing how mutch seconds does some interval have?? I tried my_interval / ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-13-2008, 07:15 PM
| ||||
| ||||
 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin      |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? Στις 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? Στις 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? Στις 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| |||
| |||
| Re: how to get a number of seconds in some interval ? 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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
|
05-13-2008, 07:15 PM
| ||||
| ||||
| Re: how to get a number of seconds in some interval ? 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 extract function. 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |
Linear Mode
