Re: Daylight Savings Time handling on persistent connections

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.

> 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.

We have had varying and often platform-specific behaviors on this point
in past releases, but in 8.0 it should be possible to ensure consistent
results now that we are no longer at the mercy of the local libc's
timezone code.

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.)

> In my code, in fact, timestamps are recorded as seconds since the
> epoch, in UTC, so it makes little sense to convert to local time
> anyway. Right now, psycopg (the python module I'm using for postgres
> access) is generating the timestamp string for me (via
> psycopg.TimestampFromTicks()). I just need to figure out how to get
> it to generate the string with an explicit time zone, which I'm sure
> is possible. And if not, I'll just generate the string myself.

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

For example:

regression=# select 'epoch'::timestamptz + 1099251435 * '1 second'::interval;
?column?
------------------------
2004-10-31 14:37:15-05
(1 row)

Or you can do

select 'epoch'::timestamptz + '1099251435 seconds'::interval;

which saves a couple microseconds at execution but requires assembling
the query string as a string. The latter is probably easy for your
application, but if say you were extracting the numeric value from a
database column, the former would be easier.

regards, tom lane