PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Fonctions et opérateurs » Fonctions de formatage des types de données

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.24 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.

Tableau 9.24. Fonctions de formatage

FonctionType en retourDescriptionExemple
to_char(timestamp, text) textconvertit un champ de type timestamp en chaîneto_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)textconvertit un champ de type interval en chaîneto_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)textconvertit un champ de type integer en chaîneto_char(125, '999')
to_char(double precision, text)textconvertit un champ de type real/double precision en chaîneto_char(125.8::real, '999D9')
to_char(numeric, text)textconvertit un champ de type numeric en chaîneto_char(-125.8, '999D99S')
to_date(text, text) dateconvertit une chaîne en dateto_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numericconvertit une chaîne en champ de type numericto_number('12,454.8-', '99G999D9S')
to_timestamp(text, text) timestamp with time zoneconvertit une chaîne string en champ de type timestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')

Note

Il existe aussi une fonction to_timestamp à un seul argument ; voir Tableau 9.31.

Astuce

to_timestamp et to_date existent pour gérer les formats d'entrée qui ne peuvent pas être convertis par un simple transtypage. Pour la plupart des formats de date et heure, le simple fait de transtyper la chaîne source vers le type de donnée requis fonctionne, et c'est bien plus facile. De la même façon, to_number n'est pas nécessaire pour les représentations standard de valeurs numériques.

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. S'il existe des caractères dans la chaîne modèle qui ne sont pas des motifs du modèle, les caractères correspondants dans la chaîne de données en entrée sont tout simplement ignorés (qu'ils soient ou non égaux aux caractères de la chaîne modèle).

Le Tableau 9.25 affiche les motifs disponibles pour formater les valeurs de types date et heure.

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

ModèleDescription
HHheure du jour (01-12)
HH12heure du jour (01-12)
HH24heure du jour (00-23)
MIminute (00-59)
SSseconde (00-59)
MSmilliseconde (000-999)
USmicroseconde (000000-999999)
SSSSsecondes écoulées depuis minuit (0-86399)
AM ou am ou PM ou pmindicateur 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,YYYannée (quatre chiffres et plus) avec virgule
YYYYannée (quatre chiffres et plus)
YYYtrois derniers chiffres de l'année
YYdeux derniers chiffres de l'année
Ydernier chiffre de l'année
IYYYannée suivant la numérotation ISO 8601 des semaines (quatre chiffres ou plus)
IYYtrois derniers chiffres de l'année suivant la numérotation ISO 8601 des semaines
IYdeux derniers chiffres de l'année suivant la numérotation ISO 8601 des semaines
Idernier chiffre de l'année suivant la numérotation ISO 8601 des semaines
BC, bc, AD ou adindicateur de l'ère (sans point)
B.C., b.c., A.D. ou a.d.indicateur de l'ère (avec des points)
MONTHnom complet du mois en majuscules (espaces de complètement pour arriver à neuf caractères)
Monthnom complet du mois en casse mixte (espaces de complètement pour arriver à neuf caractères)
monthnom complet du mois en minuscules (espaces de complètement pour arriver à neuf caractères)
MONabréviation du nom du mois en majuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
Monabréviation du nom du mois avec la première lettre en majuscule et les deux autres en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
monabréviation du nom du mois en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
MMnuméro du mois (01-12)
DAYnom complet du jour en majuscules (espaces de complètement pour arriver à neuf caractères)
Daynom complet du jour avec la première lettre en majuscule et les deux autres en minuscules (espaces de complètement pour arriver à neuf caractères)
daynom complet du jour en minuscules (espaces de complètement pour arriver à neuf caractères)
DYabréviation du nom du jour en majuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
Dyabréviation du nom du jour avec la première lettre en majuscule et les deux autres en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
dyabréviation du nom du jour en minuscules (trois caractères en anglais, la longueur des versions localisées peut varier)
DDDjour de l'année (001-366)
IDDDjour de l'année ISO (001-371 ; le jour 1 de l'année est le lundi de la première semaine ISO.)
DDjour du mois (01-31)
Djour de la semaine du dimanche (1) au samedi (7)
IDjour ISO de la semaine du lundi (1) au dimanche (7)
Wnuméro de semaine du mois, de 1 à 5 (la première semaine commence le premier jour du mois)
WWnuméro de la semaine dans l'année, de 1 à 53 (la première semaine commence le premier jour de l'année)
IWnuméro de la semaine dans l'année ISO (01 - 53 ; le premier jeudi de la nouvelle année est dans la semaine 1)
CCsiècle (deux chiffres) (le 21è siècle commence le 1er janvier 2001)
JDate Julien (nombre de jours depuis le 24 novembre -4714 à minuit heure locale ; voir Section B.7)
Qtrimestre
RMmois en majuscules en nombre romain (I-XII ; I étant janvier) (en majuscules)
rmmois en minuscules en nombre romain (i-xii; i étant janvier) (en minuscules)
TZabréviation du fuseau horaire en majuscules (seulement supporté avec to_char)
tzabréviation du fuseau horaire en minuscules (seulement supporté avec to_char)
TZHheures avec fuseau horaire
TZMminutes avec fuseau horaire
OFdécalage du fuseau horaire à partir d'UTC (seulement supporté avec to_char)

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.26 affiche les modificateurs de motifs pour le formatage des dates/heures.

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

