Re: [SQL] Porting from mysql to psql (UNIX_TIMESTAMP()?)

Zlatko Calusic <zlatko(at)iskon(dot)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