PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

9.8. Fonctions de formatage des types de données

Les fonctions de formatage de PostgreSQL™ fournissent un ensemble d'outils puissants pour convertir différents types de données (date/heure, entier, nombre à virgule flottante, numérique) en chaînes formatées et pour convertir des chaînes formatées en types de données spécifiques. Le Tableau 9.21, « Fonctions de formatage » les liste. Ces fonctions suivent toutes une même convention d'appel : le premier argument est la valeur à formater et le second argument est un modèle définissant le format de sortie ou d'entrée.

La fonction to_timestamp à un argument est aussi disponible ; elle accepte un argument double precision unique pour convertir une valeur de type epoch Unix en timestamp with time zone (secondes depuis 1970-01-01 00:00:00+00) en timestamp with time zone. (Les types epoch Unix (entier) sont implicitement convertis en double precision.)

Tableau 9.21. Fonctions de formatage

Fonction Type en retour Description Exemple
to_char(timestamp, text) text convertit un champ de type timestamp en chaîne to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text convertit un champ de type interval en chaîne to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text) text convertit un champ de type integer en chaîne to_char(125, '999')
to_char(double precision, text) text convertit un champ de type real/double precision en chaîne to_char(125.8::real, '999D9')
to_char(numeric, text) text convertit un champ de type numeric en chaîne to_char(-125.8, '999D99S')
to_date(text, text) date convertit une chaîne en date to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric convertit une chaîne en champ de type numeric to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text) timestamp with time zone convertit une chaîne string en champ de type timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision) timestamp with time zone convertit une valeur de type epoch UNIX en valeur de type timestamp to_timestamp(1284352323)

Dans une chaîne de motif pour to_char, il existe certains motifs qui sont reconnus et remplacés avec des données correctement formatées basées sur la valeur. Tout texte qui n'est pas un motif est copié sans modification. De façon similaire, dans toute chaîne de motif en entrée (tout sauf to_char), les motifs identifient les valeurs à fournir à la chaîne de données en entrée.

Le Tableau 9.22, « Modèles pour le formatage de champs de type date/heure » affiche les motifs disponibles pour formater les valeurs de types date et heure.

Tableau 9.22. Modèles pour le formatage de champs de type date/heure

Modèle Description
HH heure du jour (01-12)
HH12 heure du jour (01-12)
HH24 heure du jour (00-23)
MI minute (00-59)
SS seconde (00-59)
MS milliseconde (000-999)
US microseconde (000000-999999)
SSSS secondes écoulées depuis minuit (0-86399)
AM ou am ou PM ou pm indicateur du méridien (sans point)
A.M. ou a.m. ou P.M. ou p.m. indicateur du méridien (avec des points)
am ou a.m. ou pm ou p.m. indicateur du méridien (en minuscules)
Y,YYY année (quatre chiffres et plus) avec virgule
YYYY année (quatre chiffres et plus)
YYY trois derniers chiffres de l'année
YY deux derniers chiffres de l'année
Y dernier chiffre de l'année
IYYY année suivant la numérotation ISO 8601 des semaines (quatre chiffres ou plus)
IYY trois derniers chiffres de l'année suivant la numérotation ISO 8601 des semaines
IY deux derniers chiffres de l'année suivant la numérotation ISO 8601 des semaines
I dernier chiffre de l'année suivant la numérotation ISO 8601 des semaines
BC, bc, AD ou ad indicateur de l'ère (sans point)
B.C., b.c., A.D. ou a.d. indicateur de l'ère (avec des points)
MONTH nom complet du mois en majuscules (espaces de complètement pour arriver à neuf caractères)
Month nom complet du mois en casse mixte (espaces de complètement pour arriver à neuf caractères)
month nom complet du mois en minuscules (espaces de complètement pour arriver à neuf caractères)
MON abréviation du nom du mois en majuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
Mon abréviation du nom du mois avec la première lettre en majuscule et les deux autres en minuscule (trois caractères en anglais, la longueur des versions localisées peut varier)
mon abréviation du nom du mois en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
MM numéro du mois (01-12)
DAY nom complet du jour en majuscules (espaces de complètement pour arriver à neuf caractères)
Day nom complet du jour avec la première lettre en majuscule et les deux autres en minuscule (espaces de complètement pour arriver à neuf caractères)
day nom complet du jour en minuscules (espaces de complètement pour arriver à neuf caractères)
DY abréviation du nom du jour en majuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
Dy abréviation du nom du jour avec la première lettre en majuscule et les deux autres en minuscule (trois caractères en anglais, la longueur des versions localisées peut varier)
dy abréviation du nom du jour en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
DDD jour de l'année (001-366)
IDDD jour de l'année ISO (001-371 ; le jour 1 de l'année est le lundi de la première semaine ISO.)
DD jour du mois (01-31)
D jour de la semaine du dimanche (1) au samedi (7)
ID jour ISO de la semaine du lundi (1) au dimanche (7)
W numéro de semaine du mois, de 1 à 5 (la première semaine commence le premier jour du mois)
WW numéro de la semaine dans l'année, de 1 à 53 (la première semaine commence le premier jour de l'année)
IW numéro de la semaine dans l'année ISO (01 - 53 ; le premier jeudi de la nouvelle année est dans la semaine 1)
CC siècle (deux chiffres) (le 21è siècle commence le 1er janvier 2001)
J nombre de jours dans le calendrier Julien (nombre de jours depuis le 24 novembre -4714 à minuit)
Q trimestre (ignoré par to_date and to_timestamp)
RM mois en majuscule en nombre romain (I-XII ; I étant janvier) (en majuscules)
rm mois en minuscule en nombre romain (i-xii; i étant janvier) (en minuscules)
TZ nom en majuscule du fuseau horaire (en majuscules)
tz nom en minuscule du fuseau horaire (en minuscules)
OF time-zone offset

