Forums PostgreSQL.fr

David

Membre

Infos sur comportement fonction to_timestamp

Bonjour à tous.
J'ai un soucis sur la compréhension du comportement de la fonction to_timestamp.
Si la variable proposée au formatage est erronée, d'une part la fonction ne retourne pas d'erreur mais elle retourne une valeur qui peut sembler aléatoire (mais qui correspondrait tout de même à une certaine logique ...).
En tout cas ceci est assez pénalisant dans le cadre du portage de nos applications car les fonctions analogues sous Oracle génèrent obligatoirement une erreur.
Avons-nous oublié un paramètre de configuration niveau serveur ?
David.

***********************************************************************************************************
Exemple 1 : test de formatage sur 3 versions PG différentes avec la même date suffixée par des '00' parasites

psql (8.4.5)
bdcp=>  select to_timestamp('2011070500','YYYYMMDD');
      to_timestamp
------------------------
2012-11-11 00:00:00+00
(1 row)

Welcome to psql 8.2.6, the PostgreSQL interactive terminal.
syn=> select to_timestamp('2011070500','YYYYMMDD');
      to_timestamp     
------------------------
2011-07-05 00:00:00+00

psql (9.0.2)
syn=> select to_timestamp('2011070500','YYYYMMDD');
      to_timestamp     
------------------------
2012-11-11 00:00:00+00
(1 row)

***********************************************************************************************************
Exemple 2 : test de formatage avec un mois erroné  (mois=13)

bdcp=>  select to_timestamp('20111302','YYYYMMDD');
      to_timestamp
------------------------
2012-01-02 00:00:00+00
(1 row)

***********************************************************************************************************
Exemple 3 : test de formatage avec date tronquée

bdcp=> select to_timestamp('200015','YYYYMMDD');
      to_timestamp
------------------------
2001-03-03 00:00:00+00
(1 row)

Bref pas mal de combinaisons possibles ....
Merci pour vos éclaircissements.

arthurr

Membre

Re : Infos sur comportement fonction to_timestamp

Etrange qu'il n'affiche pas d'erreur, inversement, sa réponse correspondre à une logique :

select to_timestamp('2011070500','YYYYMMDD');
2011 - 07 - 0 + 500 jours  : on ne doit pas etre loin du 2012-11-11

select to_timestamp('20111302','YYYYMMDD');
2011 - 12 + 1 - 02 = 2012-01-02

Dernière modification par arthurr (12/07/2011 15:37:31)

gleu

Administrateur

Re : Infos sur comportement fonction to_timestamp

Vous fournissez à PostgreSQL une chaîne de caractères à to_timestamp et cette fonction sera à deviner la date suivant la spécification du format, et il renvoie le résultat sous forme de timestamp. Mais le point important ici est qu'il essaie d'analyser la chaîne. Il évitera de renvoyer une erreur et cela passe donc forcément par une partie de devinette par moment. Du coup, il y a des différences entre les versions car les nouvelles versions peuvent avoir des algorithmes différents pour cette devinette. Si vous êtes sûr de ce que vous lui fournissez, donnez-lui directement en tant que timestamp. Là, si vous indiquez une mauvaise date, vous aurez directement une erreur.

Si on prend le premier exemple, pourquoi renvoit-il le 12 novembre 2012 ? tout simplement parce que vous avez ajouté 00 après la date et que cela ne figure pas dans la spécification du format. Du coup, il suppose qu'il s'agit de 500 jours à partir de juillet 2011.

postgres=# select to_timestamp('2011070500','YYYYMMDD');
      to_timestamp      
------------------------
 2012-11-11 00:00:00+01
(1 row)

postgres=# select '2011-07-01'::timestamp + '500 days'::interval;
      ?column?       
---------------------
 2012-11-12 00:00:00
(1 row)

Remarquez aussi que si on avait indiqué au format les deux derniers 0, il aurait correctement deviné :

postgres=# select to_timestamp('2011070500','YYYYMMDD00');
      to_timestamp      
------------------------
 2011-07-05 00:00:00+02
(1 row)

Si vous aviez utilisé directement la conversion vers timestamp, vous auriez eu une erreur :

postgres=# select '2011-07-0500'::timestamp;
ERREUR:  valeur du champ date/time en dehors des limites : « 2011-07-0500 »
LINE 1: select '2011-07-0500'::timestamp;
               ^
HINT:  Peut-être avez-vous besoin d'un paramètrage « datestyle » différent.

postgres=# select '2011-07-05'::timestamp;
      timestamp      
---------------------
 2011-07-05 00:00:00
(1 row)

---

Guillaume.

jacques

Membre

Re : Infos sur comportement fonction to_timestamp

Bonjour,

Le comportement s'applique aussi avec des nombres négatifs ou une absence de valeur :

