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

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

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.

"Bennett, Steve" <s(dot)bennett(at)lancaster(dot)ac(dot)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