Postgresql – Incorrect time on postgresql database

postgresqlwindows-xp

I have an XP VM (Xen) running postgresql 8.3.5. All was well, until last week sometime-the time showsup 1 hour ahead.

So the XP is configured to Mountain time, with no ntp or any other synch up – This machine doesnt have any internet access.

Postgres has all default settings in postgresql.conf, so it should pickup the system time.

Here's the output of various commands…
Current mountain time was "14:06" when i ran these commands….(pls ignore the minute/sec/msec differences below)

SHOW TIME ZONE;
"US/Mountain"

select now()
"2011-11-01 15:06:42.885-06"

select now()::timestamptz
"2011-11-01 15:06:51.828-06"

select localtime
"15:06:59.799"

select timezone('MST'::text, now())
"2011-11-01 14:07:10.535"

As you can see, only the last command manages to show time correctly. I cant figureout why suddenly it is going 1 hour ahead.

Other interesting observations:

  1. JBOSS application server running on that same machine has the same behavior

  2. Another standalone Java application shows the correct time though…

So if there was something wrong at OS level it should have been consistent across all applications running on the OS…but some of them like JBOSS,postgresql are showing 15:06 but others like my stand alone java application show 14:06

Best Answer

looks like you need to update your version of PostgreSQL. i believe 8.3.16 is current. DST rules have changed in the last few years.

addendum: according to JBOSS it's time comes either from the OS or JVM depending on scenario outlined in that link. So you should check JVM and OS too.

Related Topic