Re: nulls - Mailing list pgsql-general

On Thu, March 12, 2009 12:00, Richard Huxton wrote:
> James B. Byrne wrote:
>> The basic issue is episodic duration, expressed as columns named
>> dt_effective_from and dt_superseded_after.  Both are datetime
>> types containing values normalized to utc.
>
> If it's unknown use null.
>
>> in some indeterminate future date, which may be never.
>
> That's not unknown that's "in the future".
>
> If you have an event that starts '2001-01-01 01:01:01+01' and
> ends "null" then you can confidently say "don't know" as to how
> long that event is.
>
>> On the other hand, setting some artificially excessive future
>> date seems in its place seems, to me, to have its own problems.
>
> Which is where you reach for the handy datetime literals as
> described below:
>  select 'infinity'::timestamp without time zone;
>  select '-infinity'::timestamp without time zone;
>

Ahh. I did not know about infinity as a value for datetime columns.
This I will investigate further.

For brevity's sake, and because I was focused on my own situation, I
did not give sufficient context for what is happening.  This issue
relates to taxation. In the regime I must deal with, taxation rates
are often set some considerable time before they come into effect.
They are also sometimes related to specific commodities for specific
times of the year.

For example, tomato forcings may be taxed at 5% from 20090101T0500Z
until 20090401T0359Z, then at 0% from 20090401T0400Z to ? (until the
minister decides to withdraw the preferential rate).

Sometime in January we may, in fact we will, know when the
preferential rate will come into effect. However, sometimes not even
the ministry itself knows when this will be revoked and the regular
tax rate reinstated.  On the other hand, there are instances when
the preferential treatment period is delimited from the outset so
that the effective and the superseded date are both known well in
advance.  The point being is that the necessary database updates can
be input well in advance of their being used.

Because of these real world constraints I do not see how we could
use a Boolean flag to indicate whether a rate is active or not.
What rate is in effect is entirely dependent upon the transaction
date and that date on occasion may be considerably in the past.  So
we are forced to determine the effective rate with a conditional
(assuming that we use null to mean unset) along the lines of:

select c.e_tx_rate
  from commodity_tax_rates c
  where c.commodity_class = <commodity_class>
    and c.date_effective_from <= <transaction_date>
    and
       (c.date_superseded_after IS NULL
        or
        c.date_superseded_after <= <transaction_date>)

Please forgive any syntax errors. This is obviously not a piece of
real code, for one thing the selection criteria involves far more
than commodity class and transaction_date. It serves but to
demonstrate one approach to the problem.

I infer that if instead we set the unknown superseded date to
infinity (or some arbitrary large date far in the future) then the
query is simplified to:

...
    and c.date_effective_from >= <transaction_date>
    and c.date_superseded_after <= <transaction_date>

Have I understood things aright?


--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3