strange timezone problem
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
Nick Johnson <ctfdy@spatula.net> writes:
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?
Huh? Those results look perfectly sane to me.
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
That's correct ... as of last year, DST extends through the first Sunday
in November in the USA.
regards, tom lane
Nick Johnson wrote:
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)
Note the top two are not in order (cuz you didnt 'order by create_date')
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
This one is correct, it is the second row. And the first row should not
be in because its > 16:00.
The db looks correct to me.
-Andy
On Wed, 7 Nov 2007, Tom Lane wrote:
Nick Johnson <ctfdy@spatula.net> writes:
I'm using PostgreSQL 8.2.3 and seeing this behaviour with timezones:
[snip]
Shouldn't that second row have been in the results of the second query?
Huh? Those results look perfectly sane to me.
Ah, you're right of course. Just a complete mental lapse on my part (thus
the need of a 'sanity' check).
That's correct ... as of last year, DST extends through the first Sunday
in November in the USA.
Twice in one morning the database was cleverer than me.
Nick