The following bug has been logged online:

Bug reference: 1757
Logged by: Steve Bennett
Email address: S.Bennett (AT) lancaster (DOT) ac.uk
PostgreSQL version: 8.0.3
Operating system: Linux
Description: timestamp 'epoch' is not absolute
Details:

I'm seeing a rather annoying problem converting timestamps between unix and
PostgreSQL. It appears that the timestamp 'epoch' is being interpreted as
relative to the local timezone, rather than being an absolute time.

e.g. I'm in the UK, and at the moment we're in summer time, which is UTC+1.
If I do:
select date_part('epoch', timestamp 'epoch');
I get:
date_part
-----------
-3600

Whereas if I do:
select date_part('epoch', timestamp 'epoch' AT TIME ZONE 'UTC');
I get:
date_part
-----------
0
Am I misunderstanding what's going on here?
Is there any circumstance where 'epoch' should vary according to the
timezone that you happen to be in?

Thanks in advance

Steve.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

"Steve Bennett" <S.Bennett (AT) lancaster (DOT) ac.uk> writes:
This is correct for timestamp ... you want timestamp with time zone.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Bennett, Steve" <s.bennett (AT) lancaster (DOT) ac.uk> writes:
Sorry, but you're out of luck on that. A timestamp without time zone
cannot represent any absolute time at all --- assuming that it does
amounts to ascribing a timezone to it, which we don't do in general.

Perhaps it would make more sense to refuse the "epoch" keyword in the
context of timestamp without timezone ...

You might want to call this to_timestamp(), since that's what it's going
to be called in 8.1 ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

=20
Sorry, but that's dumb (IMHO). The unix epoch is not relative to the
local timezone.

I'm now using a function to convert from unix times to timestamps, since
the alternative is ugly and verbose even by SQL standards...

create function epoch(integer)
returns timestamp with time zone
as 'select timestamp with time zone \'epoch\'
+ interval \'1 second\' * $1;'
language sql immutable;

Is there a better way?

Steve.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly