Thread

1. Proposal: Division operator for (interval / interval => double precision)

   Gurjeet Singh <gurjeet@singh.im> — 2024-06-24T00:57:00Z

   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
   
   
   
   

2. Re: Proposal: Division operator for (interval / interval => double precision)

   Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-06-24T03:54:27Z

   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
   >
   >
   >
   

3. Re: Proposal: Division operator for (interval / interval => double precision)

   David G. Johnston <david.g.johnston@gmail.com> — 2024-06-24T04:43:57Z

   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.
   

4. Re: Proposal: Division operator for (interval / interval => double precision)

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-06-24T08:34:00Z

   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
   
   
   
   

5. Re: Proposal: Division operator for (interval / interval => double precision)

   Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2024-06-24T08:47:06Z

   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