ModificateurDescriptionExemple
préfixe FMmode remplissage (Fill Mode) (supprime les zéros et les blancs de remplissage en début de chaîne)FMMonth
suffixe THsuffixe du nombre ordinal en majuscules, c'est-à-dire 12THDDTH
suffixe thsuffixe du nombre ordinal en minuscules, c'est-à-dire 12thDDth
préfixe FXoption globale de format fixe (voir les notes d'utilisation)FX Month DD Day
préfixe TMmode de traduction (affiche les noms des jours et mois localisés en fonction de lc_time)TMMonth
suffixe SPmode é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 basculent 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 ignorent le modificateur TM.

  • to_timestamp et to_date ignorent les espaces blancs multiples au débit de la chaîne en entrée et autour des valeurs date et heure sauf si l'option FX est utilisée. Par exemple, to_timestamp(' 2000    JUN', 'YYYY MON') et to_timestamp('2000 - JUN', 'YYYY-MON') fonctionnent, mais to_timestamp('2000    JUN', 'FXYYYY MON') renvoie une erreur parce que to_timestamp s'attend seulement à un seul espace. FX doit être indiqué comme premier élément dans le modèle.

  • Un séparateur (un espace ou un caractère qui n'est ni une lettre ni un chiffre) dans la chaîne modèle des fonctions to_timestamp et to_date correspond à tout séparateur simple dans la chaîne en entrée ou est ignoré, sauf si l'option FX est utilisé. Par exemple, to_timestamp('2000JUN', 'YYYY///MON') et to_timestamp('2000/JUN', 'YYYY MON') fonctionnent et to_timestamp('2000//JUN', 'YYYY/MON') renvoie une erreur parce que le nombre de séparateurs dans la chaîne en entrée dépasse le nombre de séparateurs dans le modèle.

    Si FX est précisé, un séparateur dans la chaîne modèle correspond exactement à un caractère dans la chaîne en entrée. Mais notez qu'il n'est pas requis que le caractère de la chaîne en entrée soit identique au séparateur dans la chaîne modèle. Par exemple, to_timestamp('2000/JUN', 'FXYYYY MON') fonctionne, mais to_timestamp('2000/JUN', 'FXYYYY  MON') renvoie une erreur parce que le deuxième espace de la chaîne modèle est consommé par la lettre J dans la chaîne en entrée.

  • Un modif modèle TZH peut correspondre à un nombre signé. Sans l'option FX, les signes moins peuvent être ambigus et pourraient être interprétés comme un séparateur. L'ambiguïté est résolue ainsi. Si le nombre de séparateurs avant TZH dans la chaîne modèle est inférieur au nombre de séparateurs avant le signe moins dans la chaîne en entrée, le signe moins est interprété comme faisant partie de TZH. Sinon le signe moins est considéré comme un séparateur entre valeurs. Par exemple, to_timestamp('2000 -10', 'YYYY TZH') correspond à -10 avant TZH, mais to_timestamp('2000 -10', 'YYYY  TZH') correspond à 10 avec TZH.

  • 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, le texte littéral et les chaînes entre guillemets doubles ignorent le nombre de caractères en entrée contenus dans la chaîne, par exemple "XX" ignore les deux caractères en entrée (qu'ils soient ou non XX).

    Astuce

    Avant PostgreSQL 12, il était possible d'ignorer du texte arbitraire dans la chaîne en entrée en utilisant des caractères autres que des lettres et des chiffres. Par exemple, to_timestamp('2000y6m1d', 'yyyy-MM-DD') fonctionnait. Maintenant, vous pouvez seulement utiliser des lettres pour ça. Par exemple, to_timestamp('2000y6m1d', 'yyyytMMtDDt') et to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') ignorent y, m et d.

  • Pour afficher un guillemet double dans la sortie, il faut le faire précéder d'un antislash. '\"YYYY Month\"', par exemple. Les antislashs ne sont pas traités spécialement en dehors des chaînes entre guillemets doubles. Dans une chaîne entre guillemets doubles, un antislash fait que le caractère suivant est pris littéralement, quel qu'il soit (mais ceci n'a pas d'effet spécial, sauf si le caractère suivant est un guillemet double ou un autre antislash).

  • Dans to_timestamp et to_date, les années négatives sont traitées comme signifiant avant Jésus Christ. Si vous écrivez à la fois une année négative et un champ BC explicite, vous obtenez après Jésus Christ de nouveau. Une entrée année zéro est traitée comme l'année 1 après Jésus Christ.

  • Dans to_timestamp et to_date, 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.

  • Dans to_timestamp et to_date, la conversion YYYY 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 to_timestamp et to_date, le champ CC (siècle) est accepté, mais ignoré s'il y a un champ YYY, YYYY ou Y,YYY. Si CC est utilisé avec YY ou Y, alors le résultat est calculé 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 .

  • Dans to_timestamp et to_date, les noms ou chiffres des jours de la semaine (DAY, D, et les type de champs liés) sont acceptés, mais ignorés pour les besoins du calcul du résultat. C'est également vrai pour les champs quartiers (Q).

  • Dans to_timestamp et to_date, une date dans la numérotation par semaine ISO 8601 (différent d'une date grégorienne) peut être spécifiée d'une de ces 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

    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 pour plus d'informations).

  • Dans to_timestamp, les champs millisecondes (MS) et microsecondes (US) sont utilisés comme partie décimale des secondes. Par exemple, to_timestamp('12.3', 'SS.MS') ne correspond pas à 3 millisecondes, mais à 300, car la conversion traite la valeur comme 12 + 0.3 seconds. Ainsi, pour le format SS.MS, les valeurs en entrée 12.3, 12.30, et 12.300 spécifient toutes le même nombre de millisecondes. Pour obtenir trois millisecondes, il faut écrire 12.003, que la conversion traite comme 12 + 0.003 = 12.003 seconds.

    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 une valeur de type interval.

Le Tableau 9.27 affiche les motifs de modèle disponibles pour le formatage des valeurs numériques.

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

MotifDescription
9position du chiffre (peut être supprimé si non significatif)
0position du chiffre (ne sera pas supprimé, même si non significatif)
. (point)point décimal
, (virgule)séparateur de groupe (milliers)
PRvaleur négative entre chevrons
Ssigne accroché au nombre (utilise la locale)
Lsymbole monétaire (utilise la locale)
Dpoint décimal (utilise la locale)
Gséparateur de groupe (utilise la locale)
MIsigne moins dans la position indiquée (si le nombre est inférieur à 0)
PLsigne plus dans la position indiquée (si le nombre est supérieur à 0)
SGsigne plus/moins dans la position indiquée
RNnuméro romain (saisie entre 1 et 3999)
TH ou thsuffixe du nombre ordinal
Vdécalage du nombre indiqué de chiffres (voir les notes)
EEEEexposant pour la notation scientifique

Notes d'utilisation pour le formatage des nombres :

  • 0 indique la position d'un chiffre qui sera toujours affiché, même s'il contient un zéro en début ou en fin. 9 indique aussi la position d'un chiffre, mais, s'il s'agit d'un zéro en début, alors il sera remplacé par un espace alors que, s'il s'agit d'un zéro en fin et que le mode de remplissage est activé, il sera supprimé. (Pour to_number(), ces deux motifs de caractères sont équivalents.) ;

  • si le format fournit moins de chiffres décimaux que le nombre en cours de formatage, to_char() arrondira le nombre du nombre indiqué de chiffres décimaux.

  • les motifs S, L, D et G représentent le signe, le symbole de monnaie, le point décimal et le séparateur des milliers définis par la locale actuelle (voir lc_monetary et lc_numeric). Les motifs point et virgule représentent ces caractères avec la signification des séparateurs point décimal et séparateur de milliers, quelle que soit la locale ;

  • si aucun espace explicite n'est prévu pour un signe dans le motif de to_char(), une colonne sera réservée pour le signe et il sera ancré pour apparaître à la gauche du nombre. Si S apparaît à gauche d'un motif de 9, le signe sera ancré au nombre ;

  • 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'implémentation d'Oracle n'autorise pas l'utilisation de MI devant 9, mais requiert plutôt que 9 précède MI) ;

  • 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 ;

  • Dans to_number, si les motifs de modèle sans données tels que L ou TH sont utilisés, le nombre correspondant de caractères en entrée est ignoré, qu'ils correspondent ou non au motif du modèle, sauf si ce sont des caractères de données (autrement dit, chiffres, signes, point décimal, ou virgule). Par exemple, TH ignorera deux caractères qui ne sont pas des données.

  • V avec to_char multiplie effectivement les valeurs en entrée par 10^n, où n est le nombre de chiffres qui suit V. V avec to_number divise de la même façon. to_char et to_number ne supportent 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, FM99.99 est le motif 99.99 avec le modificateur FM. Tableau 9.28 affiche les motifs pour le formatage numérique.

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

ModificateurDescriptionExemple
préfixe FMmode de remplissage (supprime les blancs et zéros en début de chaîne)FM99.99
suffixe THsuffixe d'un nombre ordinal en majuscule999TH
suffixe thsuffixe d'un nombre ordinal en minuscule999th

Le Tableau 9.29 affiche quelques exemples de l'utilisation de la fonction to_char.

Tableau 9.29. Exemples avec to_char

ExpressionRé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, 'FM90.99')'-0.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'