Date PostgreSQL() avec fuseau horaire
j'ai un problème avec la sélection des dates correctement à partir de Postgres - ils sont stockés dans UTC, mais ne pas convertir correctement avec la fonction Date ().
convertir l'horodatage en date me donne la mauvaise date si elle est après 16h PST.
2012-06-21 devrait être 2012-06-20 dans ce cas.
le type de données de la colonne starts_at est timestamp without time zone . Voici mes requêtes:
sans conversion en PST timezone:
Select starts_at from schedules where id = 40;
starts_at
---------------------
2012-06-21 01:00:00
Conversion donne ça:
Select (starts_at at time zone 'pst') from schedules where id = 40;
timezone
------------------------
2012-06-21 02:00:00-07
mais ni l'un ni l'autre ne convertissent à la date correcte dans le fuseau horaire.
3 réponses
Je ne vois pas le exact type de starts_at dans votre question. Vraiment, vous devez inclure cette information, c'est la clé de la solution. Je vais avoir à deviner.
fondamentalement, PostgreSQL toujours stocke la valeur de temps UTC pour le type timestamp with time zone à l'interne. Seul l'affichage varie avec le paramètre timezone . L'effet de la construction AT TIME ZONE change également avec le type de données. Plus de détails:
si vous extrayez un date du type timestamp [without time zone] , vous obtenez la date pour le fuseau horaire actuel. Le jour dans la sortie sera le même que dans l'affichage de la valeur timestamp .
si vous extrayez un date du type timestamp with time zone ( timestamptz en bref), le décalage du fuseau horaire est "appliqué" en premier. Vous obtenez toujours la date pour le fuseau horaire actuel, qui est d'accord avec le affichage de l'horodatage. Le même point dans le temps se traduit par le jour suivant dans certaines parties de l'Europe, quand il est plus de 16 heures en Californie par exemple. Pour obtenir la date pour un certain fuseau horaire, appliquer AT TIME ZONE d'abord.
donc, ce que vous décrivez au début de la question contredit votre exemple.
étant donné que starts_at est un timestamp [without time zone] et que l'heure sur votre serveur est réglée à l'heure locale. Essai avec:
SELECT now();
affiche-t-il la même heure qu'une horloge sur votre mur? Si oui (et que le serveur de la base de données tourne à l'heure correcte), le paramètre timezone de votre session courante est en accord avec votre fuseau horaire local. Si non, vous voudrez peut-être visiter le réglage de timezone dans votre postgresql.conf ou votre client pour la session. détails dans le manuel.
soyez conscient que le timezone offset utilisé le contraire signe de ce qui est affiché dans timestamp littérales. Voir:
pour obtenir votre date locale de starts_at juste
SELECT starts_at::date
équivalant à:
SELECT date(starts_at)
BTW, votre heure locale est à UTC-7 en ce moment, pas UTC-8, parce que l'heure d'été est en vigueur (pas parmi les idées les plus brillantes de la race humaine).
heure normale du Pacifique (HNP) est normalement 8 heures" plus tôt "(plus grande valeur timestamp ) que L'UTC (Universal Time Zone), mais pendant les périodes de clarté (comme maintenant) il peut être de 7 heures. C'est pourquoi timestamptz est affiché comme 2012-06-21 02:00:00 -07 dans votre exemple. La construction AT TIME ZONE 'PST' prend en compte l'heure d'été. Ces deux expressions donnent des résultats différents (un en hiver, un en été) et peuvent donner des dates différentes lors de la coulée:
SELECT '2012-06-21 01:00:00'::timestamp AT TIME ZONE 'PST'
, '2012-12-21 01:00:00'::timestamp AT TIME ZONE 'PST'
En Gros, ce que vous voulez c'est:
$ select starts_at AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' from schedules where id = 40
j'ai obtenu la solution de cet article est ci-dessous, qui est l'or droit!!! Il explique cette question non-triviale très clairement, lui donner une lecture si vous souhaitez mieux comprendre la gestion pstgrsql TZ.
expression des horodateurs PostgreSQL sans zones en heure locale
voilà ce qui se passe. Tout d'abord, vous devez savoir que ' PST timezone est de 8 heures derrière le fuseau horaire de L'UTC, par exemple le 1er janvier 2014, 16h30 HNP (Wed, 01 Jan 2014 16:00:30 -0800) est équivalent au 2 janvier 2014, 00: 30 h TUC (Thu, 02 Jan 2014 00:00:30 +0000). Tout moment après 16h00 dans les feuillets de TVP jusqu'au jour suivant, interprété comme UTC.
aussi, comme Erwin Brandstetter mentionné ci-dessus, potresql a deux types de timestamps type de données, l'un avec un fuseau horaire et l'autre sans. Si vos horodateurs comprennent un fuseau horaire, alors un simple:
$ select starts_at AT TIME ZONE 'US/Pacific' from schedules where id = 40
marchera. Cependant, si votre timestamp n'est pas temporel, l'exécution de la commande ci-dessus ne fonctionnera pas, et vous devez d'abord convertir votre timestamp intemporel en timestamp avec un fuseau horaire, à savoir un fuseau horaire UTC, et ensuite seulement le convertir à votre " PST " ou "US/Pacific" désirés (qui sont les mêmes jusqu'à certains problèmes d'heure d'été). Je pense que vous devez être bien avec soit).
Permettez-moi de démontrer avec un exemple où je crée un timestamp intemporel. Supposons pour la commodité que notre fuseau horaire local est en effet "PST" (si ce n'était pas alors il devient un tout petit peu plus compliqué qui est inutile aux fins de cette explication).
Dire que j'ai:
$ select timestamp '2014-01-2 00:30:00' AS a, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AS b, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST' AS c, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d
Cela donnera:
"a"=>"2014-01-02 00:30:00" (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct 'PST' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"
le dernier horodatage est la raison de toute la confusion concernant la conversion horodatage intemporel de UTC en 'PST' dans postgresql. Quand nous écrivons:
timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d
nous prenons un timestamp intemporel et essayons de le convertir en ' PST TZ (nous supposons indirectement que postgresql comprendra que nous voulons qu'il convertisse le timestamp D'un UTC TZ, mais potresql a ses propres plans!). En pratique, ce que fait postgresql est qu'il prend le timestamp intemporel ('2014-01-2 00:30:00) et le traite comme s'il était déjà un timestamp 'PST' TZ (I. e: 2014-01-2 00:30: 00 -0800) et convertit cela en UTC timezone!!! Donc il le pousse 8 heures à l'avance. au lieu de retour! Ainsi nous obtenons (2014-01-02 08:30:00+00).
quoi qu'il en soit, ce dernier comportement (non intuitif) est la cause de toute confusion. Lire l'article si vous voulez une explication plus approfondie, j'ai effectivement eu des résultats un peu différents de leurs sur cette dernière partie, mais l'idée générale est la même.
je sais que c'est un vieux mais vous pouvez envisager d'utiliser au fuseau horaire" US/Pacific " lors du moulage pour éviter tout problème de PST/PDT. So
sélectionner starts_at:: TIMESTAMPTZ AT TIME ZONE "US / Pacific" à partir des horaires où ID = '40';