Postgres Pro
Downloads
Home   >   mailing lists

Thread: strange timezone problem

strange timezone problem

From
Nick Johnson
Date:
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

Re: strange timezone problem

From
Tom Lane
Date:
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

Re: strange timezone problem

From
andy
Date:
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

Re: strange timezone problem

From
Nick Johnson
Date:
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