Comment convertir un bigint en timestamp avec un fuseau horaire dans postgres lors d'une mise à jour ?

Question

Existe-t-il un moyen de lancer un BIGINT a TIMESTAMP o TIMESTAMP WITH TIME ZONE dans Postgres ? J'ai besoin de copier les données d'un BIGINT à la colonne TIMESTAMP colonne.

Voici ce que j'ai essayé :

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

ERROR : impossible de convertir le type bigint en timestamp sans fuseau horaire

J'ai remplacé la colonne timestamp par une colonne avec fuseau horaire et j'ai essayé ceci :

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

ERROR : impossible de convertir le type bigint en timestamp avec le fuseau horaire

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

ERREUR : la colonne "date__timestamp" est de type timestamp sans fuseau horaire. mais l'expression est de type text Conseil : vous devrez réécrire ou couler l'expression.

Les données ressemblent à ceci.

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

Dois-je transmettre des valeurs par défaut au casting ?

Answer 1

Vous pouvez le convertir en texte et utiliser TO_TIMESTAMP et le convertir en 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;

Démo

Answer 2

Cette réponse suppose que le date__bigint stocke un horodatage UNIX en secondes depuis l'époque. Voici une façon de convertir en un horodatage Postgres :

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

En d'autres termes, nous pouvons ajouter un certain nombre de secondes à l'horodatage de l'époque pour convertir votre valeur en un horodatage Postgres formel.

Answer 3

L'approche la plus simple consistera à passer de bigint à varchar puis de varchar à timestamp

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

entonces

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