Обсуждение: 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