Hello all.
In IRC, one of the questions we get from time to time is how to convert
UNIX epoch to PostgreSQL timestamp. Users are often surprised there
isn't a builtin cast or function that does this.
I've put together two simple SQL functions that accomplish this. I
think they would make the epoch-to-timestamp converstion more
convenient. I don't know if they would be more efficient if written in
C.
create or replace function epoch_to_timestamp( integer ) returns timestamp(0) language sql as ' SELECT
''epoch''::timestamp+ $1 * ''1 second''::interval; ';
create or replace function epoch_to_timestamptz( double precision ) returns timestamptz language sql as '
SELECT(''epoch''::timestamp + $1 * ''1 second''::interval) at time
zone ''UTC''; ';
The first function, epoch_to_timestamp, conforms to the normal
definition of epoch, which is integer seconds. It returns timestamp at
UTC. The second function, epoch_to_timestamptz, provides an inverse to
extract(epoch from timestamp), which returns double precision.
I'm wondering if this wouldn't be better as cast rather than explicit
functions. I'm interested in hearing people's thoughts, whether or not
these would be useful additions to PostgreSQL and whether this
particular implementation is appropriate. If it is, I'll write up some
docs for the appropriate section, with any modifications people might
suggest.
Thank you for your time.
Michael Glaesemann
grzm myrealbox com