Analyse et formatage des dates

Nettoyer des données dans des bases de données PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Analyser des dates avec la fonction DATE()

  camis   |         name         | inspection_date | grade_date | ... 
 ---------+----------------------+-----------------+------------+----
 ...      | ...                  | ...             | ...        | ...
 50034992 | EMPANADAS MONUMENTAL | 06/21/2019      | 06/21/2019 | ...
 50095871 | ALPHONSO'S PIZZERIA  | 01/16/2020      | 01/16/2020 | ...
 41104041 | THE SPARROW TAVERN   | 09/17/2019      | 09/17/2019 | ...
 50016937 | BURGER KING          | 09/14/2018      | 09/14/2018 | ...
 50033304 | ASTORIA PIZZA        | 12/18/2019      | 12/18/2019 | ...
 ...      | ...                  | ...             | ...        | ...

Fonctions DATE non offertes pour une colonne TEXT
- Vérifier des plages de dates
- Extraire des composants de date
- Calculer l'intervalle entre des dates
DATE(string_date)
- Convertit string_date en valeur DATE
- DATE('2019-12-01') → valeur DATE

Analyser des dates avec la fonction DATE()

SELECT
  camis,
  name,
  DATE(inspection_date) AS inspection_date,
  DATE(grade_date) AS grade_date
FROM
  restaurant_inspection;

  camis   |              name               | inspection_date | grade_date | ... 
 ---------+---------------------------------+-----------------+------------+-----
 ...      | ...                             | ...             | ...        | ...
 50034992 | EMPANADAS MONUMENTAL            | 2019-06-21      | 2019-06-21 | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 2020-01-16      | 2020-01-16 | ...
 41104041 | THE SPARROW TAVERN              | 2019-09-17      | 2019-09-17 | ...
 50016937 | BURGER KING                     | 2018-09-14      | 2018-09-14 | ...
 50033304 | ASTORIA PIZZA                   | 2019-12-18      | 2019-12-18 | ...
 ...      | ...                             | ...             | ...        | ...

Analyser des dates avec la fonction TO_DATE()

TO_DATE(date_string, format_string) → valeur DATE
DATE('Wednesday, June 10th, 2014') → ERREUR
TO_DATE('Wednesday, June 10th, 2014', 'Day, Month DDth, YYYY') → valeur DATE

L'expression NULLIF()

 camis   |              name                | inspection_date | grade_date | ... 
 --------+----------------------------------+-----------------+------------+-----
 ...      | ...                             | ...             | ...        | ...
 41659848 | LA BRISA DEL CIBAO              | 2018-01-30      | -          | ...
 40961447 | MESON SEVILLA RESTAURANT        | 2019-03-19      | -          | ...
 50063071 | WA BAR                          | 2018-05-23      | -          | ...
 50034992 | EMPANADAS MONUMENTAL            | 2019-06-21      | 2019-06-21 | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 2020-01-16      | 2020-01-16 | ...
 ...      | ...                             | ...             | ...

NULLIF(value1, value2)

SELECT
  NULLIF(grade_date, '-')
FROM
  restaurant_inspection;

Afficher des dates avec la fonction TO_CHAR()

Format de date par défaut :
- YYYY-MM-DD (ex. 2012-04-03)
TO_CHAR('2012-04-03', YYYY-DD-MM)
TO_CHAR(date_value, format_string) → valeur chaîne

SELECT
  camis,
  name,
  TO_CHAR(
        inspection_date::date, 
        'MM/DD/YY'
  ) AS inspection_date
FROM
  restaurant_inspection;

 camis    |              name               | inspection_date | ... 
 ---------+---------------------------------+-----------------+-----
 ...      | ...                             | ...             | ...
 41659848 | LA BRISA DEL CIBAO              | 01/30/2018      | ...
 40961447 | MESON SEVILLA RESTAURANT        | 03/19/2019      | ...
 50063071 | WA BAR                          | 05/23/2018      | ...
 50034992 | EMPANADAS MONUMENTAL            | 06/21/2019      | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 01/16/2020      | ...
 ...      | ...                             | ...             | ...

 camis    |              name               | inspection_date | ... 
 ---------+---------------------------------+-----------------+-----
 ...      | ...                             | ...             | ...
 41659848 | LA BRISA DEL CIBAO              | 01/30/20        | ...
 40961447 | MESON SEVILLA RESTAURANT        | 03/19/20        | ...
 50063071 | WA BAR                          | 05/23/20        | ...
 50034992 | EMPANADAS MONUMENTAL            | 06/21/20        | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 01/16/20        | ...
 ...      | ...                             | ...             | ...

Masques de format de date

TO_DATE(date_string, format)
TO_CHAR(date_value, format)

Masques de format de date avec TO_DATE()

YYYY

TO_DATE('2012', 'YYYY')

DATE

MM

TO_DATE('09/2012', 'MM/YYYY')

DATE

DD

TO_DATE('09/03/2012', 'MM/DD/YYYY')

DATE

Day

TO_DATE('Sunday, the 10th', 'Day, the DDth')

DATE

¹ https://www.postgresql.org/docs/12/functions-formatting.html

Masques de format de date avec TO_CHAR()