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

[Roman Neuhauser]

Hello,

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?

--
FreeBSD 4.10-STABLE
12:01AM up 15:39, 7 users, load averages: 0.08, 0.04, 0.01

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Michael Glaesemann]

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>

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Roman Neuhauser]

# [email]tgl@sss.pgh.pa.us[/email] / 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[David Fetter]

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 [email]david@fetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

[Roman Neuhauser]

# [email]tgl@sss.pgh.pa.us[/email] / 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[David Fetter]

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 [email]david@fetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])