FROM_UNIX alternate in PostgreSQL

Question

Guys I have stored unix timestamps in my MySQL database, where I was reading them using the MySQL function FROM_UNIX().

Now I'm migrating the database from MySQL to PostgreSQL. In PostgreSQL, how I can read unix timestamps just like I was doing in MySQL with FROM_UNIX() ?

Answer 1

Taken from the manual:

to_timestamp(double precision) convert Unix epoch to time stamp

If you need parts of the created timestamp, use the extract function

select extract(year from to_timestamp(1284352323))

Answer 2

SELECT to_char(date(to_timestamp(1195374767)),'YYYY-MM-DD');

• to_timestamp - convert to Postgresql timestamp no unix timestamp
• date convert to date type
• to_char format output

http://www.postgresql.org/docs/8.1/static/functions-formatting.html

extract can do the same thing but not in one step