How to convert a unix time to PostgreSQL' Timstamp without timezome?

Question

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30)

I have some data in a table which is created like this:

CREATE TABLE "CLOUDDATA"
(
 "CD_Tm_Obs" timestamp without time zone,
 "CD_Avg_Cloud" double precision
)

I want to query the data and get the values for a specific time. My Input will be an Unix Timestamp (i.e. Seconds from 1-Jan 1970)

The only way of converting the unix time to Timestamp I have found is this: select to_timestamp(TRUNC(CAST(1395036000 AS bigint))). But this create a timestamp with timezone. My data is in timestamp without time zone, so I don't get any results.

How to convert a unix time to PostgreSQL's Timstamp without timezome?

Answer 1

Here are a couple of approaches:

I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST().

SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';
SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';

For reference, more information can be found at the following links:

• First query

• Second query

• Gianluca Casati: Second snippet works also on RedShift.