13

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?

31

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:

  • Second snippet works also on RedShift. – Gianluca Casati Apr 28 '16 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.