sql - How to convert Unix epoch to a timestamp

Question

I am new to postgresql bot not to sql in general. I have a table that I need to read values from, on of the columns is a unix timestamp that I want to convert in to a more human readable format thus I found this:

SELECT lt,dw,up,to_char(uxts, 'YYYY-MM-DD HH24:MI:SS') 
from products;

But that produces an error: ERROR: multiple decimal points

I am lost here. I am sure someone can show me how to do it. The documentation isn't that clear to me. Postgresql 9.5 is the database.

Answer 1

to_char() converts a number, date or timestamp to a string, not the other way round.

You want to_timestamp()

Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp

---

So just apply that function on your column

SELECT lt,dw,up,to_timestamp(uxts) as uxts 
from products;

This assumes that uxts is some kind of number data type (integer, bigint or double precision)