Обсуждение: Proposal: Division operator for (interval / interval => double precision)
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)
От
Kashif Zeeshan
Дата:
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 <gurjeet@singh.im> 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)
От
"David G. Johnston"
Дата:
On Sun, Jun 23, 2024 at 5:57 PM Gurjeet Singh <gurjeet@singh.im> 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.
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)
От
Ashutosh Bapat
Дата:
On Mon, Jun 24, 2024 at 2:04 PM Laurenz Albe <laurenz.albe@cybertec.at> 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