Thread: BUG #6385: extract epoch at timezone returns wrong value

The following bug has been logged on the website:

Bug reference:      6385
Logged by:          Sreeraj S
Email address:      sreeraj@ordyn.com
PostgreSQL version: 9.1.2
Operating system:   Windows Vista Professional
Description:=20=20=20=20=20=20=20=20

Below is given psql output - I am in timezone UTC+05:30 (India) as shown by
select now() output below.

When I give "SELECT extract(epoch from now() );" the output is
1325872658.xxx=20

I would expect a slightly incremented value when after a few seconds I give
the command "SELECT extract(epoch from now() at time zone 'utc+05:30');" .
Nut I get the value 1325833067.xxx which is the value for utc-05:30 !!!

And when I give "SELECT extract(epoch from now() at time zone
'utc-05:30');", I get 1325872672.xxx which is what is expected if I
specified timezone +05:30 !!!

-----  psql extract below  ------

ordprdlic=3D> select now();
              now
-------------------------------
 2012-01-06 23:27:35.663+05:30
(1 row)


ordprdlic=3D> SELECT extract(epoch from now() );
   date_part
----------------
 1325872658.608
(1 row)


ordprdlic=3D> SELECT extract(epoch from now() at time zone 'utc+05:30');
   date_part
----------------
 1325833067.073
(1 row)


ordprdlic=3D> SELECT extract(epoch from now() at time zone 'utc-05:30');
   date_part
----------------
 1325872672.977
(1 row)

sreeraj@ordyn.com writes:
> When I give "SELECT extract(epoch from now() );" the output is
> 1325872658.xxx

> I would expect a slightly incremented value when after a few seconds I give
> the command "SELECT extract(epoch from now() at time zone 'utc+05:30');" .
> Nut I get the value 1325833067.xxx which is the value for utc-05:30 !!!

> And when I give "SELECT extract(epoch from now() at time zone
> 'utc-05:30');", I get 1325872672.xxx which is what is expected if I
> specified timezone +05:30 !!!

I think you are confused about the sign convention for time zones
specified in POSIX notation.  See
http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES
particularly this comment:

    Another issue to keep in mind is that in POSIX time zone names,
    positive offsets are used for locations west of
    Greenwich.  Everywhere else, PostgreSQL follows the ISO-8601
    convention that positive timezone offsets are east of Greenwich.

This is not a bug, or at least not our bug --- we're just doing the best
we can to cope with inconsistent standards documents.

            regards, tom lane