Thread: reverse of extract epoch?

I happen to like using epoch-based timestamps. Given that, I make heavy use
of the following function as a default value in several places:

CREATE FUNCTION EPOCHNOW () RETURNS int
AS 'SELECT (EXTRACT (epoch FROM NOW()))::int'
LANGUAGE SQL;

Now I want to create its reverse so I can get a timestamp (with tz) from an
epoch-based integer. Is there some simple way of doing it? Right now I have
in place the following:

CREATE FUNCTION EPOCHtoTS (int) RETURNS timestamp
AS 'SELECT NOW() + (($1 - (EXTRACT (epoch FROM NOW()))::int) || \' seconds\')::interval'
LANGUAGE SQL;

It has to be easier than that, right? Realistically, if I can convert
from epoch to timestamp simply then I will move over to having things
stored in the db as timestamps and only use epoch-based times
programmatically.

--Greg

On Sun, 2 Jun 2002 21:17:50 -0400
"Gregory Seidman" <gss+pg@cs.brown.edu> wrote:
> Now I want to create its reverse so I can get a timestamp (with tz) from an
> epoch-based integer. Is there some simple way of doing it?

Try:

select "timestamp"(extract(epoch from now())::int);

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Neil Conway sez:
} On Sun, 2 Jun 2002 21:17:50 -0400
} "Gregory Seidman" <gss+pg@cs.brown.edu> wrote:
} > Now I want to create its reverse so I can get a timestamp (with tz) from an
} > epoch-based integer. Is there some simple way of doing it?
}
} Try:
}
} select "timestamp"(extract(epoch from now())::int);

Yowza! It's been there all along! I actually wanted timestamptz(), as it
turns out, but I found that easily. Thank you!

} Cheers,
}
} Neil
--Greg