Thread: BUG #1757: timestamp 'epoch' is not absolute

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.

"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

"Bennett, Steve" <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.

> 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

=20
> > It appears that the timestamp 'epoch' is being interpreted as
> > relative to the local timezone, rather than being an absolute time.
>=20
> 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.