Thread: int4 -> unix timestamp -> sql timestamp; abstime?
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
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> 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!