Forums PostgreSQL.fr

Jean-Marie

Membre

prendre en compte un time zone spécifique

Bonjour

je fais actuellement des requêtes sur des dates en UTC
je souhaiterais pouvoir spécifier un time zone différent au niveau du format de sortie

par exemple j'ai une requête du genre :

select to_char(date,'dd/mm/YYYY HH24:MI:SS'), to_char(x(coord),'FM990D999'), to_char(y(coord),'FM990D999') ...where date between '2012-01-26 12:15:18' and '2012-01-26 14:15:18'...

j'obtiens :
26/01/2012 13:39:15 | 13.485  | 52.276

le time zone est par défaut en UTC

je voudrais pouvoir afficher cette date dans un time zone différent de UTC (exemple CET) ce qui donnerait

26/01/2012 14:39:15 | 13.485  | 52.276

quelle la manière la plus simple de m'y prendre sachant que le time zone est fourni par une requête http
et varie suivant le pays d'où provient cette requête

merci beaucoup

Jean-Marie

Marc Cousin

Membre

Re : prendre en compte un time zone spécifique

En fait, avant de faire le to_char, il faut passer la date dans la bonne timezone. Par exemple:

marc=# SELECT now();   
              now             
-------------------------------
2012-01-26 17:10:46.698873+01
(1 row)

marc=# SELECT now() at time zone 'MST';
          timezone         
----------------------------
2012-01-26 09:10:47.871987
(1 row)

On peut donc faire un to_char dessus:

marc=# SELECT to_char(now() at time zone 'MST','dd/mm/YYYY HH24:MI:SS');
       to_char       
---------------------
26/01/2012 09:11:24

Pour la liste des timezones:

SELECT * from pg_timezone_names ;

(MST c'est le Mountain Standard Time…)

---

Marc.

Marc Cousin

Membre

Re : prendre en compte un time zone spécifique

Au passage, il faut que le temps de départ ait une timezone pour que la conversion se passe bien…

---

Marc.

Jean-Marie

Membre

Re : prendre en compte un time zone spécifique

merci pour ces indications

mais apparemment j'ai le souci suivant :
quand j'utilise le time zone CET ou "Europe/Paris" j'obtiens 1 heure de - qu'en UTC (au lieu d'1 heure de plus)

ainsi

SELECT to_char(date at time zone 'CET','dd/mm/YYYY HH24:MI:SS'), to_char(x(coord),'FM990D999'), to_char(y(coord),'FM990D999') ...where date between '2012-01-26 12:15:18' and '2012-01-26 14:15:18'...

me donne

26/01/2012 12:39:15 | 13.485  | 52.276

dans le cas où je remplace CET par UTC+1, j'obtiens pourtant le résultat attendu soit :

26/01/2012 14:39:15 | 13.485  | 52.276

je précise que mes dates de début et de fin '2012-01-26 12:15:18' et '2012-01-26 14:15:18' sont en UTC

peut-être le problème vient-il de là

vous avez dit qu'il faut que le temps de départ ait un time zone

comment puis-je préciser cette information

merci d'avance

Jean-Marie

dverite

Membre

Re : prendre en compte un time zone spécifique

dans le cas où je remplace CET par UTC+1, j'obtiens pourtant le résultat attendu soit :

26/01/2012 14:39:15 | 13.485  | 52.276

C'est à mon avis un problème d'interprétation de ce que désigne réellement UTC+1.
Voir ce passage de la doc de postgresql:
http://www.postgresql.org/docs/9.1/stat … etime.html

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

Donc UTC+1 dans ce contexte bien précis ne désignerait pas l'heure d'hiver à Paris, mais un fuseau de l'autre côté du méridien de Greenwich, pas du tout équivalent à CET.

vous avez dit qu'il faut que le temps de départ ait un time zone

comment puis-je préciser cette information

Si je comprends la situation de départ, la colonne date serait du type timestamp without time zone, et la timezone de la session postgres serait UTC.
Il faut voir que:
1) AT TIME ZONE 'zone' appliqué à un timestamp without time zone produit un timestamp with time zone.
2) tout timestamp with time zone est présenté à l'utilisateur en étant converti au vol dans le fuseau horaire de la session.

Donc, SELECT date AT TIME ZONE 'CET' va certes convertir la valeur de date dans le fuseau CET mais le reconvertir aussitôt  dans le fuseau UTC avant présentation à l'utilisateur.

Si je ne trompe pas dans le raisonnement, le résultat recherché s'obtiendrait plutôt par:
select ((date at time zone 'UTC') at time zone 'CET')
Peut-être aussi que 'Europe/Paris' serait un meilleur choix que CET pour être valide toute l'année.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Marc Cousin

Membre

Re : prendre en compte un time zone spécifique

Pour commencer, le champ «date» est un timestamp avec ou sans timezone ? On peut voir la définition de la table ?

---

Marc.

Jean-Marie

Membre

Re : prendre en compte un time zone spécifique

le chanp date est un timestamp sans time zone :

Column  |            Type                         | Modifiers
--------------------------------------------------------------
date       | timestamp without time zone | not null
...

Marc Cousin

Membre

Re : prendre en compte un time zone spécifique

Ok. le AT TIME ZONE n'a pas le même sens sur une donnée sans timezone. Pour une donnée sans time zone, il retourne une donnée avec timezone, en considérant que la donnée sans timezone est dans la timezone indiquée (comme expliqué par dverite plus haut). Je redétaille un peu plus…

Par exemple:
SELECT '2012-01-30 15:04:40.514083'::timestamp without time zone at time zone 'CET';
           timezone           
-------------------------------
2012-01-30 15:04:40.514083+01

(Normal, c'est la timezone dans laquelle je suis…)

SELECT '2012-01-30 15:04:40.514083'::timestamp without time zone at time zone 'MST';
           timezone           
-------------------------------
2012-01-30 23:04:40.514083+01

(le timestamp est indiqué comme étant en Mountain, donc à l'affichage, puisque PostgreSQL me l'affiche dans mon timestamp local, ça devient 23:04 +1)

Après, on peut appliquer la notation AT TIME ZONE sur le timestamp avec time zone qu'on vient de récupérer:

SELECT '2012-01-30 15:04:40.514083'::timestamp without time zone at time zone 'CET' at time zone 'MST';
          timezone         
----------------------------
2012-01-30 07:04:40.514083
(1 ligne)

=> 15:04 CET (temps standard en france, avec les passages heure d'été/heure d'hiver) correspond à 7:04 le même jour en MST. Il suffit ensuite de rajouter du formatage…

Le plus simple aurait été, évidemment, de stocker les données directement avec la timezone… Parce que là, on a une ambiguité au passage à l'heure d'hiver (de quel 1h30 du matin parle-t-on ?)

---

Marc.

Jean-Marie

Membre

Re : prendre en compte un time zone spécifique

Bonjour

à priori la solution la plus intéressante est d'utiliser la syntaxe que vous avez mentionné

select ((date at time zone 'UTC') at time zone 'TIMEZONE')

merci pour votre précieuse contribution

Jean-Marie

Marc Cousin

Membre

Re : prendre en compte un time zone spécifique

Oui. Toutefois, si c'est encore possible, passez le champ en timestamp avec time zone, ça permet de résoudre beaucoup de problèmes sur les dates, sans effort.

---

Marc.