Les modificateurs peuvent être appliqués à tous les motifs pour en changer le comportement. Par exemple, FMMonth est le motif Month avec le modificateur FM. Le Tableau 9.23, « Modificateurs de motifs pour le formatage des dates/heures » affiche les modificateurs de motifs pour le formatage des dates/heures.

Tableau 9.23. Modificateurs de motifs pour le formatage des dates/heures

Modificateur Description Exemple
préfixe FM mode remplissage (Fill Mode) (supprime les espaces et les zéros de complétion en fin) FMMonth
suffixe TH suffixe du nombre ordinal en majuscules, c'est-à-dire 12TH DDTH
suffixe th suffixe du nombre ordinal en minuscules, c'est-à-dire 12th DDth
préfixe FX option globale de format fixe (voir les notes d'utilisation) FX Month DD Day
préfixe TM mode de traduction (affiche les noms des jours et mois localisés en fonction de lc_time) TMMonth
suffixe SP mode épelé (Spell Mode) (non implanté) DDSP

Notes d'utilisation pour le formatage date/heure :

  • FM supprime les zéros de début et les espaces de fin qui, autrement, sont ajoutés pour fixer la taille du motif de sortie ; Dans PostgreSQL™, FM modifie seulement la prochaine spécification alors qu'avec Oracle, FM affecte toutes les spécifications suivantes et des modificateurs FM répétés bascule l'activation du mode de remplissage.

  • TM n'inclut pas les espaces de complétion en fin de chaîne ; to_timestamp et to_date ignore le modificateur TM.

  • to_timestamp et to_date ignorent les espaces multiples de la chaîne en entrée si l'option FX n'est pas utilisée. Par exemple, to_timestamp('2000    JUN', 'YYYY MON') fonctionne mais to_timestamp('2000    JUN', 'FXYYYY MON') renvoie une erreur car to_timestamp n'attend qu'une seule espace ; FX doit être indiqué comme premier élément du modèle.

  • to_timestamp et to_date existent pour gérer les formats en entrée qui ne peuvent pas être convertis par une conversion simple. Ces fonctions interprètent une entrée de façon libre, avec une vérification minimale des erreurs. Bien qu'elle produit une sortie valide, cette conversion peut générer des résultats inattendus. Par exemple, les données en entrée de ces fonctions ne sont pas restreintes par les intervalles habituels, du coup to_date('20096040','YYYYMMDD') renvoie 2014-01-17 plutôt que de causer une erreur. Une conversion standard n'aura pas ce comportement.

  • il est possible d'insérer du texte ordinaire dans les modèles to_char. il est alors littéralement remis en sortie. Une sous-chaîne peut être placée entre guillemets doubles pour forcer son interprétation en tant que libellé même si elle contient des mots clés de motif. Par exemple, dans '"Hello Year "YYYY', les caractères YYYY sont remplacés par l'année mais l'Y isolé du mot Year ne l'est pas ; Dans to_date, to_number et to_timestamp, les chaînes entre guillemets doubles ignorent le nombre de caractères en entrée contenus dans la chaîne, par exemple "XX" ignorent les deux caractères en entrée.

  • pour afficher un guillemet double dans la sortie, il faut le faire précéder d'un antislash. '\"YYYY Month\"', par exemple.

  • Si la spécification du format de l'année est inférieure à quatre chiffres, par exemple YYY et que l'année fournie est inférieure à quatre chiffres, l'année sera ajustée à l'année la plus proche de l'année 2020. Par exemple, 95 devient 1995.

  • la conversion YYYY d'une chaîne en champ de type timestamp ou date comporte une restriction avec les années à plus de quatre chiffres. Il faut alors utiliser un modèle ou un caractère non-numérique après YYYY, sans quoi l'année est toujours interprétée sur quatre chiffres. Par exemple, pour l'année 20000 : to_date('200001131', 'YYYYMMDD') est interprété comme une année à quatre chiffres ; il faut alors utiliser un séparateur non décimal après l'année comme to_date('20000-1131', 'YYYY-MMDD') ou to_date('20000Nov31', 'YYYYMonDD') ;

  • dans les conversions de chaîne en timestamp ou date, le champ CC (siècle) est ignoré s'il y a un champ YYY, YYYY ou Y,YYY. Si CC est utilisé avec YY ou Y, alors l'année est calculée comme l'année dans le siècle spécifié. Si le siècle est précisé mais pas l'année, la première année du siècle est utilisée ;

  • Une date ISO (distincte de la date grégorienne) peut être passée à to_timestamp et to_date de deux façons :

    • Année, semaine et jour de la semaine. Par exemple, to_date('2006-42-4', 'IYYY-IW-ID') renvoie la date 2006-10-19. En cas d'omission du jour de la semaine, lundi est utilisé.

    • Année et jour de l'année. Par exemple, to_date('2006-291', 'IYYY-IDDD') renvoie aussi 2006-10-19.

    Essayer de construire une date en utilisant un mélange de champs de semaine ISO 8601 et de date grégorienne n'a pas de sens et renverra du coup une erreur. Dans le contexte d'une année ISO, le concept d'un « mois » ou du « jour d'un mois » n'a pas de signification. Dans le contexte d'une année grégorienne, la semaine ISO n'a pas de signification.

    [Attention]

    Attention

    Alors que to_date rejette un mélange de champs de dates grégoriennes et ISO, to_char ne le fait pas car une spécification de format de sortie telle que YYYY-MM-DD (IYYY-IDDD) peut être utile. Mais évitez d'écrire quelque chose comme IYYY-MM-DD ; cela pourrait donner des résultats surprenants vers le début d'année (voir Section 9.9.1, « EXTRACT, date_part » pour plus d'informations).

  • les valeurs en millisecondes (MS) et microsecondes (US) dans une conversion de chaîne en champ de type timestamp sont utilisées comme partie décimale des secondes. Par exemple, to_timestamp('12:3', 'SS:MS') n'est pas 3 millisecondes mais 300 car la conversion le compte comme 12 + 0,3 secondes. Cela signifie que pour le format SS:MS, les valeurs d'entrée 12:3, 12:30 et 12:300 indiquent le même nombre de millisecondes. Pour obtenir trois millisecondes, il faut écrire 12:003 que la conversion compte comme 12 + 0,003 = 12,003 secondes.

    Exemple plus complexe : to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US') représente 15 heures, 12 minutes et (2 secondes + 20 millisecondes + 1230 microsecondes =) 2,021230 secondes ;

  • la numérotation du jour de la semaine de to_char(..., 'ID') correspond à la fonction extract(isodow from ...) mais to_char(..., 'D') ne correspond pas à la numération des jours de extract(dow from ...).

  • to_char(interval) formate HH et HH12 comme indiqué dans une horloge sur 12 heures, c'est-à-dire que l'heure 0 et l'heure 36 sont affichées 12, alors que HH24 affiche la valeur heure complète, qui peut même dépasser 23 pour les

Le Tableau 9.24, « Motifs de modèle pour le formatage de valeurs numériques » affiche les motifs de modèle disponibles pour le formatage des valeurs numériques.

Tableau 9.24. Motifs de modèle pour le formatage de valeurs numériques

Motif Description
9 valeur avec le nombre indiqué de chiffres
0 valeur avec des zéros de début de chaîne
. (point) point décimal
, (virgule) séparateur de groupe (milliers)
PR valeur négative entre chevrons
S signe accroché au nombre (utilise la locale)
L symbole monétaire (utilise la locale)
D point décimal (utilise la locale)
G séparateur de groupe (utilise la locale)
MI signe moins dans la position indiquée (si le nombre est inférieur à 0)
PL signe plus dans la position indiquée (si le nombre est supérieur à 0)
SG signe plus/moins dans la position indiquée
RN numéro romain (saisie entre 1 et 3999)
TH ou th suffixe du nombre ordinal
V décalage du nombre indiqué de chiffres (voir les notes)
EEEE exposant pour la notation scientifique

Notes d'utilisation pour le formatage des nombres :

  • un signe formaté à l'aide de SG, PL ou MI n'est pas ancré au nombre ; par exemple, to_char(-12, 'S9999') produit '  -12' mais to_char(-12, 'MI9999') produit '-  12'. L'implantation Oracle n'autorise pas l'utilisation de MI devant 9, mais requiert plutôt que 9 précède MI ;

  • 9 est transformé en valeur avec le même nombre de chiffres qu'il y a de 9. Si un chiffre n'est pas disponible, il est remplacé par une espace ;

  • TH ne convertit pas les valeurs inférieures à zéro et ne convertit pas les nombres fractionnels ;

  • PL, SG et TH sont des extensions PostgreSQL™ ;

  • V multiplie effectivement les valeurs en entrée par 10^n, où n est le nombre de chiffres qui suit V. to_char ne supporte pas l'utilisation de V combiné avec un point décimal (donc 99.9V99 n'est pas autorisé).

  • EEEE (notation scientifique) ne peut pas être utilisé en combinaison avec un des autres motifs de formatage ou avec un autre modificateur, en dehors des motifs chiffre et de point décimal, et doit être placé à la fin de la chaîne de format (par exemple, 9.99EEEE est valide).

Certains modificateurs peuvent être appliqués à un motif pour modifier son comportement. Par exemple, FM9999 est le motif 9999 avec le modificateur FM. Tableau 9.25, « Modifications de motifs pour le formatage numérique » affiche les motifs pour le formatage numérique.

Tableau 9.25. Modifications de motifs pour le formatage numérique

Modificateur Description Exemple
préfixe FM mode de remplissage (supprime les blancs et zéros en fin de chaîne) FM9999
suffixe TH suffixe d'un nombre ordinal en majuscule 999TH
suffixe th suffixe d'un nombre ordinal en minuscule 999th

Le Tableau 9.26, « Exemples avec to_char » affiche quelques exemples de l'utilisation de la fonction to_char.

Tableau 9.26. Exemples avec to_char

Expression Résultat
to_char(current_timestamp, 'Day, DD  HH12:MI:SS') 'Tuesday  , 06  05:39:18'
to_char(current_timestamp, 'FMDay, FMDD  HH12:MI:SS') 'Tuesday, 6  05:39:18'
to_char(-0.1, '99.99') '  -.10'
to_char(-0.1, 'FM9.99') '-.1'
to_char(0.1, '0.9') ' 0.1'
to_char(12, '9990999.9') '    0012.0'
to_char(12, 'FM9990999.9') '0012.'
to_char(485, '999') ' 485'
to_char(-485, '999') '-485'
to_char(485, '9 9 9') ' 4 8 5'
to_char(1485, '9,999') ' 1,485'
to_char(1485, '9G999') ' 1 485'
to_char(148.5, '999.999') ' 148.500'
to_char(148.5, 'FM999.999') '148.5'
to_char(148.5, 'FM999.990') '148.500'
to_char(148.5, '999D999') ' 148,500'
to_char(3148.5, '9G999D999') ' 3 148,500'
to_char(-485, '999S') '485-'
to_char(-485, '999MI') '485-'
to_char(485, '999MI') '485 '
to_char(485, 'FM999MI') '485'
to_char(485, 'PL999') '+485'
to_char(485, 'SG999') '+485'
to_char(-485, 'SG999') '-485'
to_char(-485, '9SG99') '4-85'
to_char(-485, '999PR') '<485>'
to_char(485, 'L999') 'DM 485'
to_char(485, 'RN') '        CDLXXXV'
to_char(485, 'FMRN') 'CDLXXXV'
to_char(5.2, 'FMRN') 'V'
to_char(482, '999th') ' 482nd'
to_char(485, '"Good number:"999') 'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999') 'Pre: 485 Post: .800'
to_char(12, '99V999') ' 12000'
to_char(12.4, '99V999') ' 12400'
to_char(12.45, '99V9') ' 125'
to_char(0.0004859, '9.99EEEE') ' 4.86e-04'