How to cast bigint to timestamp with time zone in postgres in an update

Question

Is there a way to cast a BIGINT to TIMESTAMP or TIMESTAMP WITH TIME ZONE in Postgres? I need to copy the data from a BIGINT column to the TIMESTAMP column.

Here is what I have tried:

update table
set date__timestamp = date__bigint::timestamp 
where foo = 1;

ERROR: cannot cast type bigint to timestamp without time zone

I changed the timestamp column to a with timezone column and tried this:

update table
set date__timestamp = date__bigint::timestamp with time zone at time zone 'PST' 
where foo = 1;

ERROR: cannot cast type bigint to timestamp with time zone

update table
set date__timstamp = TO_CHAR(TO_TIMESTAMP(date__bi / 1000), 'DD/MM/YYYY HH24:MI:SS')
where foo = 1;

ERROR: column "date__timestamp" is of type timestamp without time zone but expression is of type text Hint: You will need to rewrite or cast the expression.

The data looks like this.

date_bigint: 20181102
date_timestamp: 2018-11-02 17:00:00.000000

Do I need to pass default values to the casting?

Solution 1

You can cast it to text and use TO_TIMESTAMP and the convert to timestamp at time zone

SELECT 
to_timestamp ( '20181102'::bigint::text,'YYYYMMDD')::timestamp at time zone 'UTC' 
at time zone 'PST' ;
update t
   set date__timestamp = TO_TIMESTAMP(date_bigint::text,'YYYYMMDD')::timestamp 
   at time zone 'UTC' at time zone 'PST'
where foo = 1;

Demo

Solution 2

This answer assumes that the date__bigint column is storing a UNIX timestamp in seconds since the epoch. Here is one way to convert to a Postgres timestamp:

UPDATE your_table
SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second'
WHERE foo = 1;

That is, we can add some number of seconds to the epoch timestamp to convert your value to a formal Postgres timestamp.

Solution 3

The simpler approach will be to change from bigint to varchar then varchar to timestamp

alter table tablename alter column colname type varchar(30) USING colname::varchar;

then

alter table tablename alter column colname type timestamp USING colname::timestamp;