Daylight Savings Time handling on persistent connections

[Randall Nortman]

I assume I'm not the first person to have encountered this, but I
couldn't find anything in the FAQ or on the mailing lists recently.
My apologies if this is already documented somewhere...

My application logs data to a Postgres table continuously (once every
15 seconds), maintaining a persistent connection. Each datum is
logged with a time stamp (Postgres type "timestamp with time zone").
The application does not explicitly set the time zone, and does not
specify it when inserting the records. So everything just defaults to
the local time zone configured for the system, which is "US/Eastern".
This has been working fine all summer.

Until this morning, of course, when DST ended and "US/Eastern"
switched from GMT+4 to GMT+5. Everything logged fine up to 01:59 EDT
(05:59 UTC). Then the clock ticked to 01:00 EST (06:00 UTC), and I
got a unique constraint violation, because the database incorrectly
computed that I was trying to insert another record at 01:00 EDT
(05:00 UTC). I restarted the application when I noticed the problem
this morning, and now everything is working correctly.

My suspicion is that Postgres calculates the local offset from UTC
only once per session, during session initialization. Therefore, it
fails to notice when the local offset changes as a result of DST,
causing the problem I just described. It's hard for me to test this,
because I don't have a system I can freely muck with the clock on, but
it would completely explain this behavior.

Is this what's happening? Is it considered a bug? I can see making
the case for not changing the offset mid-session, but in that case it
should be explained more thoroughly in the documentation.

In my case, I think I'll have my app convert all times to UTC before
inserting them. This should avoid all such problems in the future.

PostgreSQL version (client and server) is 7.4.5, on i686 Debian sarge.
The client app is in python 2.3.4 using psycopg.

Thanks,

Randall Nortman

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Vinko Vrsalovic]

On Mon, Nov 01, 2004 at 07:08:39PM +0100, Martijn van Oosterhout wrote:

For the parsing integer issue it may have worked, but this is another
kettle of fish. I don't think you can do this as a simple switch, it
would have to set during the initdb and not allowed to be changed
afterwards. I don't know if that something that can be supported.

I suspected it wasn't that easy. Anyhow, I strongly believe
that when no reasonable defaults can be deduced, the software
should give the user the ability to decide what he wants to do.

Of course technical (implementation , maintenance, etc.) issues are
highly relevant and if it can't reasonably be done, well, tough luck,
but I think (and I don't have a clue about the internals of
PostgreSQL, so take this with two grains of salt) a solution such
as the one you mention should be given consideration.

--
Vinko Vrsalovic <el[|-@-|]vinko.cl>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Vinko Vrsalovic]

On Mon, Nov 01, 2004 at 07:08:39PM +0100, Martijn van Oosterhout wrote:

For the parsing integer issue it may have worked, but this is another
kettle of fish. I don't think you can do this as a simple switch, it
would have to set during the initdb and not allowed to be changed
afterwards. I don't know if that something that can be supported.

I suspected it wasn't that easy. Anyhow, I strongly believe
that when no reasonable defaults can be deduced, the software
should give the user the ability to decide what he wants to do.

Of course technical (implementation , maintenance, etc.) issues are
highly relevant and if it can't reasonably be done, well, tough luck,
but I think (and I don't have a clue about the internals of
PostgreSQL, so take this with two grains of salt) a solution such
as the one you mention should be given consideration.

--
Vinko Vrsalovic <el[|-@-|]vinko.cl>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Steve Crawford]

On Sunday 31 October 2004 11:44 am, Tom Lane wrote:

Randall Nortman <po***********@ wonderclown.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.

Would it help to add the PG locale TZ to the insert statement? For
example the following queries return the TZ as text.

select to_char(now(),' tz');
to_char
---------
pst

select to_char(now()-'3 days'::interval ,'tz');
to_char
---------
pdt

So the following might fix this particular situation:
insert into sensor_readings _numeric (...) values (...,'2004-10-31
01:00:00 ' || to_char(now(),' tz'),...)

I realize that it assumes that the data is being inserted at the time
it was taken so a reading taken just before DST changes and inserted
just after will be incorrect but it may work for this particular app.

Of course the better solution is to have the application generate a
fully-qualified timestamp with time zone. Generating all the
timestamps in UTC and explicitly specifying that in the insert is
probably the easiest way to go. Your queries will still have your
local-appropriate TZ:

select '2004-10-31 00:00:00+00'::t imestamptz;
timestamptz
------------------------
2004-10-30 17:00:00-07

select '2004-11-01 00:00:00+00'::t imestamptz;
timestamptz
------------------------
2004-10-31 16:00:00-08
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Steve Crawford]

On Sunday 31 October 2004 11:44 am, Tom Lane wrote:

Randall Nortman <po***********@ wonderclown.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.

Would it help to add the PG locale TZ to the insert statement? For
example the following queries return the TZ as text.

select to_char(now(),' tz');
to_char
---------
pst

select to_char(now()-'3 days'::interval ,'tz');
to_char
---------
pdt

So the following might fix this particular situation:
insert into sensor_readings _numeric (...) values (...,'2004-10-31
01:00:00 ' || to_char(now(),' tz'),...)

I realize that it assumes that the data is being inserted at the time
it was taken so a reading taken just before DST changes and inserted
just after will be incorrect but it may work for this particular app.

Of course the better solution is to have the application generate a
fully-qualified timestamp with time zone. Generating all the
timestamps in UTC and explicitly specifying that in the insert is
probably the easiest way to go. Your queries will still have your
local-appropriate TZ:

select '2004-10-31 00:00:00+00'::t imestamptz;
timestamptz
------------------------
2004-10-30 17:00:00-07

select '2004-11-01 00:00:00+00'::t imestamptz;
timestamptz
------------------------
2004-10-31 16:00:00-08
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings