From: Oliver Elphick <olly@lfix.co.uk>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: A proposal for Interval Math
Date: 20 May 2002 08:54:51 +0100
Message-ID: <1021881291.1452.189.camel@linda> (raw)
In-Reply-To: <200205191421.05691.josh@agliodbs.com>
References: <200205191336.10383.josh@agliodbs.com>
<1021841886.1401.56.camel@linda>
<200205191421.05691.josh@agliodbs.com>
On Sun, 2002-05-19 at 22:21, Josh Berkus wrote:
> However, I'll continue pushing for some solution that allows me to calculate
> pay periods in real intervals.
But this is a matter for your local business rules. If someone is paid
monthly and leaves one week into the month, there must be a local rule
to determine what proportion of his monthly pay he gets - is it always
7/28 or 7/30 or is it 7/{28,29,30,31} according to which month it is?
You are proposing a mathematical operation that looks exact but actually
has a fuzzy result and the reason it is fuzzy is that you are trying to
shortcut the process of deciding what rule to apply.
You mentioned calculating the number of 2-week intervals in an interval
column: if you need to do this, the column should not be defined as YEAR
TO MONTH but as DAY (of course, PostgreSQL hasn't yet implemented these
subtypes of INTERVAL). If you *have* to do it with a YEAR TO MONTH
column, you have implicitly defined a local rule that averages out the
difference in month lengths, because in a YEAR-MONTH interval, you don't
know how long a year or a month is. You gave the example
interval_round('7 months','days') / '3 days' = 70
which seems to assume that a month is 30 days. On a different basis the
result would be 365.2425 * 7 / 12 / 3 = 71.019375 or 365 * 7 / 12 / 3
= 70.972222. Both of those round to 71 days. Your 7 months interval is
anything from 209 to 213 days depending on which months it includes,
which is unknowable without a context.
So if you define interval_round() you must also include some means of
configuring what rule to use, because that is not necessarily going to
be the same in every organisation. It might not even be the same
between different applications in one organisation.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"For all that is in the world, the lust of the flesh,
and the lust of the eyes, and the pride of life, is
not of the Father, but is of the world."
I John 2:17
Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, josh@agliodbs.com
Subject: Re: A proposal for Interval Math
In-Reply-To: <1021881291.1452.189.camel@linda>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox