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

9.8. Fonctions de formatage de type de données

Les fonctions de formatage de PostgreSQL forment un ensemble puissant d'outils pour convertir différents types de données (date/heure, entier, nombre à virgule flottante, numérique) vers des chaînes formatées et pour convertir de chaînes formatées vers des types de données spécifiques. Tableau 9.25 les liste. Ces fonctions suivent toutes une même convention de nommage : le premier argument est la valeur à formater et le second argument est un modèle qui définit le format de sortie ou d'entrée.

Tableau 9.25. Fonctions de formatage

Fonction

Description

Exemple(s)

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

Convertit une donnée de type timestamp en une chaîne suivant le format indiqué.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')05:31:12

to_char ( interval, text ) → text

Convertit un intervalle en une chaîne suivant le format indiqué.

to_char(interval '15h 2m 12s', 'HH24:MI:SS')15:02:12

to_char ( numeric_type, text ) → text

Convertit un nombre en une chaîne suivant le format indiqué. Disponible pour les types integer, bigint, numeric, real, double precision.

to_char(125, '999')125

to_char(125.8::real, '999D9')125.8

to_char(-125.8, '999D99S')125.80-

to_date ( text, text ) → date

Convertit une chaîne en date suivant le format indiqué.

to_date('05 Dec 2000', 'DD Mon YYYY')2000-12-05

to_number ( text, text ) → numeric

Convertit une chaîne en type numeric suivant le format indiqué.

to_number('12,454.8-', '99G999D9S')-12454.8

to_timestamp ( text, text ) → timestamp with time zone

Convertit une chaîne en type timestamp suivant le format indiqué. (Voir aussi to_timestamp(double precision) dans Tableau 9.32.)

to_timestamp('05 Dec 2000', 'DD Mon YYYY')2000-12-05 00:00:00-05


Astuce

to_timestamp et to_date existent pour gérer les formats en entrée qui ne peuvent être convertis par une simple conversion. Pour les formats plus standards de date/heure, convertir simplement la chaîne source dans le type de données requis fonctionne, et est plus simple. De façon similaire, to_number n'est pas nécessaire pour les représentations standards en type numeric.

Dans une chaîne modèle de sortie pour to_char, il existe certains motifs reconnus et remplacés avec les données formatées de façon appropriée basées sur la valeur donnée. Tout texte qui n'est pas un motif modèle est simplement copié directement. De façon similaire, dans une chaîne modèle en entrée (pour les autres fonctions), les motifs modèles identifient les valeurs à fournir par 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 modèles, les caractères correspondants dans la chaîne de données en entrée sont simplement ignorés (qu'ils soient égaux ou non aux caractères de la chaîne modèle).

Tableau 9.26 affiche les motifs modèles disponibles pour le formatage des valeurs date et heure.

Tableau 9.26. Motifs modèles pour le formatage date/heure

