Subject: Re: A proposal for Interval Math
From: Oliver Elphick <olly@lfix.co.uk>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200205191421.05691.josh@agliodbs.com>
References: <200205191336.10383.josh@agliodbs.com>
	<1021841886.1401.56.camel@linda> <200205191421.05691.josh@agliodbs.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-KTGmfzDC56TaOjgkO1YZ"
Date: 20 May 2002 08:54:51 +0100
Message-Id: <1021881291.1452.189.camel@linda>
Mime-Version: 1.0

--=-KTGmfzDC56TaOjgkO1YZ
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Sun, 2002-05-19 at 22:21, Josh Berkus wrote:

> However, I'll continue pushing for some solution that allows me to calcul=
ate=20
> 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?=20=20
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' =3D 70

which seems to assume that a month is 30 days.  On a different basis the
result would be 365.2425 * 7 / 12 / 3  =3D 71.019375 or 365 * 7 / 12 / 3=20
=3D 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.
--=20
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,=20
      and the lust of the eyes, and the pride of life, is
      not of the Father, but is of the world."=20=20=20=20=20=20=20
                             I John 2:17

--=-KTGmfzDC56TaOjgkO1YZ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQA86KvLYU1MND4dDBwRAgtrAKDJYxRVmtR6u5D0qUK5XyoZjOYgpACgzDSn
q3xbYcC+FfxuvTVjBQbIYEA=
=sHY7
-----END PGP SIGNATURE-----

--=-KTGmfzDC56TaOjgkO1YZ--