Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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. –  desasteralex 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. –  Amit Phatarphekar Nov 1 '11 at 20:56

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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