strange timezone problem

From: Nick Johnson <ctfdy(at)spatula(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: strange timezone problem
Date: 2007-11-07 17:07:03
Message-ID: 20071107090015.E46367@turing
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Before I open a bug on this, I wanted to do a sanity check, since there
may be something I'm just not seeing.

I'm using PostgreSQL 8.2.3 and seeing this behaviour with timezones:

select create_date from article_lead;
create_date
---------------------------
2007-11-04 16:35:33.17+00
2007-11-04 04:35:36.09+00
2007-11-05 04:35:36.38+00
2007-11-05 16:35:36.67+00
(4 rows)

select create_date from article_lead where create_date >= '2007-11-03
17:00:00.0' and create_date <='2007-11-04 16:00:00.0';
create_date
---------------------------
2007-11-04 04:35:36.09+00

Shouldn't that second row have been in the results of the second query?
(create_date is of timestamptz type).

Also noted this oddity, though it may be unrelated:

set TimeZone='America/Los_Angeles';
select create_date from article_lead;
create_date
---------------------------
2007-11-04 08:35:33.17-08
2007-11-03 21:35:36.09-07 <-- why 07?
2007-11-04 20:35:36.38-08
2007-11-05 08:35:36.67-08

Nick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-11-07 17:10:23 Re: prepared statements suboptimal?
Previous Message Tom Lane 2007-11-07 17:04:36 Re: DB on a ramdisk (was Re: Temporary, In-memory Postgres DB?)