Обсуждение: Should we throw error when converting a nonexistent/ambiguous timestamp?

It's DST transition season again, and that means that we're getting the
usual quota of questions from people who don't quite understand how
DST-related timestamp arithmetic works, and whose incorrect code seems
to work until exercised during a transition interval.  We've got this
one from a guy who got bit by converting a nonexistent local time:
http://archives.postgresql.org/pgsql-general/2010-03/msg00590.php
and last week we had one from an Aussie who was getting bit by the
behavior for ambiguous local times at the other end of the cycle:
http://archives.postgresql.org/pgsql-general/2010-03/msg00459.php

I'm starting to think that maybe we should throw error in these cases
instead of silently doing something that's got a 50-50 chance of being
wrong.  I'm not sure if the "assume standard time" rule is standardized,
but I think it might be better if we dropped it.  Thoughts?
        regards, tom lane

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Mar 15, 2010 at 7:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm starting to think that maybe we should throw error in these cases
>> instead of silently doing something that's got a 50-50 chance of being
>> wrong. �I'm not sure if the "assume standard time" rule is standardized,
>> but I think it might be better if we dropped it. �Thoughts?

> That seems overly picky and fairly pointless to me.  Generally I'm a
> big fan of the idea that obvious breakage is better than silent
> breakage, but in this case it seems highly likely that you'll still
> have silent breakage until such time as a time change rolls around.

Yes, that's true, the failure will only be apparent when a DST
transition is sufficiently close by.  However, the problem with the
current behavior is that the failure isn't obvious even then ---
you might not notice the data inconsistency until much later when
it's not possible to sort things out.

The current code behavior seems to me to be on par with, for example,
trying to intuit MM-DD versus DD-MM field orders.  We used to try to
do that, too, and gave it up as a bad idea.
        regards, tom lane

On Mon, Mar 15, 2010 at 9:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Mar 15, 2010 at 7:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I'm starting to think that maybe we should throw error in these cases
>>> instead of silently doing something that's got a 50-50 chance of being
>>> wrong.  I'm not sure if the "assume standard time" rule is standardized,
>>> but I think it might be better if we dropped it.  Thoughts?
>
>> That seems overly picky and fairly pointless to me.  Generally I'm a
>> big fan of the idea that obvious breakage is better than silent
>> breakage, but in this case it seems highly likely that you'll still
>> have silent breakage until such time as a time change rolls around.
>
> Yes, that's true, the failure will only be apparent when a DST
> transition is sufficiently close by.  However, the problem with the
> current behavior is that the failure isn't obvious even then ---
> you might not notice the data inconsistency until much later when
> it's not possible to sort things out.

I disagree.  Even if the application DOES record a wrong time-stamp,
it's likely to be wrong by exactly an hour, or say two hours, and it
may well be possible to reconstruct what should have happened later;
an application crash may be result in no data being recorded at all,
and may therefore be harder to recover from.  Alternatively the
timestamp may be used for something non-critical, like logging a
last-changed time, and now you've turned a minor inaccuracy into an
application crash.  The scenario you describe is possible too, but
it's not clear-cut.  If we were starting from scratch I think either
behavior would be defensible, but changing it now doesn't seem good to
me.

> The current code behavior seems to me to be on par with, for example,
> trying to intuit MM-DD versus DD-MM field orders.  We used to try to
> do that, too, and gave it up as a bad idea.

I suppose it's topologically equivalent, but to me that is an order of
magnitude crazier than this case.

Of course I may be in the minority...  but you did ask...

...Robert

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Mar 15, 2010 at 9:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The current code behavior seems to me to be on par with, for example,
>> trying to intuit MM-DD versus DD-MM field orders. �We used to try to
>> do that, too, and gave it up as a bad idea.

> I suppose it's topologically equivalent, but to me that is an order of
> magnitude crazier than this case.

> Of course I may be in the minority...  but you did ask...

Well, the purpose of asking is to see whether there's a consensus for
doing something.  If not, fine, it's one less thing to worry about...
        regards, tom lane