PostgreSQL Administration - Postgres timestamps are out by one hour

Author

Postgres timestamps are out by one hour

Simon Kinsella

2006-10-26, 7:25 pm

Hello,

I'm experiencing a strange problem on two of my installs (PostgreSQL 8.1.4
on FreeBSD6.1-Release):

From the shell:

root@stilton> date
Thu Oct 26 16:03:09 BST 2006

From psql/any sql client:

template1=# select now();
now
------------------------------
2006-10-26 15:03:24.43408+00

The shell / system timestamp is correct local time (London, daylight
saving), but the postgres time is one hour behind.

postgresql.conf shows:
#timezone = unknown # actually, defaults to TZ
# environment setting

This is happening on two machines, but not on a third. All with same version
and config. Not sure where to start - any clues? Sounds like some time zone
or daylight saving thing...?

---
Simon Kinsella

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2006-10-26, 7:25 pm

"Simon Kinsella" < simon@bluefiresystem
s.co.uk> writes:
> The shell / system timestamp is correct local time (London, daylight
> saving), but the postgres time is one hour behind.

What does SHOW TIMEZONE say?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Simon Kinsella

2006-10-26, 7:25 pm

template1=# show timezone;
TimeZone
----------
GMT0

Thanks.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, October 26, 2006 4:53 PM
To: Simon Kinsella
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres timestamps are out by one hour

"Simon Kinsella" < simon@bluefiresystem
s.co.uk> writes:
> The shell / system timestamp is correct local time (London, daylight
> saving), but the postgres time is one hour behind.

What does SHOW TIMEZONE say?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2006-10-26, 7:25 pm

"Simon Kinsella" < simon@bluefiresystem
s.co.uk> writes:
> template1=# show timezone;
> TimeZone
> ----------
> GMT0

That's a non-daylight-savings zone ... and one would guess that the
postmaster was started with TZ=GMT or some such, else it would not have
picked that as the Postgres zone.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Simon Kinsella

2006-10-27, 5:14 am

Thanks for your help Tom - I've got it sorted now by setting
timezone='GMT0DST' in the conf file.

Simon.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 26, 2006 5:16 PM
To: Simon Kinsella
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres timestamps are out by one hour

"Simon Kinsella" < simon@bluefiresystem
s.co.uk> writes:
> template1=# show timezone;
> TimeZone
> ----------
> GMT0

That's a non-daylight-savings zone ... and one would guess that the
postmaster was started with TZ=GMT or some such, else it would not have
picked that as the Postgres zone.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links