471,049 Members | 1,465 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?

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 - pg******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #1
3 3428
On Wed, Sep 10, 2003 at 01:47:20 +0200,
Bruno BAGUETTE <pg******@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2
"Bruno BAGUETTE" <pg******@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

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

Nov 11 '05 #3
> 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 - pg******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mage | last post: by
29 posts views Thread by Paul Ganainm | last post: by
12 posts views Thread by DC Gringo | last post: by
reply views Thread by Jean-Michel POURE | last post: by
10 posts views Thread by bonnie.tangyn | last post: by
3 posts views Thread by Aggelos | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.