Re: Timezone database changes - Mailing list pgsql-hackers

On 10/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Trevor Talbot" <quension@gmail.com> writes:
> > Neither is the birth certificate.  The recorded, legal time of the
> > birth is the one that was written down.  If it doesn't happen to match
> > an international notion of current time, that's unfortunate, but it's
> > not subject to arbitrary changes later.  Even if it does match, it
> > still belongs to a specific time zone.  That's the key semantic point:
> > regurgitating that time as anything other than exactly what it was
> > entered as is simply not correct.
>
> I'm not convinced about that.  One consideration I think you are failing
> to account for is that there is a big difference between past and future
> times, at least in terms of what is likely to be the meaning of a
> change.  The above reasoning might apply to a past time but I think it's
> bogus for a future time.  If the TZ offset for a future time changes,
> it's likely because of a DST law change, and we are in Peter's
> what-time-is-the-appointment scenario.  A TZ offset for a past time
> probably should not change, but if it does, it suggests a retroactive
> data correction.  Surely you don't intend to prevent people from fixing
> bad data?

No, but I am mixing some different issues together.  The original
question of this thread is what happens when the zone rules change for
an already-entered time.  I contend the answer to that is a symptom of
the semantics of how it's treated, which boil down to whether a value
is stable relative to a specific zone, or to UTC.  Other symptoms
include whether it accurately transports, can be retrieved in the same
form it was entered in, etc.

So the birth certificate argument is for past times, unlikely to have
zone rules change, but does need to be tagged with a specific time
zone so that it can be returned exactly the same way.

The appointment argument is for future times, more likely to have zone
rules change, and still needs to be tagged with a specific time zone.
That includes transport, which implies that it should never be exposed
in any other form.

Same semantics really, it's just that one problem is less likely to
happen in one of those situations.

If something like a birth date is found to be incorrect, it would have
to be corrected through official methods, which means some human
involvement.  The only reasonable thing a database can do is keep it
exactly the same as entered until explicitly told otherwise; changing
it automatically is equivalent to corruption.

If the database is using zone rules that are out of date, and the
stamps are stored as local value and zone, only dynamic calculations
are affected.  When the zone rules are updated, not changing the data
is always the correct approach.

I don't know if there have ever been retroactive changes to DST laws
we could look at, but I could easily see a change like that affecting
some things and not others.  Individual organizations make their own
calls, state entities make varying decisions after gigantic reviews,
etc.  It would not surprise me at all to see yearly permits
retroactively change, lifetime certificates stay the same because they
don't want to reprint stuff, except the modern computerized department
that doesn't need to reprint much of anything, etc.  The correct
result is subjective, but since it's still a human call, you want to
default to not mangling the data.

People shouldn't be prevented from fixing bad data, but I don't see
how the database can possibly determine it *is* bad.  It seems similar
to the server's clock being off while it's inserting data with NOW;
there's just nothing you can do to automatically repair that after you
fix the clock.