2009-06-11MySQL FROM_UNIXTIME and UNIX_TIMESTAMP functions in PostgreSQL

Personally I prefer PostgreSQL over MySQL DBMS. However there are some things I miss in PostgreSQL. Here is an advice on how to use MySQL FROM_UNIXTIME and UNIX_TIMESTAMP function in PostgreSQL DBMS.

FROM_UNIXTIME()

MySQL query:

mysql> SELECT FROM_UNIXTIME(123456789);

PostgreSQL equivalent:

postgres=> SELECT to_timestamp(123456789); -- with time zone
postgres=> SELECT to_timestamp(123456789)::timestamp; -- without time zone

In order to be able to run MySQL style query in PostgreSQL DBMS, you need to create this function:

CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp AS '
	 SELECT to_timestamp($1)::timestamp AS result
' LANGUAGE 'SQL';

then you can run query like this (exactly the same as in MySQL):

postgres=> SELECT FROM_UNIXTIME(123456789);
    from_unixtime
---------------------
 1973-11-29 21:33:09
(1 row)

You can probably find (on the Internet) definitions with keyword abstime (i.e. $1::abstime::timestamp without time zone AS result).
This keyword is obsolete as described here, so IMHO it’s better to use the definition you see above.

UNIX_TIMESTAMP()

MySQL queries:

mysql> SELECT UNIX_TIMESTAMP();
mysql> SELECT UNIX_TIMESTAMP('1973-11-29 21:33:09');

PostgreSQL equivalents:

postgres=> SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0));
postgres=> SELECT EXTRACT(EPOCH FROM TIMESTAMP '1973-11-29 21:33:09');

Again – in order to be able to run MySQL style query in PostgreSQL DBMS, you need to create 3 functions:

-- no params
CREATE OR REPLACE FUNCTION unix_timestamp() RETURNS bigint AS '
	SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))::bigint AS result;
' LANGUAGE 'SQL';
 
-- timestamp without time zone (i.e. 1973-11-29 21:33:09)
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp) RETURNS bigint AS '
	SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
' LANGUAGE 'SQL';
 
-- timestamp with time zone (i.e. 1973-11-29 21:33:09+01)
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp WITH time zone) RETURNS bigint AS '
	SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
' LANGUAGE 'SQL';

As noted here, prior to PostgreSQL 7.3, writing just timestamp was equivalent to timestamp with time zone. This was changed for SQL compliance.

Now you can run queries like this:

postgres=> SELECT UNIX_TIMESTAMP();
postgres=> SELECT UNIX_TIMESTAMP('1973-11-29 21:33:09');
postgres=> SELECT UNIX_TIMESTAMP('1973-11-29 21:33:09' AT TIME ZONE 'GMT');
postgres=> SELECT UNIX_TIMESTAMP('1973-11-29 21:33:09+01');

Tested on: PostgreSQL 8.1.11 (debian), PostgreSQL 8.2.7 (gentoo) and MySQL 5.0.70 (gentoo)

Filed under: Howto, ,

One Response to “MySQL FROM_UNIXTIME and UNIX_TIMESTAMP functions in PostgreSQL”

  1. postgresql version of mysql UNIX_TIMESTAMP() « Different.In a good way says:

    [...] MySQL from_unixtime and unix_timestamp [...]

    September 24th, 2009 at 15:37

Leave a Reply

»