How to add unix_timestamp() and from_unixtime() to PostgreSQL?

March 10th, 2006 in Programming, SQL, Web

CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp AS '
SELECT
$1::abstime::timestamp without time zone AS result
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION unix_timestamp() RETURNS integer AS '
SELECT
ROUND(EXTRACT( EPOCH FROM abstime(now()) ))::int4 AS result;
' LANGUAGE 'SQL';
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS integer AS '
SELECT
ROUND(EXTRACT( EPOCH FROM ABSTIME($1) ))::int4 AS result;
' LANGUAGE 'SQL';
Examples:

SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW());
unix_timestamp | unix_timestamp
—————-+—————-
1141955358 | 1141955358
(1 row)
SELECT FROM_UNIXTIME(1141955358);
from_unixtime
———————
2006-03-10 03:49:18
(1 row)

Amazon.com Widgets