Please find attached two patches (one for pg_proc.h and another for supporting documentation) for two SQL functions: epoch_to_timestamp(integer) and epoch_to_timestamptz(double precision), which convert from UNIX epoch to the native PostgreSQL timestamp and timestamptz data types. The equivalent SQL code is
create function epoch_to_timestamp(integer)
returns timestamp
language sql as '
select (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)::timestamp
';
create function epoch_to_timestamptz(double precision)
returns timestamptz
language sql as '
select (\'epoch\'::timestamp + $1 * \'1 second\'::interval) at time zone \'UTC\'
';
Some very simple tests (all should return TRUE):
test=# select epoch_to_timestamp(extract(epoch from current_timestamp)::integer) = current_timestamp::timestamp(0);
?column?
----------
t
(1 row)
test=# select epoch_to_timestamptz(extract(epoch from current_timestamp)::integer) = current_timestamp(0);
?column?
----------
t
(1 row)
test=# select epoch_to_timestamptz(extract(epoch from current_timestamp)) = current_timestamp;
?column?
----------
t
(1 row)
If regression tests are desired, I'll work some up. Any feedback appreciated.
Michael Glaesemann grzm myrealbox com
func.sgml.diff
Description: Binary data
Description: Binary data---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
