[PostgreSQL] 문자열에서 날짜/시간 변환 및 처리 과정

1. 문자열에서 날짜/시간으로의 변환

PostgreSQL의 날짜형태의 칼럼을 조회할 때, 종종 정확한 날짜 형태를 사용하는 것이 아닌, 문자열, 혹은 숫자 형태로 간편하게 조회하는 경우가 있다. 예를 들어 2024/05/02 이후의 값을 조회할 때 다음 두 가지 조회 방법을 사용할 수 있다.

date_column > '20240502'
date_column > TO_DATE('20240502', 'YYYYMMDD')

예제와 같이 PostgreSQL은 일련의 문자/숫자열을 조건에 맞는 날짜형으로 자동으로 디코딩을 해주는데, 문자열을 인식하는 상세 과정을 순서대로 알아보자.

2. 문자열에서 날짜/시간으로의 디코딩 과정

2-1. 문자열을 토큰으로 분리하고 각 토큰을 시간, 시간대, 또는 숫자로 분류한다.

예제들에서는 정상적으로 날짜 및 시간이 변환되는지 확인하기 위해 강제로 TIMESTAMP 및 DATE로 형 변환을 하였지만, 날짜 형태의 데이터와 문자열 그대로를 비교하여도 날짜 및 시간 비교가 가능하다.

• 숫자 토큰이 ":"를 포함한다면, 시간 문자열로 인식되며, 하나라도 발견되면 이후의 모든 숫자와 콜론은 시간 문자열의 일부로 취급

SELECT '20240202 13:45:30'::TIMESTAMP

13:45:30에 ":"를 포함하였으니 해당 토큰 전체를 시간 문자열로 취급하여, 13시 45분 30초로 해석된다. (hh:mm, hh:mm:ss 등의 표준규격에 맞는 경우에만)

• 숫자 토큰에 하이픈(-), 슬래시(/) 또는 두개이상의 점(.)이 포함되어 있으면 날짜 문자열 취급

SELECT '2024-05-02'::DATE;
SELECT '05/02/2024'::DATE;
SELECT '02.05.2024'::DATE;

yyyy mm dd의 표준 규격에 맞는 경우 모두 날짜 형태로 해석된다.

• 이미 날짜 토큰이 확인된경우 문자열을 시간대 이름 (ex America/New_York)으로 해석

SELECT '2023-12-25 America/New_York'::TIMESTAMP WITH TIME ZONE;

2023-12-25 America/New_York 은, 앞부분에서 이미 날짜 토큰이 확인되었기에, 뒷부분은 시간대 이름으로 해석된다.

• 토큰이 숫자만으로 구성되어 있으면 단일필드이거나 ISO 8601 형식의 날짜로 해석된다.

SELECT '19990113'::DATE;

19990113 = 1999년 1월 13일 또는 (141516 = 14시 15분 16초)

• 토큰이 +, -로 시작하면 숫자 시간대 또는 특별필드이다.

+0200 : UTC보다 2시간 빠른 시간대

-0500 : UTC보다 5시간 늦은 시간대

+15 : 현재 날짜로부터 15일 후 날짜

-3 : 현재 날짜로부터 3일 전 날짜

SELECT '20240202 12:00 +0200'::TIMESTAMP WITH TIME ZONE;

2-2. 토큰이 알파벳 문자열이라면 가능한 문자열 사전을 조회한다.

• 토큰이 알려진 시간대 약어 중에 일치하는 게 있는지 확인

SELECT '20240202 12:00 PM EST'::TIMESTAMP WITH TIME ZONE;

"12:00 PM EST" 에서 EST는 동부표준시(Eastern Standard Time)의 약어로, UTC 오프셋 매핑 딕셔너리에 포함되어 있어 사용 가능

• 발견된 문자열이 없으면 내부 테이블을 검색하여 토큰을 특별 문자열 (ex, today, Thursday, January) 혹은 at, on 같은 조사와 매칭시킨다

SELECT 'Today'::DATE + 1;

'Today'::date + 1 은 내일 날짜를 반환한다.

• 문자열이 위 두조건에 부합하지 않는다면 에러 발생

2-3. 토큰이 숫자, 숫자 필드로만 이루어져 있을 때

• 6 , 8자리이며 다른 날짜 필드가 발견되기 전이라면 날짜 형태로 해석(YYYYMMDD 혹은 YYMMDD)

SELECT '20240201'::DATE
SELECT '240201'::DATE

20240201, 240201 모두  2024년 02월 01일로 해석된다.

• 토큰이 3자리 숫자이고, 이미 연도가 발견되었다면, 그 해의 n번째 일수로 해석

SELECT '2024 021'::DATE;

'2024 021'::date 은 2024년의 21번째 날로 2024년 01월 21일로 해석된다.

• 네 자리 또는 여섯 자리 숫자이고 이미 날짜가 발견되었다면, 시간(HHMM 또는 HHMMSS)으로 해석

SELECT '20240502 123422'::TIMESTAMP;

'20240502 123422'::timestamp 에서 앞부분 토큰에 날짜는 이미 발견되었기에, 6자리 숫자가 시간으로 해석된다. (2024년 05월 02일의 12:34:22)

• 세 자리 이상의 숫자이고 아직 날짜 필드가 발견되지 않았다면, 연도로 해석 (기본적으로 yy-mm-dd 순서이며 서버의 DateStyle 설정에 따라 mm-dd-yy, dd-mm-yy 등으로 변경할 수 있다.

SELECT '240522'::TIMESTAMP

'240522'::timestamp는 처음 발견된 날짜형 토큰이기에 기본 설정인 yy-mm-dd에 따라 해석된다. (2024년 05월 02일)

• 월/일 필드가 범위를 벗어나거나 유효하지 않은 값이라면 오류 발생

2-4. BC(기원전) 설정

• bc 문자열을 통해 기원전 설정이 가능하며, BC(기원전)이 설정되어 있다면, 내부적으로는 연도를 음수로 바꾸고 1을 더한 후 저장한다. (그레고리력에는 연도 0이 없으므로 수치상 1 BC는 연도 0이 됨)

SELECT 'bc 1200201'::DATE

• BC가 지정되지 않고 연도 필드가 두 자리 숫자인 경우, 연도를 4자리로 조정한다. 해당 필드가 70보다 작으면 2000을 더하고, 그렇지 않으면 1900을 더한다.
  • '800502'::date - 년도필드(80)가 70보다 크기에 1900을 더한 '1980년 05월 02일'을 의미 
  • '240502'::date - 년도필드(24)가 70보다 작기에 2000을 더한 '2024년 05월 02일'을 의미

SELECT '800502'::DATE ;

SELECT '240502'::DATE ;

 

 

 

 

 

 

 

참고 

https://www.postgresql.org/docs/16/datetime-input-rules.html