int4 -> unix timestamp -> sql timestamp; abstime?

Roman Neuhauser <neuhauser@chello.cz> writes:

what is the opposite of cast(extract('epoch' from now()) as int)?
The only thing I found that works is
cast(cast(... as abstime) as timestamp)
and the documentation says abstime shouldn't be used, and may
disappear. What should I use instead?

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

Of course you can wrap this up in a function if you prefer (not sure why
we haven't done so already).

regards, tom lane

On Jan 12, 2005, at 8:35, Tom Lane wrote:

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

Of course you can wrap this up in a function if you prefer (not sure
why
we haven't done so already).

It's in the queue for 8.1.

<http://candle.pha.pa.us/mhonarc/patches2/msg00090.html&gt;

Michael Glaesemann
grzm myrealbox com

# tgl@sss.pgh.pa.us / 2005-01-11 18:35:18 -0500:

Roman Neuhauser <neuhauser@chello.cz> writes:

what is the opposite of cast(extract('epoch' from now()) as int)?
The only thing I found that works is
cast(cast(... as abstime) as timestamp)
and the documentation says abstime shouldn't be used, and may
disappear. What should I use instead?

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

Have I missed this in the manual?

Of course you can wrap this up in a function if you prefer (not sure why
we haven't done so already).

I most surely will, and I'm glad to hear this will be included in 8.1.

Thanks for the replies!

--
FreeBSD 4.10-STABLE
1:25AM up 17:04, 9 users, load averages: 0.11, 0.06, 0.01

Roman Neuhauser <neuhauser@chello.cz> writes:

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

Have I missed this in the manual?

The 8.0 docs mention this in the discussion of extract(epoch), but I'm
not sure if it was there before. It's been discussed in the mailing
list archives many times...

regards, tom lane

On Tue, Jan 11, 2005 at 06:35:18PM -0500, Tom Lane wrote:

Roman Neuhauser <neuhauser@chello.cz> writes:

what is the opposite of cast(extract('epoch' from now()) as int)?
The only thing I found that works is
cast(cast(... as abstime) as timestamp)
and the documentation says abstime shouldn't be used, and may
disappear. What should I use instead?

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

I think this should read:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
/* ^^^^^^^^^^^^^^^^^^ */

to conform with the *n*x standard.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David Fetter <david@fetter.org> writes:

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

I think this should read:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
/* ^^^^^^^^^^^^^^^^^^ */

It's correct as written; your modification throws it off by the local GMT
offset.

regards, tom lane

# tgl@sss.pgh.pa.us / 2005-01-11 19:31:19 -0500:

Roman Neuhauser <neuhauser@chello.cz> writes:

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

Have I missed this in the manual?

The 8.0 docs mention this in the discussion of extract(epoch), but I'm
not sure if it was there before. It's been discussed in the mailing
list archives many times...

My searches on google.com and of the postresql.org documentation sets
haven't turned anything up. Any chance of this getting into the 7.4
docs?

--
FreeBSD 4.10-STABLE
1:53AM up 17:31, 9 users, load averages: 0.05, 0.03, 0.00

On Tue, Jan 11, 2005 at 07:44:46PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

The recommended locution is

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + <x> * INTERVAL '1 second';

I think this should read:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' AT TIME ZONE 'UTC' + <x> * INTERVAL '1 second';
/* ^^^^^^^^^^^^^^^^^^ */

It's correct as written; your modification throws it off by the
local GMT offset.

Thanks for the heads-up. I was just about to Do The Wrong Thing(TM)
in a doc patch re: ALTER COLUMN TYPE :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!