Re: Daylight Savings Time handling on persistent connections

On Sun, Oct 31, 2004 at 02:44:51PM -0500, Tom Lane wrote:
> Randall Nortman <postgreslists(at)wonderclown(dot)com> writes:
> > Ah, I see now. PostgreSQL is behaving a bit differently than I
> > expected. The timestamp string above is ambiguous in the timezone
> > US/Eastern -- it could be EST or EDT. I was expecting PostgreSQL to
> > resolve this ambiguity based on the current time when the SQL
> > statement is processed
>
> I think this would be a very bad thing for it to do. It might seem
> to make sense for a timestamp representing "now", but as soon as you
> consider a timestamp that isn't "now" it becomes a sure way to shoot
> yourself in the foot.

Yes, I absolutely see your point.

> > But it appears that PostgreSQL always assumes EDT in this case,
> > regardless of the current time?
>
> Actually, the intended and documented behavior is that it should
> interpret an ambiguous time as local standard time (e.g., EST not EDT).
> That seems to be broken at the moment :-(, which is odd because I'm
> quite certain I tested it last time we touched the relevant subroutine.

It certainly seems that way, but as I've said I can't reproduce the
bug without mucking with my clock, which is not an option right now.
But looking at the data which was generated overnight in UTC, I see
continuous data all the way up to 05:59. If the server had started
converting to EST at 01:00EDT, there would be a gap in the data from
05:00UTC to 06:00UTC as the server switched from a +4 offset to +5,
and then data would have been logged with a timestamp one hour in the
future through 06:59UTC, and then I would have gotten a unique
constraint violation when the actual switch happened.

> Before I go off and try to fix it, does anyone have any objection to
> the rule "interpret an ambiguous time as local standard time"?
> This would normally mean picking the later of the two possible
> interpretations, which might be the wrong choice for some applications.
> (I notice that HPUX's cron is documented to choose the earlier
> interpretation in comparable situations.)

I'm finding it hard to see how either way is likely to generate good
results in *any* application, much less in a majority of applications.
So in a way, perhaps the most correct thing to do would be to spit out
an error if the timestamp is ambiguous. Any application which deals
with timestamps in anything other than UTC should really be handling
the disambiguation itself, because the server can't possibly know what
the application means to do. Not generating an error is likely to
allow an application bug to go unnoticed, especially if the database
does not have a unique constraint on timestamps (as mine does).

Then again, it's not up to the database to expose bugs in the client,
so perhaps it's best to just stick with the current intended behavior
of always choosing local standard time. Or maybe we should write our
legislative representatives and get them to abolish DST. ;)

> Actually, your best bet is to forgo the conversion altogether. The
> recommended way to get from a Unix epoch value to a timestamp is
>
> 'epoch'::timestamptz + NNNNN * '1 second'::interval

At first glance, that seems to me to be really inefficient, but that's
just because my brain tends to associate verbosity in code with
runtime overhead. In this case, it's probably just as fast as letting
the Python library do the math required to convert the Unix timestamp
to a date/time string. And if Postgres stores timestamps as some unit
of time since an epoch, then it would be quite a bit more efficient.
Of course, all these calculations happen in the blink of an eye, and
I'm only logging data every 15 seconds, so I suppose it doesn't matter
anyway. So thanks for the tip! That will be much easier and more
reliable than the way I'm currently doing it. (I just hope that
nobody ever gets the idea of changing the Unix epoch.)

Thanks for all your help,

Randall Nortman