Conversion des formats de dates

Le monde des bases de données et du frontend n’est pas toujours d’accord sur la façon dont les dates doivent s’afficher. PostgreSQL peut stocker les dates comme DATE, TIMESTAMP ou même TIMESTAMPTZ, mais ce format ne colle pas toujours pour l’affichage côté utilisateur. Par exemple, au lieu du classique 2023-10-01 12:30:45, les designers peuvent vouloir voir 01 octobre 2023, 12:30. Et parfois, il faut formater la date pour des rapports ou des API.

Pour convertir des dates en chaînes de caractères et inversement dans PostgreSQL, on a les fonctions TO_CHAR() et TO_DATE().

La fonction TO_CHAR()

TO_CHAR(), c’est ton meilleur pote quand tu veux transformer des données temporelles en un format texte lisible. Elle prend une date ou un timestamp et la formate selon le modèle que tu donnes.

Syntaxe

TO_CHAR(value, format)

• value — la date ou le timestamp à convertir.
• format — une chaîne avec le modèle de format, c’est comme ça que tu veux afficher la date.

Exemples de formats

La liste complète des formats est dispo dans la doc officielle PostgreSQL.

Exemples d’utilisation de TO_CHAR()

Formatage de date pour un rapport

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Résultat : '09.10.2023'

Affichage de l’heure au format 12h

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Résultat : '03:45 PM'

Afficher le mois en toutes lettres

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Résultat : 'October '

À noter : PostgreSQL ajoute un espace à la fin. C’est une feature, pas un bug ! Pour virer les espaces, utilise la fonction TRIM() :

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Créer un format custom

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Résultat : '2023/10/09 15:45:30'

Formatage pour l’interface utilisateur

SELECT TO_CHAR(NOW(), 'DD "octobre" YYYY année') AS user_friendly_date;
-- Résultat : '09 octobre 2023 année'

La fonction TO_DATE()

TO_DATE() fait l’inverse : elle prend une chaîne et la convertit en type DATE. Pourquoi faire ? Par exemple, un utilisateur peut saisir une date au format 01-10-2023, et PostgreSQL doit "comprendre" de quelle date il s’agit.

Syntaxe

TO_DATE(value, format)

• value — la chaîne qui contient la date.
• format — la chaîne qui décrit le format de la chaîne.<

Exemples d’utilisation de TO_DATE()

Conversion d’une chaîne en date

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Résultat : '2023-10-01' (type : DATE)

Comparer une date chaîne avec une date en table

Imaginons qu’on a une table appointments avec une colonne appointment_date de type DATE. L’utilisateur saisit une date sous forme de chaîne :

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Mauvais format

Important : si le format de la chaîne ne correspond pas au modèle, tu auras une erreur ! Exemple :

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Erreur : format d’entrée invalide

Vérification des données utilisateur

Imaginons qu’on crée une table pour stocker des commandes, où la date est saisie par l’utilisateur :

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Insertion de données avec conversion de chaîne en date
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Exemples pratiques

Formatage d’un rapport. Dans la table sales, la date de vente est stockée dans la colonne sale_date (type TIMESTAMP). On veut sortir un rapport où les dates sont au format DD.MM.YYYY.

-- Exemple de données
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Rapport
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Conversion de données utilisateur. Disons que l’utilisateur saisit la date au format chaîne MM/DD/YYYY. Il faut la convertir en DATE pour la stocker dans le système.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Erreurs courantes et conseils

Mauvais format. L’erreur classique, c’est quand le format de la chaîne ne colle pas avec le modèle. Par exemple, si l’utilisateur a saisi la date comme 01-10-2023 mais que le format est MM/DD/YYYY, PostgreSQL va râler. Conseil : valide toujours l’entrée utilisateur avant de l’envoyer en SQL.

Espaces dans les formats TO_CHAR(). Certains formats, comme MONTH, ajoutent des espaces. Si ça pose souci, utilise la fonction TRIM().

Erreurs lors du parsing des chaînes. Si la chaîne contient des caractères ou un format inattendu, PostgreSQL ne pourra pas la convertir. Conseil : utilise des regex ou des contrôles supplémentaires avant d’insérer dans la base.

Mauvaise utilisation des formats de temps. Par exemple, essayer de traiter un TIMESTAMP avec un modèle pour DATE. Conseil : assure-toi que les types de données utilisés correspondent à ce que tu veux faire.

Les fonctions TO_CHAR() et TO_DATE() ouvrent plein de possibilités pour bosser avec les données temporelles. Tu peux créer des formats pratiques pour les rapports, convertir les saisies utilisateur et rendre tes requêtes SQL plus lisibles. Dans la vraie vie, ces fonctions sont super utilisées pour la visualisation de données, la génération de rapports, l’intégration avec d’autres systèmes et la préparation d’interfaces utilisateur.