Thread: BUG #6424: Possible error in time to seconds conversion

The following bug has been logged on the website:

Bug reference:      6424
Logged by:          Olaf
Email address:      o.bousche@krohne.com
PostgreSQL version: 9.1.2
Operating system:   Windows 7 - 64bits
Description:=20=20=20=20=20=20=20=20

Should the query

select
  extract(epoch
    from cast('2012-01-01 14:30:1' as
             timestamp) -
         cast('1970-01-01 0:0:0' as
              timestamp))) -=20
  extract(epoch
    from (cast('2012-01-01 14:30:1' as
              timestamp)))

return 0 instead of 3600?

o.bousche@krohne.com writes:
> Should the query

> select
>   extract(epoch
>     from cast('2012-01-01 14:30:1' as
>              timestamp) -
>          cast('1970-01-01 0:0:0' as
>               timestamp))) -
>   extract(epoch
>     from (cast('2012-01-01 14:30:1' as
>               timestamp)))

> return 0 instead of 3600?

Well, right now it's operating as designed, because extract(epoch,
timestamp without timezone) tries to rotate the timestamp from local
time to GMT so that "epoch 0" corresponds to midnight GMT 1970-01-01.
(I presume that you are in a GMT+1 timezone.)

Changing that behavior is one of the possible solutions to the problem
being discussed over here:
http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
but I don't believe we have any consensus yet about whether that
would be a good idea.

            regards, tom lane

On Wed, Feb 1, 2012 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> o.bousche@krohne.com writes:
>> Should the query
>
>> select
>> =A0 extract(epoch
>> =A0 =A0 from cast('2012-01-01 14:30:1' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0timestamp) -
>> =A0 =A0 =A0 =A0 =A0cast('1970-01-01 0:0:0' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 timestamp))) -
>> =A0 extract(epoch
>> =A0 =A0 from (cast('2012-01-01 14:30:1' as
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 timestamp)))
>
>> return 0 instead of 3600?
>
> Well, right now it's operating as designed, because extract(epoch,
> timestamp without timezone) tries to rotate the timestamp from local
> time to GMT so that "epoch 0" corresponds to midnight GMT 1970-01-01.
> (I presume that you are in a GMT+1 timezone.)
>
> Changing that behavior is one of the possible solutions to the problem
> being discussed over here:
> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
> but I don't believe we have any consensus yet about whether that
> would be a good idea.

TBH, I think the behavior of the example given is 100% correct *if a
timezone isn't specified', which the OP didn't.  It's only weird if
you do this:

postgres=3D# select extract(epoch from '2012-01-01 14:30:1'::timestamp -
'1970-01-01 0:0:0 GMT'::timestamp)
  - extract(epoch from '2012-01-01 14:30:1'::timestamp);

which really boils down to this:
postgres=3D# select extract(epoch from  '1970-01-01 0:0:0 GMT'::timestamp);
 date_part
-----------
     21600
(1 row)

which is what seems busted to me.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> TBH, I think the behavior of the example given is 100% correct *if a
> timezone isn't specified', which the OP didn't.  It's only weird if
> you do this: ...
> which really boils down to this:
> postgres=# select extract(epoch from  '1970-01-01 0:0:0 GMT'::timestamp);
>  date_part
> -----------
>      21600
> (1 row)

> which is what seems busted to me.

Well, the timezone specification in that input is ignored, so you'll get
that result (or actually, a result that depends on your timezone setting
--- for me, that prints 18000) regardless of whether you write a
timezone or which one you write.


The underlying issue here is that at some time in the forgotten past,
we decided that these two operations should produce the same result:

regression=# select extract(epoch from '1970-01-01 0:0:0'::timestamp);
 date_part
-----------
     18000
(1 row)

regression=# select extract(epoch from '1970-01-01 0:0:0'::timestamptz);
 date_part
-----------
     18000
(1 row)

I believe that the second behavior is entirely correct, because since
I'm in EST5EDT zone, "local midnight" for me is in fact 5 hours behind
GMT.  However, it seems debatable whether the first behavior is correct,
since timestamp without timezone's operations really ought not depend
on the timezone setting.

If you do want a timezone-aware epoch value, you could always cast the
timestamp value to timestamptz; but if you don't, it's damn hard to get
one that's not, using the currently available operations.  I think you
have to do what the OP suggests here, namely subtract two timestamp
values (forming an interval) and then use extract(epoch from interval).
Ugh.

            regards, tom lane

Thank you for the fast reply=0D
=0D
I was not sure this was some spurious result messing up my calculations.=0D
If it is by design and consistent it does not present any problems for=0D
me.=0D
=0D
For me the matter is closed.=0D
=0D
Olaf=0D
=0D
=0D
=0D
-----Original Message-----=0D
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] =0D
Sent: Wednesday, 01 February, 2012 17:00=0D
To: Bousche, Olaf=0D
Cc: pgsql-bugs@postgresql.org=0D
Subject: Re: [BUGS] BUG #6424: Possible error in time to seconds=0D
conversion =0D
=0D
o.bousche@krohne.com writes:=0D
> Should the query=0D
=0D
> select=0D
>   extract(epoch=0D
>     from cast('2012-01-01 14:30:1' as=0D
>              timestamp) -=0D
>          cast('1970-01-01 0:0:0' as=0D
>               timestamp))) - =0D
>   extract(epoch=0D
>     from (cast('2012-01-01 14:30:1' as=0D
>               timestamp)))=0D
=0D
> return 0 instead of 3600?=0D
=0D
Well, right now it's operating as designed, because extract(epoch,=0D
timestamp without timezone) tries to rotate the timestamp from local=0D
time to GMT so that "epoch 0" corresponds to midnight GMT 1970-01-01.=0D
(I presume that you are in a GMT+1 timezone.)=0D
=0D
Changing that behavior is one of the possible solutions to the problem=0D
being discussed over here:=0D
http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php=0D
but I don't believe we have any consensus yet about whether that would=0D
be a good idea.=0D
=0D
            regards, tom lane=0D
=0D
=0D
 This message has been scanned for malware by Websense Hosted Email=0D
Security.=0D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D DISCLAIMER =3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D =0D
NOTE: The information transmitted in this email is for the person or entity=
 to which it is addressed: =0D
it may contain information that is confidential and/or legally privileged. =
=0D
If you are not the intended recipient, please do not read, use, retransmit =
or disseminate this information. =0D
Although this email and any attachments are believed to be free of any viru=
s, it is the responsibility =0D
of the recipient to ensure that they are virus free. No responsibility is a=
ccepted by the KROHNE Company =0D
for any loss or damage arising from receipt of this message. =0D
Furthermore, unless explicitly stated, this email is in no way a legally bi=
nding agreement. =0D
The views represented in this email do not necessarily represent those of t=
he corporation. =0D