Skip site navigation (1) Skip section navigation (2)

Daylight Savings Time handling on persistent connections

From: Randall Nortman <postgreslists(at)wonderclown(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Daylight Savings Time handling on persistent connections
Date: 2004-10-31 16:17:16
Message-ID: 20041031161716.GX23033@li2-47.members.linode.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-10-31 16:52:03
Subject: Re: Daylight Savings Time handling on persistent connections
Previous:From: Ed L.Date: 2004-10-31 16:10:08
Subject: Re: Replicating sequences

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group