How to convert unix_timestamp interger in psql?

Question

How to convert a unix_timestamp integer to time in readable text.
Such as:

timestamp|integer|not null default 0

select timestamp from table limit 1; 
1541001600

1541001600 should be converted to Thu Nov 1 00:00:00 CST 2018

PostgreSQL 8.0.2

Are you really still using Postgres 8.0? That has been unsupported for ages. What does `select version();` show you? — , Feb 12 '19 at 09:50

krithikaGopalakrishnan · Answer 1 · 2019-02-12T09:59:14.807

1

You can use to_timestamp() in postgresql.

select to_timestamp(1541001600) AT TIME ZONE 'CST';

Refer Link for more details

For 8.0:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1541001600 * INTERVAL '1 second';

edited Feb 12 '19 at 09:59

answered Feb 12 '19 at 09:49

It does not work. The error information is as follows ERROR: function to_timestamp(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. – Cow Feb 12 '19 at 09:55
Documentation says function accepts double type, maybe try `to_timestamp(1541001600.0)`? – Ulrich Thomas Gabor Feb 12 '19 at 10:01
For 8.0, the to_timestamp(double precision) itself is unsupported. – krithikaGopalakrishnan Feb 12 '19 at 10:03

score 0 · Answer 2 · answered Feb 12 '19 at 12:02

0

You should really upgrade your Postgres! But I think this will work:

select '1970-01-01'::timestamp + 1541001600 * interval '1 second'

answered Feb 12 '19 at 12:02

Gordon Linoff

2 Answers2