Thread: timezone, how postgres deal with it?

> 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.

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

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