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