The following bug has been logged on the website:

Bug reference: 6424
Logged by: Olaf
Email address: o.bousche (AT) krohne (DOT) com
PostgreSQL version: 9.1.2
Operating system: Windows 7 - 64bits
Description:

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?


--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

o.bousche (AT) krohne (DOT) com writes:
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...1/msg00649.php
but I don't believe we have any consensus yet about whether that
would be a good idea.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Feb 1, 2012 at 10:00 AM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
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=# 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=# 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Merlin Moncure <mmoncure (AT) gmail (DOT) com> writes:
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

--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thank you for the fast reply

I was not sure this was some spurious result messing up my calculations.
If it is by design and consistent it does not present any problems for
me.

For me the matter is closed.

Olaf



-----Original Message-----
From: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]
Sent: Wednesday, 01 February, 2012 17:00
To: Bousche, Olaf
Cc: pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] BUG #6424: Possible error in time to seconds
conversion

o.bousche (AT) krohne (DOT) com writes:
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...1/msg00649.php
but I don't believe we have any consensus yet about whether that would
be a good idea.

regards, tom lane


This message has been scanned for malware by Websense Hosted Email
Security.
====================================== DISCLAIMER ===================================
NOTE: The information transmitted in this email is for the person or entityto which it is addressed:
it may contain information that is confidential and/or legally privileged.
If you are not the intended recipient, please do not read, use, retransmit or disseminate this information.
Although this email and any attachments are believed to be free of any virus, it is the responsibility
of the recipient to ensure that they are virus free. No responsibility is accepted by the KROHNE Company
for any loss or damage arising from receipt of this message.
Furthermore, unless explicitly stated, this email is in no way a legally binding agreement.
The views represented in this email do not necessarily represent those of the corporation.


--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs