Thread: Division of intervals.

Attempting rather unsuccessfully to convert something of the form

45 years 9 mons 5 days 20:28:18.886891

to a total number of months, I attempted

SELECT age(now(), timestamp '1957-06-13') / interval '1 month';

and got the error

ERROR:  Unable to identify an operator '/' for types 'interval' and 'interval'
        You will have to retype this query using an explicit cast


Does anyone know what interval format intervals are stored as?  Is there a way
I can cast this expression to be able to find the number of months within
this interval?

Or is there a function I don't know about capable of doing this? (extracting
the total number of a specific interval from another interval)...  e.g. total
number of months, total number of days...

Josh.

Joshua Moore-Oliva <josh@chatgris.com> writes:
> I attempted
> SELECT age(now(), timestamp '1957-06-13') / interval '1 month';
> and got the error
> ERROR:  Unable to identify an operator '/' for types 'interval' and 'interval'

I'm not sure division of intervals is sensible --- consider the
recently-pointed-out issues about variable length of months, etc.

Does this do what you want?

regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'));
 date_part
-----------
        45
(1 row)

regression=# SELECT extract(month from age(now(), timestamp '1957-06-13'));
 date_part
-----------
         9
(1 row)

regression=# SELECT extract(year from age(now(), timestamp '1957-06-13')) *12
regression-# + extract(month from age(now(), timestamp '1957-06-13'));
 ?column?
----------
      549
(1 row)


            regards, tom lane

I guess that it does for this problem..  But I have other applications that
require the number of days...  and that gets a lot more complex to work out.

Are there any plans in the near future to add this functionality?

I had already through of this solution, but it appeared rather bulky to me,
but I guess that's all there is now thanks.

Josh.

On March 19, 2003 12:23 am, Tom Lane wrote:
> Joshua Moore-Oliva <josh@chatgris.com> writes:
> > I attempted
> > SELECT age(now(), timestamp '1957-06-13') / interval '1 month';
> > and got the error
> > ERROR:  Unable to identify an operator '/' for types 'interval' and
> > 'interval'
>
> I'm not sure division of intervals is sensible --- consider the
> recently-pointed-out issues about variable length of months, etc.
>
> Does this do what you want?
>
> regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'));
>  date_part
> -----------
>         45
> (1 row)
>
> regression=# SELECT extract(month from age(now(), timestamp '1957-06-13'));
>  date_part
> -----------
>          9
> (1 row)
>
> regression=# SELECT extract(year from age(now(), timestamp '1957-06-13'))
> *12 regression-# + extract(month from age(now(), timestamp '1957-06-13'));
> ?column?
> ----------
>       549
> (1 row)
>
>
>             regards, tom lane

On Wed, Mar 19, 2003 at 01:03:50 -0500,
  Joshua Moore-Oliva <josh@chatgris.com> wrote:
> I guess that it does for this problem..  But I have other applications that
> require the number of days...  and that gets a lot more complex to work out.

If you subtract the timestamps the interval will have 0 for the month/year
part and you can extract the epoch from it to get the time in seconds.
This can be divided by 24*60*60 to get days.

Thanks, that should work.

Hmm that gives me an idea... Maybe I'll try to make a date_ceil function
identical to date_part and submit it.

All this stuff currently just seems so messy!

Josh.

On March 19, 2003 06:13 am, Bruno Wolff III wrote:
> On Wed, Mar 19, 2003 at 01:03:50 -0500,
>
>   Joshua Moore-Oliva <josh@chatgris.com> wrote:
> > I guess that it does for this problem..  But I have other applications
> > that require the number of days...  and that gets a lot more complex to
> > work out.
>
> If you subtract the timestamps the interval will have 0 for the month/year
> part and you can extract the epoch from it to get the time in seconds.
> This can be divided by 24*60*60 to get days.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Wed, Mar 19, 2003 at 06:02:25AM -0500, Joshua Moore-Oliva wrote:
> Thanks, that should work.
>
> Hmm that gives me an idea... Maybe I'll try to make a date_ceil function
> identical to date_part and submit it.
>
> All this stuff currently just seems so messy!

I agree.  It looks somewhat incomplete and cumbersome.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)