PostgreSQL Bugs - BUG #1757: timestamp 'epoch' is not absolute

Author

BUG #1757: timestamp 'epoch' is not absolute

Steve Bennett

2005-07-07, 11:24 am

The following bug has been logged online:

Bug reference: 1757
Logged by: Steve Bennett
Email address: S.Bennett@lancaster.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

Tom Lane

2005-07-07, 11:24 am

"Steve Bennett" <S.Bennett@lancaster.ac.uk> writes:
> It appears that the timestamp 'epoch' is being interpreted as
> relative to the local timezone, rather than being an absolute time.

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@postgresql
.org)

Tom Lane

2005-07-07, 8:29 pm

"Bennett, Steve" <s.bennett@lancaster.ac.uk> writes:
[color=darkred]
> Sorry, but that's dumb (IMHO). The unix epoch is not relative to the
> local timezone.

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 ...

> 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?

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@postgresql
.org)

Bennett, Steve

2005-07-08, 9:24 am

>
> This is correct for timestamp ... you want timestamp with time zone.

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@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links