Daylight Savings Time handling on persistent connections
От | Randall Nortman |
---|---|
Тема | Daylight Savings Time handling on persistent connections |
Дата | |
Msg-id | 20041031161716.GX23033@li2-47.members.linode.com обсуждение исходный текст |
Ответы |
Re: Daylight Savings Time handling on persistent connections
|
Список | 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
В списке pgsql-general по дате отправления: