Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select TO_TIMESTAMP('20181102'::bigint::text,'YYYYMMDD')::timestamp at time zone 'UTC' at time zone 'PST' ;

timezone

2018-11-01 16:00:00

SELECT 1

create table  t as select 1::int as foo,
   null::timestamp with time zone  as date__timestamp,
 '20181102'::BIGINT  as date_bigint;

SELECT 1

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

UPDATE 1

select * from t;

foo
date__timestamp
date_bigint

1
2018-11-01 16:00:00+00
20181102

SELECT 1