Utilisation de l'heure courante en UTC comme valeur par défaut dans PostgreSQL

Question

J'ai une colonne de la TIMESTAMP WITHOUT TIME ZONE et souhaite que l'heure par défaut soit l'heure actuelle en UTC. Obtenir l'heure actuelle en UTC est facile :

postgres=# select now() at time zone 'utc';
          timezone          
----------------------------
 2013-05-17 12:52:51.337466
(1 row)

Tout comme l'utilisation de l'horodatage actuel pour une colonne :

postgres=# create temporary table test(id int, ts timestamp without time zone default current_timestamp);
CREATE TABLE
postgres=# insert into test values (1) returning ts;
             ts             
----------------------------
 2013-05-17 14:54:33.072725
(1 row)

Mais cela utilise l'heure locale. Essayer de forcer le passage à l'heure UTC entraîne une erreur de syntaxe :

postgres=# create temporary table test(id int, ts timestamp without time zone default now() at time zone 'utc');
ERROR:  syntax error at or near "at"
LINE 1: ...int, ts timestamp without time zone default now() at time zo...

Answer 1

Une fonction n'est même pas nécessaire. Il suffit de mettre des parenthèses autour de l'expression par défaut :

create temporary table test(
    id int, 
    ts timestamp without time zone default (now() at time zone 'utc')
);

Answer 2

Une autre solution encore :

timezone('utc', now())

Answer 3

Enveloppez-le dans une fonction :

create function now_utc() returns timestamp as $$
  select now() at time zone 'utc';
$$ language sql;

create temporary table test(
  id int,
  ts timestamp without time zone default now_utc()
);

Answer 4

Qu'en est-il

now()::timestamp

Si vos autres horodatages ne comportent pas de fuseau horaire, cette distribution donnera le type correspondant "horodatage sans fuseau horaire" pour l'heure actuelle.

J'aimerais cependant savoir ce que les autres pensent de cette option. Je n'ai toujours pas confiance en ma compréhension de cette histoire de fuseau horaire "avec/sans".

EDIT : Ajout du commentaire de Michael Ekoka ici car il clarifie un point important :

Avertissement. La question porte sur la génération de l'horodatage par défaut en UTC pour une colonne d'horodatage qui se trouve ne pas stocker le fuseau horaire (peut-être fuseau horaire (peut-être parce qu'il n'y a pas besoin de stocker le fuseau horaire si vous vos horodatages partagent le même). Votre solution consiste à générer un horodatage local (qui pour la plupart des gens ne sera pas nécessairement nécessairement réglé sur UTC) et de le stocker comme un horodatage naïf (un horodatage qui ne spécifie pas son fuseau horaire).

Answer 5

Il s'agit de deux solutions équivalentes :

(dans le code suivant, vous devez remplacer 'UTC' pour zone y now() pour horodatage )

1. timestamp AT TIME ZONE zone - Conformité aux normes SQL
2. timezone(zone, timestamp) - sans doute plus lisible

La fonction timezone(zone, timestamp) est équivalente à la construction conforme au SQL timestamp AT TIME ZONE zone.

---

Explication :

• zone peut être spécifié soit comme une chaîne de texte (par exemple, 'UTC' ) ou comme un intervalle (par exemple, INTERVAL '-08:00' ) - voici une liste de tous les fuseaux horaires disponibles
• horodatage peut être toute valeur de type horodatage
• now() retourne une valeur de type horodatage (juste ce dont nous avons besoin) avec le fuseau horaire par défaut de votre base de données attaché (par ex. 2018-11-11T12:07:22.3+05:00 ).
• timezone('UTC', now()) transforme notre heure actuelle (de type timestamp avec fuseau horaire ) en l'équivalent sans décalage horaire dans UTC .
  Par exemple, SELECT timestamp with time zone '2020-03-16 15:00:00-05' AT TIME ZONE 'UTC' retournera 2020-03-16T20:00:00Z .

Docs : timezone()