Re: Proposal: Division operator for (interval / interval => double precision)
Hi
Its always a good idea to extend the functionality of PG.
Thanks
Kashif Zeeshan
On Mon, Jun 24, 2024 at 5:57 AM Gurjeet Singh wrote:
> Is there a desire to have a division operator / that takes dividend
> and divisor of types interval, and results in a quotient of type
> double precision.
>
> This would be helpful in calculating how many times the divisor
> interval can fit into the dividend interval.
>
> To complement this division operator, it would be desirable to also
> have a remainder operator %.
>
> For example,
>
> ('365 days'::interval / '5 days'::interval) => 73
> ('365 days'::interval % '5 days'::interval) => 0
>
> ('365 days'::interval / '3 days'::interval) => 121
> ('365 days'::interval % '3 days'::interval) => 2
>
> Best regards,
> Gurjeet
> http://Gurje.et
>
>
>
Re: Proposal: Division operator for (interval / interval => double precision)
On Sun, Jun 23, 2024 at 5:57 PM Gurjeet Singh wrote:
> Is there a desire to have a division operator / that takes dividend
> and divisor of types interval, and results in a quotient of type
> double precision.
[...]
> ('365 days'::interval / '3 days'::interval) => 121
> ('365 days'::interval % '3 days'::interval) => 2
>
>
Is it double or biginteger that your operation is producing?
How about making the % operator output an interval? What is the answer to:
'1 day 12 hours 59 min 10 sec' / '3 hours 22 min 6 sec'?
Though I'd rather add functions to produce numbers from intervals then let
the existing math operations be used on those. These seem independently
useful though like this feature I've not really seen demand for them from
others.
in_years(interval) -> numeric
in_days(interval) -> numeric
in_hours(interval) -> numeric
in_microseconds(interval) -> numeric
etc...
That said, implementing the inverse of the existing
interval/double->interval operator has a nice symmetry. Though the 4
examples are trivial, single unit, single scale, divisions, so exactly how
that translates into support for a possibly messy example like above I'm
uncertain.
There is no precedence, but why not add a new composite type, (whole
bigint, remainder bigint) that, for you example #2, would be
(121,2*24*60*60*1000*1000), the second field being 2 days in microseconds?
Possibly under a different operator so those who just want integer division
can have it more cheaply and easily.
David J.
Re: Proposal: Division operator for (interval / interval => double precision)
On Sun, 2024-06-23 at 17:57 -0700, Gurjeet Singh wrote:
> Is there a desire to have a division operator / that takes dividend
> and divisor of types interval, and results in a quotient of type
> double precision.
>
> This would be helpful in calculating how many times the divisor
> interval can fit into the dividend interval.
>
> To complement this division operator, it would be desirable to also
> have a remainder operator %.
>
> For example,
>
> ('365 days'::interval / '5 days'::interval) => 73
> ('365 days'::interval % '5 days'::interval) => 0
>
> ('365 days'::interval / '3 days'::interval) => 121
> ('365 days'::interval % '3 days'::interval) => 2
I think that is a good idea in principle, but I have one complaint,
and one thing should be discussed.
The complaint is that the result should be double precision or numeric.
I'd want the result of '1 minute' / '8 seconds' to be 7.5.
That would match how the multiplication operator works.
What should be settled is how to handle divisions that are not well defined.
For example, what is '1 year' / '1 day'?
- 365.24217, because that is the number of solar days in a solar year?
- 365, because we don't consider leap years?
- 360, because we use the usual conversion of 1 month -> 30 days?
Yours,
Laurenz Albe
Re: Proposal: Division operator for (interval / interval => double precision)
On Mon, Jun 24, 2024 at 2:04 PM Laurenz Albe
wrote:
> On Sun, 2024-06-23 at 17:57 -0700, Gurjeet Singh wrote:
> > Is there a desire to have a division operator / that takes dividend
> > and divisor of types interval, and results in a quotient of type
> > double precision.
> >
> > This would be helpful in calculating how many times the divisor
> > interval can fit into the dividend interval.
> >
> > To complement this division operator, it would be desirable to also
> > have a remainder operator %.
> >
> > For example,
> >
> > ('365 days'::interval / '5 days'::interval) => 73
> > ('365 days'::interval % '5 days'::interval) => 0
> >
> > ('365 days'::interval / '3 days'::interval) => 121
> > ('365 days'::interval % '3 days'::interval) => 2
>
> I think that is a good idea in principle, but I have one complaint,
> and one thing should be discussed.
>
> The complaint is that the result should be double precision or numeric.
> I'd want the result of '1 minute' / '8 seconds' to be 7.5.
> That would match how the multiplication operator works.
>
> What should be settled is how to handle divisions that are not well
> defined.
> For example, what is '1 year' / '1 day'?
> - 365.24217, because that is the number of solar days in a solar year?
> - 365, because we don't consider leap years?
> - 360, because we use the usual conversion of 1 month -> 30 days?
>
We will need to go back to first principles, I guess. Result of division is
quotient, which is how many times a divisor can be subtracted from
dividend, and remainder, which is the what remains after so many
subtractions. Since day to hours and month to days conversions are not
constants, interval/interval will result in an integer quotient and
interval remainder. That looks painful.
--
Best Wishes,
Ashutosh Bapat
