Re: Porting from mysql to psql (UNIX_TIMESTAMP()?) - Mailing list pgsql-sql

Zlatko Calusic <zlatko@iskon.hr> writes:
> Is there any similar functionality (returning unixish number of
> seconds since 1970 from the timestamp field) in PostgreSQL?

Sure.  You can use date_part, or cast to abstime and thence to integer:

regression=# select now();
          now
------------------------
 2000-09-09 12:55:50-04
(1 row)

regression=# select date_part('epoch',now());
 date_part
-----------
 968518563
(1 row)

regression=# select now()::abstime::int4;
 ?column?
-----------
 968518585
(1 row)

To go the other way (integer seconds to timestamp), use the cast
method in reverse:

regression=# select 968518585 :: int4 :: abstime :: timestamp;
        ?column?
------------------------
 2000-09-09 12:56:25-04
(1 row)

(there's probably a cleaner way to do this, but that works ...)

            regards, tom lane