Thread: timezone, how postgres deal with it?

timezone, how postgres deal with it?

From
"Anibal David Acosta"
Date:

This query returns false

 

select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) = ('2012-11-20 18:00:00-03:00'::timestamp with time zone)

 

Why?

 

Does postgres convert the datetime or just show/hide time zone?

 

 

Re: timezone, how postgres deal with it?

From
Gary Chambers
Date:
> This query returns false
> select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) = ('2012-11-20 18:00:00-03:00'::timestamp with time
zone)

It's false for me, too.  Change '2012-11-20 18:00:00-03:00' to the correct '2012-11-20
16:00:00-03:00' and it will work as you expect.

--
G.


Re: timezone, how postgres deal with it?

From
Tom Lane
Date:
Gary Chambers <gwchamb@gwcmail.com> writes:
>> This query returns false
>> select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) = ('2012-11-20 18:00:00-03:00'::timestamp with time
zone)

> It's false for me, too.  Change '2012-11-20 18:00:00-03:00' to the correct '2012-11-20
> 16:00:00-03:00' and it will work as you expect.

I suppose the OP is confused about the sign of timezone offsets.

When reading/printing timestamptz values we follow ISO 8601, which says
negative offsets are west of Greenwich.  Unfortunately, there are other
standards that say the opposite, and just in case you weren't confused
yet, PG follows those other standards in other places :-(.  So for
example the timezone name "EST5EDT" refers to five hours west, as does
"-05:00" in a timestamptz value.  (The great thing about standards is
there are so many to choose from.)

There's some docs about this at
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html#DATATYPE-TIMEZONES

            regards, tom lane


Re: timezone, how postgres deal with it?

From
Adrian Klaver
Date:
On 11/20/2012 01:48 PM, Anibal David Acosta wrote:
> This query returns false
>
> select ('2012-11-20 17:00:00-02:00'::timestamp with time zone) =
> ('2012-11-20 18:00:00-03:00'::timestamp with time zone)
>
> Why?
>
> Does postgres convert the datetime or just show/hide time zone?
>

Try:
test=> select ('2012-11-20 17:00:00+02:00'::timestamp with time zone) = ('2012-11-20 18:00:00+03:00'::timestamp with
timezone); 
 ?column?
----------
 t
(1 row)

For explanation see:

http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html#DATATYPE-TIMEZONES


--
Adrian Klaver
adrian.klaver@gmail.com