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