날짜 파싱과 포맷팅

PostgreSQL 데이터베이스에서 데이터 정제하기

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

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

TEXT 열에는 DATE 기능을 사용할 수 없음
- 날짜 범위 확인
- 날짜 성분 추출
- 날짜 간 간격 계산
DATE(string_date)
- string_date를 DATE 값으로 변환
- DATE('2019-12-01') → DATE 값

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

TO_DATE()로 날짜 파싱하기

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

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;

TO_CHAR()로 날짜 표시하기

기본 날짜 형식:
- YYYY-MM-DD (예: 2012-04-03)
TO_CHAR('2012-04-03', YYYY-DD-MM)
TO_CHAR(date_value, format_string) → 문자열 값

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

날짜 포맷 패턴

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

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

TO_CHAR()의 날짜 포맷 패턴