bdcp=> select to_timestamp('201107-2','yyyymmdd');       -- bizarre toutefois car 3 jours et non 2 avant le 01 juillet 2011
      to_timestamp     
------------------------
2011-06-28 00:00:00+00
(1 row)

bdcp=> select to_timestamp('201107','yyyymmdd');       -- ok, 01 juillet 2011 à 00 heure
      to_timestamp     
------------------------
2011-07-01 00:00:00+00
(1 row)

bdcp=> show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)

bdcp=> select '201107-2'::timestamp;       -- ok, erreur de formatage
ERROR:  invalid input syntax for type timestamp: "201107-2"
LINE 1: select '201107-2'::timestamp;
               ^
bdcp=> select '201107'::timestamp;  -- bizarre car erreur de formatage
      timestamp     
---------------------
2020-11-07 00:00:00
(1 row)

En fait, dans le dernier exemple, l'année est prise sur 2 digits, soit 2020 11 07,  soit 2020, novembre, le 07
Par contre, la logique du premier exemple n'est pas forcément évidente (2 jours avant le 30 juin et non le 01 juillet).

Par ailleurs, le comportement de to_date semble similaire à celui de to_timestamp et selon Guillaume, cela semble plus être une fonction de calcul de date/timestamp qu'une façon d'indiquer une date/un timestamp.

Y-a-il un moyen de rendre plus " strict " les fonctions to_timestamp... ?

Par avance, merci.

Dernière modification par jacques (12/07/2011 16:47:16)

arthurr

Membre

Re : Infos sur comportement fonction to_timestamp

^
bdcp=> select '201107'::timestamp;  -- bizarre car erreur de formatage
      timestamp     
---------------------
2020-11-07 00:00:00
(1 row)

Non, tout est normal => 20 11 07 (YYMMDD)-> 2020 - 11 - 07

gleu

Administrateur

Re : Infos sur comportement fonction to_timestamp

Y-a-il un moyen de rendre plus " strict " les fonctions to_timestamp... ?

Non, ce n'est pas fait pour. Convertissez directement en timestamp.

---

Guillaume.

jacques

Membre

Re : Infos sur comportement fonction to_timestamp

Autre remarque :

bdcp=> select to_timestamp('20110700','yyyymmdd');       -- même résultat qu'avec 20110701
      to_timestamp     
------------------------
2011-07-01 00:00:00+00
(1 row)

bdcp=> select to_timestamp('20110701','yyyymmdd');       -- même résultat qu'avec 20110700
      to_timestamp     
------------------------
2011-07-01 00:00:00+00

gleu

Administrateur

Re : Infos sur comportement fonction to_timestamp

Ça n'est pas choquant 00/07/2011 n'est pas une date valide. Ce qui gêne est le "00", donc si je l'enlève, il me reste 201107. Le premier jour de juillet 2011 est le 1er juillet 2011 (évient, n'est-ce pas ? ), d'où le 2011-07-01.

---

Guillaume.

jacques

Membre

Re : Infos sur comportement fonction to_timestamp

Effectivement, c'était évident pour moi avant cette discussion, mais si je me réfère à votre idée sur les " algorithmes différents pour cette devinette " et dès lors je raisonne selon un algorithme, cela devient davantage une devinette qu'un algorithme :
to_timestamp('201107-2','yyyymmdd');  --> 2 jours avant le 30 juin car c'est le jour qui précède le 1er juillet, logique quoi.

David

Membre

Re : Infos sur comportement fonction to_timestamp

Bonjour.
Pour l'interprétation, c'est bien ce que je pensais en parlant d'une certaine logique. Je l'avais comprise et je trouve ceci très dangereux, notamment pour ceux qui viennent de "l'autre monde" et qui utilisent le SGBD comment élément de validation en entrée (et oui on a eu cette année des collègues européens qui nous ont envoyé des données datées du 00-MARS-2011 et sur nos plateformes Postgres, on a du les entrer sans soucis à la date du 01/03 !!!!! Gloupsss ).
D'ailleurs un gros warning dans la doc serait intéressante, juste pour signaler les différences de comportement, sensibiliser aux résultats et peut-être plus orienter vers le cast explicite.

De notre côté, nous allons devoir reporter toutes les vérifications de format lors d'insertions de données au niveau des exécutables (ou surcharger la fonction ?) à moins que nous arrivions à utiliser le cast '::to_timestamp' avec nos multiples formats en entrée. A voir. Bref, du bouleau sur la planche ...
Merci pour vos réponses, David.

***********************************************************
C'est vrai que le cast est plus blindé !

syn=> select '2011-03-00 12:01:00+00'::timestamp;
ERROR:  date/time field value out of range: "2011-03-00 12:01:00+00"
LINE 1: select '2011-03-00 12:01:00+00'::timestamp;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

Dernière modification par David (13/07/2011 22:14:22)