MotifDescription
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)
FF1dixième de seconde (0–9)
FF2centième de seconde (00–99)
FF3milliseconde (000–999)
FF4dixième de milliseconde (0000–9999)
FF5centième de milliseconde (00000–99999)
FF6microseconde (000000–999999)
SSSS, SSSSSsecondes après minuit (0–86399)
AM, am, PM ou pmindicateur de méridien (sans points)
A.M., a.m., P.M. or p.m.indicateur de méridien (avec points)
Y,YYYannée (4 chiffres ou plus) avec virgule
YYYYannée (4 chiffres ou plus)
YYY3 derniers chiffres de l'année
YY2 derniers chiffres de l'année
Ydernier chiffre de l'année
IYYYannée, numérotation ISO 8601 (4 chiffres ou plus)
IYY3 derniers chiffres de l'année, numérotation ISO 8601
IY2 derniers chiffres de l'année, numérotation ISO 8601
Idernier chiffre de l'année, numérotation ISO 8601
BC, bc, AD ou adindicateur d'ère (sans points)
B.C., b.c., A.D. ou a.d.indicateur d'ère (avec points)
MONTHnom du mois en majuscule (sur 9 caractères)
Monthnom du mois en majuscule (sur 9 caractères)
monthnom du mois en minuscule (sur 9 caractères)
MONabréviation du nom du mois en majuscule (3 caractères en anglais, peut varier ailleurs)
Monabréviation du nom du mois capitalisé (3 caractères en anglais, peut varier ailleurs)
monabréviation du nom du mois en minuscule (3 caractères en anglais, peut varier ailleurs)
MMnuméro du mois (01–12)
DAYnom du jour en majuscule (sur 9 caractères)
Daynom du jour en majuscule (sur 9 caractères)
daynom du jour en minuscule (sur 9 caractères)
DYabréviation du nom du jour en majuscule (3 caractères en anglais, peut varier ailleurs)
Dyabréviation du nom du jour capitalisé (3 caractères en anglais, peut varier ailleurs)
dyabréviation du nom du jour en minuscule (3 caractères en anglais, peut varier ailleurs)
DDDjour de l'année (001–366)
IDDDjour de l'année, d'après le standard ISO 8601 (001–371 ; la journée 1 de l'année est le lundi de la première semaine ISO)
DDjour du mois (01–31)
Djour de la semaine, de dimanche (1) à samedi (7)
IDjour de la semaine d'après le standard ISO 8601, de lundi (1) à dimanche (7)
Wnuméro de semaine du mois (1–5) (la première semaine commence le premier jour du mois)
WWnuméro de semaine de l'année (1–53) (la première semaine commence le premier jour de l'année)
IWnuméro de semaine de l'année, d'après le standard ISO 8601 (01–53 ; le premier jeudi de l'année est en semaine 1)
CCsiècle (2 chiffres) (le 21è siècle commence le 1er janvier 2001)
Jjour Julien (nombre de jours depuis le 24 novembre, 4714 avant JC à minuit UTC)
Qquartier
RMmois en majuscules suivant la convention romaine (I–XII; I=janvier)
rmmois en minuscules suivant le convention romaine (i–xii; i=janvier)
TZabréviation du fuseau horaire, en majuscule (supporté uniquement avec to_char)
tzabréviation du fuseau horaire, en minuscule (supporté uniquement avec to_char)
TZHheures du fuseau horaire
TZMminutes du fuseau horaire
OFdécalage du fuseau horaire à partir d'UTC (supporté uniquement avec to_char)

Les modificateurs peuvent être appliqués à tous les motifs modèles pour modifier leurs comportements. Par exemple, FMMonth est le motif Month avec le modificateur FM. Tableau 9.27 affiche les motifs de modificateur pour le formatage date/heure.

Tableau 9.27. Modificateurs des motifs modèles pour le formatage date/heure

ModificateurDescriptionExemple
préfixe FMmode de remplissage (supprime les zéros avant et les blancs de remplissage)FMMonth
Suffixe THsuffixe du numéro ordinal en majusculeDDTH, e.g., 12TH
Suffixe thsuffixe du numéro ordinal en minusculeDDth, e.g., 12th
préfixe FXoption globale pour le format fixe (voir les notes d'utilisation)FX Month DD Day
préfixe TMmode de traduction (utilise les noms localisés du jour et du mois basés sur lc_time)TMMonth
Suffixe SPspell mode (non implémenté)DDSP

Notes d'utilisation du formatage date/heure :

  • FM supprime les zéros en début et les blancs à la fin qui pourraient avoir été ajoutés pour s'assurer d'une taille fix de la sortie. Dans PostgreSQL, FM modifie seulement la spécification suivante alors que dans Oracle, FM affecte toutes les spécifications suivantes, et des modificateurs FM répétés vont basculer l'activation et la désactivation du mode de remplissage.

  • TM supprime les espaces en fin de chaîne que FM soit spécifié ou non.

  • to_timestamp et to_date ignorent la casse des lettres en entrée ; donc par exemple MON, Mon, et mon acceptent toutes les mêmes chaînes. Lors de l'utilisation du modificateur TM, le changement de casse se fait suivant les règles de la collation pour les données en entrée de la fonction (voir Section 23.2).

  • to_timestamp et to_date ignorent les nombreux espaces blancs au début de la chaîne en entrée et autour des valeurs de date et d'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 spécifié 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 de to_timestamp et to_date correspond à tout séparateur dans la chaîne en entrée ou est ignoré, sauf si l'option FX est utilisée. Par exemple, to_timestamp('2000JUN', 'YYYY///MON') et to_timestamp('2000/JUN', 'YYYY MON') fonctionnent mais 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 indiqué, un séparateur dans la chaîne modèle correspond exactement à un caractère dans la chaîne en entrée. Mais notez que le caractère de la chaîne en entrée n'a pas besoin d'être identique au séparateur de 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 dans la chaîne modèle consomme la lettre J de la chaîne en entrée.

  • Un motif de modèle TZH peut correspondre à un nombre signé. Sans l'option FX, les signes moins pourraient être ambigus, et pourraient être interprétés comme un séparateur. Cette ambiguité 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éparateur 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é être un séparateur entre les valeurs. Par exemple, to_timestamp('2000 -10', 'YYYY TZH') fait correspondre -10 à TZH, mais to_timestamp('2000 -10', 'YYYY  TZH') fait correspondre 10 à TZH.

  • Un texte ordinaire est autorisé dans les modèles to_char et sera affiché littéralement. Vous pouvez placer une sous-chaîne entre des guillemets doubles pour forcer son interprétation comme un texte littéral, même s'il contient des motifs de modèle. Par exemple, dans '"Hello Year "YYYY', le texte YYYY sera remplacé par l'année, mais le simple Y dans Year ne le sera pas. Dans to_date, to_number et to_timestamp, le texte littéral et les chaînes entre guillemets doubles finissent en ignorant le nombre de caractères 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 qui ne sont ni des lettres ni des chiffres. Par exemple, to_timestamp('2000y6m1d', 'yyyy-MM-DD') fonctionnait. Maintenant, vous pouvez seulement utiliser des caractères lettres pour cela. Par exemple, to_timestamp('2000y6m1d', 'yyyytMMtDDt') et to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') ignorent y, m et d.

  • Si vous voulez avoir un guillemet double dans la sortie, vous devez la précéder avec un antislash, par exemple '\"YYYY Month\"'. En dehors de cela, les antislashs ne sont pas particulièrement spéciaux en dehors des chaînes entre guillemets doubles. À l'intérieur d'une chaîne entre guillemets doubles, un antislash peut faire que le prochain caractère soit considéré 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, si la spécification du format de l'année fait moins de quatre chiffres, donc YYY, et que l'année indiquée fait moins de quatre chiffres, l'année sera ajustée au plus proche de l'année 2020, donc 95 devient 1995.

  • Dans to_timestamp et to_date, la conversion YYYY a une restriction lors du traitement d'années avec plus de 4 chiffres. Vous devez utiliser certains caractères qui ne sont pas des chiffres ou un modèle après YYYY, sinon l'année est toujours interprétée avec 4 chiffres. Par exemple (avec l'année 20000) : to_date('200001131', 'YYYYMMDD') sera interprété comme une année sur 4 chiffres ; à la placez, utilisez un séparateur qui n'est pas un chiffre après l'année, comme par exemple 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 existe 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 spécifié, mais pas l'année, la première année du sècle est présumée.

  • Dans to_timestamp et to_date, les noms ou numéros des jours de la semaine (DAY, D, et les champs relatifs) sont acceptés mais ignorés pour calculer le résultat. Ceci est vrai aussi pour le champ Q.

  • Dans to_timestamp et to_date, une date d'après la numérotation ISO 8601 (dans le sens distinct d'une date grégorienne) peut être indiqué de deux façons :

    • Année, numéro de semaine et jour de la semaine : par exemple to_date('2006-42-4', 'IYYY-IW-ID') renvoie la date date 2006-10-19. Si vous omettez le jour de la semaine, il est supposé valoir 1 (lundi).

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

    Tenter de saisir une date en utilisant un mix de champs ISO 8601 et de champs grégoriens n'a pas de sens, et résultera en une erreur. Dans le contexte année-numéro de semaine sous l'ISO 8601, 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 rejettera un mix entre champs pour date grégorienne et pour date ISO, to_char ne le fera pas parce que les spécifications de format de sortie comme YYYY-MM-DD (IYYY-IDDD) peuvent se révéler utile. Mais évitez d'écrire quelque chose comme IYYY-MM-DD ; cela pourrait amener des résultats surprenant près du début de l'année. (Voir Section 9.9.1 pour plus d'informations.)

  • Dans to_timestamp, les champs milliseconde (MS) et microseconde (US) sont utilisés comme les chiffres secondes après le point décimal. Par exemple, to_timestamp('12.3', 'SS.MS') ne vaut pas 3 millisecondes, mais 300 parce que la conversion la traite comme 12 + 0,3 secondes. Donc, pour le format SS.MS, les valeurs en entrée 12.3, 12.30 et 12.300 spécifient 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 secondes.

    Voici un exemple plus complexe : to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US') est 15 heures, 12 minutes, et 2 secondes + 20 millisecondes + 1230 microsecondes = 2,021230 secondes.

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

  • to_char(interval) formate HH et HH12 comme indiqué sur une horloge 12-heures, par exemple; l'heure zéro et l'heure 36 s'affichent toutes les deux comme 12, alors que HH24 affiche la valeur complète de l'heure qui peut dépasser 23 dans une valeur de type interval.

Tableau 9.28 montre les motifs de modèle disponibles pour le formatage des valeurs numériques.

Tableau 9.28. Motifs de modèle pour le formatage numérique

MotifDescription
9position d'un chiffre (peut être supprimé si non significatif)
0position d'un chiffre (ne sera pas supprimé même si non significatif)
. (point)point décimal
, (virgule)séparateur de groupe (milliers)
PRvaleur négative entre crochets
Ssigne accroché au nombre (utilise la locale)
Lsymbôle de monnaie (utilise la locale)
Dpoint décimal (utilise la locale)
Gséparateur de groupe (utilise la locale)
MIsigne moins dans la position spécifiée (si nombre < 0)
PLsigne plus dans la position spécifiée (si nombre > 0)
SGsigne plus/moins dans la position spécifiée
RNnombre romain (entrée entre 1 et 3999)
TH or thsuffixe du numéro ordinal
Vdécalage spécifié en nombre de chiffres (voir notes)
EEEEexponsant pour la notation scientifique

Notes d'utilisation pour le formatage numérique :

  • 0 spécifie une position de chiffre qui sera toujours affiché, même s'il contient un zéro en début ou en fin. 9 spécifie aussi une position de chiffre mais, s'il s'agit 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é, alors il sera supprimé. (Pour to_number(), ces deux modèles sont équivalents.)

  • Les caractères de modèle S, L, D et G représentent le signe, le symbôle de monnaie, le point décimal et le séparateur de milliers définis par la locale actuelle (voir lc_monetary et lc_numeric). Le point et la virgule représentent ces caractères avec la signification du point décimal et du séparateur de milliers, quelque soit la locale.

  • Si aucune provision explicite n'est faite pour un signe dans le motif de to_char(), une colonne sera réservée pour le signe, et il sera ancré au chiffre (il apparaîtra à gauche). Si S apparaît juste à gauche de 9, il sera quand même ancré au nombre.

  • Un signe formaté utilisant SG, PL ou MI n'est pas ancré au nombre ; par exemple, to_char(-12, 'MI9999') produit '-  12' mais to_char(-12, 'S9999') produit '  -12'. (L'implémentation Oracle ne permet pas l'utilisation de MI avant 9, mais nécessite plutôt que 9 précède MI.)

  • TH ne convertit pas les valeurs inférieures à zéro et ne convertit pas les nombres fractionnés.

  • PL, SG et TH sont des extensions de PostgreSQL.

  • Dans to_number, si des motifs de modèle non-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 (c'est-à-dire chiffres, signe, point décimal ou virgule). Par exemple, TH ignorerait les deux caractères qui ne sont pas des données.

  • V avec to_char multiplie les valeurs en entrée par 10^n, où n est le nombre de chiffres suivant V. V avec to_number divise d'une façon similaire. to_char et to_number ne supportent pas l'utilisation de V en combinaison avec un point décimal (de ce fait, 99.9V99 n'est pas autorisé).

  • EEEE (notation scientifique) ne peut pas être utilisé en combinaison avec tout autre motif de formatage ou modificateur autre que les motifs de chiffres et de point décimal, et doit être à la fin de la chaîne de format (autrement dit, 9.99EEEE est un motif valide).

Certains modificateurs peuvent être appliqués à tout motif de modèle pour modifier son comportement. Par exemple, FM99.99 est le motif 99.99 avec le modificateur FM. Tableau 9.29 montre les motifs de modificateur pour le formatage numérique.

Tableau 9.29. Modificateurs de motifs de modèle pour le formatage numérique

ModificateurDescriptionExemple
préfixe FMmode de remplissage (supprime les zéros en fin et les espaces d'alignement)FM99.99
suffixe THsuffixe du nombre ordinal en majuscule999TH
suffixe thsuffixe du nombre ordinal en minuscule999th

Tableau 9.30 montre quelques exemples de l'utilisation de la fonction to_char.

Tableau 9.30. to_char Examples

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'