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
