Thread: BUG #5227: please add a divide operator for intervals

The following bug has been logged online:

Bug reference:      5227
Logged by:          Michal Pasternak
Email address:      michal.dtz@gmail.com
PostgreSQL version: 8.4
Operating system:   doesn't matter
Description:        please add a divide operator for intervals
Details:

Please add a divide operator for INTERVAL type, if possible.

db=# SELECT '15 seconds'::INTERVAL / '15 seconds'::INTERVAL;
ERROR:  operator does not exist: interval / interval
LINE 1: SELECT '15 seconds'::INTERVAL / '15 seconds'::INTERVAL;
                                      ^
HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.


db=# SELECT EXTRACT(EPOCH FROM '15 seconds'::INTERVAL) / EXTRACT(EPOCH FROM
'15 seconds'::INTERVAL);
 ?column?
----------
        1
(1 row)

"Michal Pasternak" <michal.dtz@gmail.com> writes:
> Please add a divide operator for INTERVAL type, if possible.

Given that intervals have multiple subfields, it's far from obvious
what division should mean.  What is '1 month' / '1 day'?

> db=# SELECT EXTRACT(EPOCH FROM '15 seconds'::INTERVAL) / EXTRACT(EPOCH FROM
> '15 seconds'::INTERVAL);

If that's the behavior you want, you already have a way to do it
(and you could wrap that up in a user-defined operator if you chose).
It loses quite a lot of information though, so I doubt we'd want
to enshrine it as the standard definition.

            regards, tom lane

1 month / 1 day equals 30.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, December 03, 2009 12:46 AM
To: Michal Pasternak
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5227: please add a divide operator for intervals

"Michal Pasternak" <michal.dtz@gmail.com> writes:
> Please add a divide operator for INTERVAL type, if possible.

Given that intervals have multiple subfields, it's far from obvious
what division should mean.  What is '1 month' / '1 day'?

> db=# SELECT EXTRACT(EPOCH FROM '15 seconds'::INTERVAL) / EXTRACT(EPOCH
FROM
> '15 seconds'::INTERVAL);

If that's the behavior you want, you already have a way to do it
(and you could wrap that up in a user-defined operator if you chose).
It loses quite a lot of information though, so I doubt we'd want
to enshrine it as the standard definition.
        regards, tom lane

Micha=B3 Pasternak wrote:
> 1 month / 1 day equals 30.=20
>=20=20=20

or 31 or 28 or 29...