Re: Daylight Savings Time handling on persistent connections

On Sun, Oct 31, 2004 at 12:47:31PM -0500, Tom Lane wrote:
> Randall Nortman <postgreslists(at)wonderclown(dot)com> writes:
> > I can't reproduce the error without messing up my clock, but from my
> > logs, here's the text of the SQL sent to the server:
>
> > insert into sensor_readings_numeric (sensor_id, reading_ts, reading,
> > min, max) values (3, '2004-10-31 01:00:00', 0.540602, 0.519071837254,
> > 0.551811824539)
>
> > And this came back:
> > ERROR: duplicate key violates unique constraint "sensor_readings_numeric_pkey"
>
> Hmm ... and you were generating that timestamp string how exactly?
> I suspect that you actually sent the same timestamp string twice, one
> hour apart, in which case I'd have to call this an application bug.
> You really need to include the timezone specification in order to
> have an unambiguous timestamp string. It doesn't have to be UTC as you
> previously suggested, but it does have to be labeled with the intended
> zone.

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 -- if it's currently EST, then the server would
assume that EST was intended, but if it's currently EDT, then it would
assume EDT. If this were the case, my code would be correct -- yes, I
tried to insert the same timestamp value twice, but the inserts were
issued when my local timezone was in different offsets from UTC.

But it appears that PostgreSQL always assumes EDT in this case,
regardless of the current time? I can see that being a good idea, for
its predictability. For example, a client running on a different host
than the server might have its clock off by a few minutes; this could
then cause the server to make a different assumption about the correct
time zone than the client. Even running on the same host, a delay
between the client issuing a command and the server processing it
could cause this problem.

So yeah, I see the wisdom of always specifying a time zone explicitly
in the query. In my case, it will probably be easiest to specify UTC,
because otherwise I have to figure out myself whether or not DST was
in effect when the sensor reading was generated. 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.