Thread: How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
"Bruno BAGUETTE"
Date:
Hello,
I'm looking for a way to convert a unix timestamp to a PostgreSQL date
without using ::abstime which seems to be deprecated.
Currently, I do that query :
levure=> select 1063147331.843::int4::abstime;
abstime
------------------------
2003-09-10 00:42:12+02
(1 row)
What can I use to replace the abstime type in ?
Thanks in advance :-)
---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net
Re: How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
Bruno Wolff III
Date:
On Wed, Sep 10, 2003 at 01:47:20 +0200, Bruno BAGUETTE <pgsql-ml@baguette.net> wrote: > > I'm looking for a way to convert a unix timestamp to a PostgreSQL date > without using ::abstime which seems to be deprecated. There are other ways to do it, but based on comments from developers I have seen in the past, abstime isn't going away any time soon. One other approach would be to multiply a 1 second interval by the timestamp and add it to a timestamp corresponding to the unix epoch (00:00:00 on January 1, 1970 if I remember correctly). But your current method is probably going to be faster.
Re: How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
Tom Lane
Date:
"Bruno BAGUETTE" <pgsql-ml@baguette.net> writes:
> I'm looking for a way to convert a unix timestamp to a PostgreSQL date
> without using ::abstime which seems to be deprecated.
abstime is not yet deprecated in my mind, precisely because it's still
the easiest way to do the reverse of "extract(epoch from timestamp)".
The cleanest alternative I know of is
select 'epoch'::timestamptz + (unixtimestamphere) * '1 sec'::interval;
but this still leaves an unsatisfied feeling. Sooner or later we'll
probably invent an explicit function to do this conversion.
regards, tom lane
RE : How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
"Bruno BAGUETTE"
Date:
> Sooner or later we'll probably invent an explicit function to do this > conversion. I may be wrong but why create a new fonction for unixtimestamp<-->date conversion ? I think that the to_date() function is the best place to add this kind of conversion. Isn't it better to add a new value for the second parameter of the to_date function ? Regards, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
RE : How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
"Bruno BAGUETTE"
Date:
> Sooner or later we'll probably invent an explicit function to do this > conversion. I may be wrong but why create a new fonction for unixtimestamp<-->date conversion ? I think that the to_date() function is the best place to add this kind of conversion. Isn't it better to add a new value for the second parameter of the to_date function ? Regards, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Re: RE : How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
From
Tom Lane
Date:
"Bruno BAGUETTE" <pgsql-ml@baguette.net> writes:
>> Sooner or later we'll probably invent an explicit function to do this
>> conversion.
> I may be wrong but why create a new fonction for unixtimestamp<-->date
> conversion ?
> I think that the to_date() function is the best place to add this kind
> of conversion.
to_date doesn't seem particularly appropriate. There's no use for a
format string in this context, and there isn't any variant of to_date
that accepts an integer as input anyway.
regards, tom lane