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)


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

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

select localtime

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

  • Looks like you've ran into a summer/winter time change issue. PostgreSQL allows you to set a default timezone (timezone param in the postgresql.conf). I too got struggled into so much issues regarding the timezones (e.g. the summer/winter change happens at different dates in US and the EU), that I decided to run all my servers and databases at UTC, to keep them in sync. All other timings are handled in the layer above. Nov 1 '11 at 20:33
  • I've tried that as well (in postgresql.conf). The queries show correct timezone, but the time is 1 hr ahead. Nov 1 '11 at 20:56

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.