On 10/11/07, Tom Lane <[EMAIL PROTECTED]> wrote: > "Trevor Talbot" <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org