타임스탬프 파싱과 포맷팅

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

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

PostgreSQL 타임스탬프

 camis    |         name          | inspection_datetime  |            inspection_type            | ... 
 ---------+-----------------------+----------------------+---------------------------------------+-----
 ...      | ...                   | ...                  | ...                                   | ...
 50000458 | BEVERLEY PIZZA & CAFE | 2019-07-08 14:26     | Cycle Inspection / Initial Inspection | ...
 50002521 | JADE PALACE           | 2018-05-14 12:35     | Cycle Inspection / Initial Inspection | ...
 40389732 | GIANDO                | 2017-07-10 13:39     | Cycle Inspection / Re-inspection      | ...
 50044246 | FLEET BAKERY          | 2019-10-29 15:40     | Cycle Inspection / Re-inspection      | ...
 50038120 | SHUN WON FLUSHING     | 2018-07-17 16:20     | Cycle Inspection / Re-inspection      | ...
 ...      | ...                   | ...                  | ...                                   | ...

inspection_datetime: TIMESTAMP 열

TO_TIMESTAMP()로 타임스탬프 파싱

문자열을 TIMESTAMP로 변환
TO_TIMESTAMP(ts_string, format_string) → TIMESTAMP

SELECT
  camis,
  name,
  TO_TIMESTAMP(inspection_datetime, 'YYYY-MM-DD HH24:MI'),
  inspection_type
FROM
  restaurant_inspection;

타임스탬프 문자열 포맷 패턴

TO_TIMESTAMP(ts_string, format)
TO_CHAR(ts_value, format)
TO_DATE() 패턴(YYYY, MM, Day 등) 사용 가능

타임스탬프 문자열 포맷 패턴

TO_TIMESTAMP(ts_string, format)

패턴	TO_TIMESTAMP() 예시
`HH24`	`TO_TIMESTAMP('23', 'HH24')` → `TIMESTAMP`
`HH12`	`TO_TIMESTAMP('01', 'HH12')` → `TIMESTAMP`
`MI`	`TO_TIMESTAMP('18:13', 'HH24:MI')` → `TIMESTAMP`
`SS`	`TO_TIMESTAMP('33:20', 'MI:SS')` → `TIMESTAMP`
`PM` 또는 `AM`	`TO_TIMESTAMP('5:35AM', 'HH12:MIPM')` → `TIMESTAMP`

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

EXTRACT() 함수

EXTRACT(time_unit FROM time_value)

time_value - DATE 또는 TIMESTAMP

EXTRACT() 함수

SELECT
  camis,
  name, 
  inspection_datetime,
  EXTRACT('year' FROM inspection_datetime) AS year,
  inspection_type
FROM
  restaurant_inspection;

 camis    |         name          |    inspection_datetime     | year |            inspection_type            | ... 
 ---------+-----------------------+----------------------------+------+---------------------------------------+-----
 ...      | ...                   | ...                        | ...  | ...                                   | ...
 50000458 | BEVERLEY PIZZA & CAFE | 2019-07-08 06:37:46.658905 | 2019 | Cycle Inspection / Initial Inspection | ...
 50002521 | JADE PALACE           | 2018-05-14 03:47:24.474573 | 2018 | Cycle Inspection / Initial Inspection | ...
 40389732 | GIANDO                | 2017-07-10 03:59:12.864428 | 2017 | Cycle Inspection / Re-inspection      | ...
 50044246 | FLEET BAKERY          | 2019-10-29 02:06:33.614964 | 2019 | Cycle Inspection / Re-inspection      | ...
 50038120 | SHUN WON FLUSHING     | 2018-07-17 01:15:04.15666  | 2018 | Cycle Inspection / Re-inspection      | ...
 ...      | ...                   | ...                        | ...  | ...                                   | ...

EXTRACT()의 시간 단위 옵션

시간 단위	EXTRACT() 예시
`year`	`EXTRACT('year' FROM '2020-07-20 16:42:21'::timestamp)` → 2020
`month`	`EXTRACT('month' FROM '2020-07-20 16:42:21'::timestamp)` → 7
`day`	`EXTRACT('day' FROM '2020-07-20 16:42:21'::timestamp)` → 20
`hour`	`EXTRACT('hour' FROM '2020-07-20 16:42:21'::timestamp)` → 16
`minute`	`EXTRACT('minute' FROM '2020-07-20 16:42:21'::timestamp)` → 42
`second`	`EXTRACT('second' FROM '2020-07-20 16:42:21'::timestamp)` → 21

¹ https://www.postgresql.org/docs/current/functions-datetime.html

연습해 봅시다!

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