Différence entre les horodatages avec / sans fuseau horaire dans PostgreSQL

Les différences sont traitées dans la documentation de PostgreSQL pour les types date / heure . Oui, le traitement de TIME ou TIMESTAMP diffère entre un WITH TIME ZONE ou WITHOUT TIME ZONE . Cela n’affecte pas la façon dont les valeurs sont stockées; cela affecte leur interprétation.

Les effets des fuseaux horaires sur ces types de données sont traités spécifiquement dans les documents. La différence provient de ce que le système peut raisonnablement savoir sur la valeur:

• Avec un fuseau horaire faisant partie de la valeur, la valeur peut être affichée sous la forme d’une heure locale dans le client.

• Sans fuseau horaire dans la valeur, le fuseau horaire par défaut évident est UTC, il est donc rendu pour ce fuseau horaire.

Le comportement diffère selon au moins trois facteurs:

• Le paramètre de fuseau horaire dans le client.
• Le type de données (c’est-à-dire WITH TIME ZONE ou WITHOUT TIME ZONE ) de la valeur.
• Indique si la valeur est spécifiée avec un fuseau horaire particulier.

Voici des exemples couvrant les combinaisons de ces facteurs:

 foo=> SET TIMEZONE TO 'Japan'; SET foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 00:00:00+09 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 06:00:00+09 (1 row) foo=> SET TIMEZONE TO 'Australia/Melbourne'; SET foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 00:00:00+11 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 08:00:00+11 (1 row) 

J’essaie de l’expliquer de manière plus compréhensible que la documentation PostgreSQL.

Ni les variantes de TIMESTAMP stockent un fuseau horaire (ou un décalage), malgré ce que les noms suggèrent. La différence réside dans l’interprétation des données stockées (et dans l’application prévue), et non dans le format de stockage lui-même:

• TIMESTAMP WITHOUT TIME ZONE stocke la date et l’heure locales (alias la date du calendrier mural et l’heure de l’horloge murale). Son fuseau horaire n’est pas spécifié dans la mesure où PostgreSQL peut le savoir (bien que votre application puisse savoir ce que c’est). Par conséquent, PostgreSQL ™ ne fait aucune conversion de zone horaire en entrée ou en sortie. Si la valeur a été entrée dans la firebase database en tant que '2011-07-01 06:30:30' , alors peu '2011-07-01 06:30:30' fuseau horaire sur lequel vous l’afficherez plus tard, l’année 2011, le mois 07, le jour 01, 06 heures, 30 minutes et 30 secondes (dans un certain format). En outre, tout décalage ou fuseau horaire que vous spécifiez dans l’entrée est ignoré par PostgreSQL, par conséquent, '2011-07-01 06:30:30+00' et '2011-07-01 06:30:30+05' sont identiques comme juste '2011-07-01 06:30:30' . Pour les développeurs Java: cela java.time.LocalDateTime à java.time.LocalDateTime .

• TIMESTAMP WITH TIME ZONE enregistre un point sur la ligne de temps UTC. À quoi cela ressemble (combien d’heures, de minutes, etc.) dépend de votre fuseau horaire, mais il fait toujours référence au même instant «physique» (comme le moment d’un événement physique réel). L’entrée est convertie en interne en UTC et c’est ainsi qu’elle est stockée. Pour cela, le décalage de l’entrée doit être connu, donc lorsque l’entrée ne contient pas de décalage ou de fuseau horaire explicite (comme '2011-07-01 06:30:30' ), elle est supposée être dans le fuseau horaire actuel de PostgreSQL session, sinon le décalage ou le fuseau horaire explicitement spécifié est utilisé (comme dans '2011-07-01 06:30:30+05' ). La sortie est affichée convertie dans le fuseau horaire actuel de la session PostgreSQL. Pour les développeurs Java: c’est similaire à java.time.Instant (avec une résolution inférieure), mais avec JDBC et JPA 2.2, vous êtes censé le mapper avec java.time.OffsetDateTime (ou avec java.util.Date ou java.sql.Timestamp bien sûr).

Certains disent que les deux variantes TIMESTAMP stockent la date et l’heure UTC. En quelque sorte, mais c’est déroutant de le dire de cette façon à mon avis. TIMESTAMP WITHOUT TIME ZONE est stocké comme un TIMESTAMP WITH TIME ZONE , qui est affiché avec le fuseau horaire UTC pour donner la même année, le mois, le jour, les heures, les minutes, les secondes et les microsecondes que dans la date-heure locale. Mais il ne s’agit pas de représenter le sharepoint la ligne temporelle que l’interprétation UTC dit, c’est simplement la manière dont les champs date-heure locaux sont codés. (C’est un groupe de points sur la ligne de temps, car le fuseau horaire réel n’est pas UTC; nous ne soaps pas ce que c’est.)

Voici un exemple qui devrait aider. Si vous avez un horodatage avec un fuseau horaire, vous pouvez convertir cet horodatage en n’importe quel autre fuseau horaire. Si vous n’avez pas de fuseau horaire de base, il ne sera pas converti correctement.

 SELECT now(), now()::timestamp, now() AT TIME ZONE 'CST', now()::timestamp AT TIME ZONE 'CST' 

 SELECT MAX('2017-07-06 12:20:48.446+00') - MIN('2017-06-06 12:20:48.446+00') as time_to_take from table_name