PostgreSQL 날짜 요일 포맷 출력 및 계산

---

이번 포스팅에서는 (필자기준) 자주 사용 하는 날짜, 요일을 계산하여 출력하는 것을 알아보려한다.

필자가 무식한 탓에 한번에 타입과 포맷을 맞춘적이 없어,

매번 에러 만들고, 구글링하고, 에러 만들고, 구글링하고...

내것이 되지 않고 매번 겉돌았다ㅠㅠ

그래서 이번기회에 확실히 정리해서 알아보도록 하겠다!

 

기본적으로 우리가 많이 사용하는 to_char(날짜, 포맷) 이다.

SELECT to_char('2023-07-25'::date+2, 'yyyy-mm-dd');

-- result
-- 2023-07-27

• 함수 이름에서 알 수 있듯 char 타입으로 바꾸기 때문에, result 타입은 문자열이다.
• to_char 안 쪽 날짜는 date타입이 있어야 하므로, 문자로 이루어진 날짜를 date로 casting 해야 변환 가능!
• 이후 2를 더해 연산 결과 확인해보니 잘된다!

다음은 to_date(날짜, 포맷)이다.

SELECT to_date('2023-07-25', 'yyyy-mm-dd')+2;

-- result
-- 2023-07-27

• Date type에는 숫자를 더하거나 빼서 날짜 계산을 할 수 있다.(but 곱하기, 나누기 X)
• 함수 실행 결과 date 타입이므로 바깥쪽에서 바로 연산이 가능하다.

그리고 to_timestamp(날짜, 포맷)...

SELECT to_timestamp('2023-07-25 14:36:25', 'yyyy-mm-dd hh24:mi:ss')-2;

-- result
-- SQL Error [42883]: ERROR: operator does not exist: timestamp with time zone - integer
-- Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

하지만 Timestamp에서 숫자를 더하거나 빼면 오류가 발생한다.(위에 to_char(날짜, 포맷)도 위와 같이 연산하면 오류 발생)

대략 timestamp의 시간대(time zone, 14:36:25)로 인해 연산을 할 수 없으니,

명시적으로 Casting을 해서 연산하라는 힌트를 주고 있다.

이럴 때 'interval' type을 이용해 연산을 수행한다.

SELECT to_timestamp('2023-07-25 14:36:25', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '2 days';
-- result
-- 2023-07-27 14:36:25.000 +0900

SELECT to_timestamp('2023-07-25 14:36:25', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '6 hours';
-- result
-- 2023-07-25 20:36:25.000 +0900

SELECT to_timestamp('2023-07-25 14:36:25', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '2 days 6 hours';
-- result
-- 2023-07-27 20:36:25.000 +0900

구글링을 하다가 아래와 같이 특이한케이스를  발견해서 출력해보았다.

--INTERVAL '2 days'
SELECT to_timestamp('2023-07-25 14:36:25', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '2 days';
-- result
-- 2023-07-27 14:36:25.000 +0900

--INTERVAL '2' day
SELECT to_timestamp('2023-07-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '2' DAY;
-- result
-- 2023-07-27 00:00:00.000 +0900

--INTERVAL '2' days 로 할 경우
SELECT to_timestamp('2023-07-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + INTERVAL '2' days;
-- result
-- 2023-07-25 00:00:02.000 +0900

INTERVAL '2 days' 일때, 우리가 상식적으로 생각했던 결괏값이 나온다.

INTERVAL '2' DAY 일때, 날짜에 대한 연산만 이루어지고, 시간은 제외되어 정각으로 표시된다.

INTERVAL '2' days 일때, 결괏값은 나오나, 그냥 2초만 더해진 값이 나오게 된다.

 

그리고 이번에 프로젝트를 맡으면서 알게된 EXTRACT 함수!!

EXTRACT(field FROM source)

field란 year, month, day 따위의 날짜/시간 데이터 요소를 말하고,

source란 실제 timestamp 값('2021-01-01 15:00:00' 와 같은 값) 혹은 interval 타입의 값을 의미한다.

만약, date 타입을 전달하게 되면 이는 timestamp 타입으로 캐스팅되어 전달한다.

그래서 결괏값으로는 double precision (0.0) 타입을 반환한다!

 

필요 인자는 아래와 같다!

Field 값	의미
CENTURY	세기(21세기, 20세기)
DAY	1~31에 해당하는 해당 월의 일
DOW	일요일(0) ~ 토요일(6)까지 반환하는 값
DOY	1~366 까지 해당하는 연중일수
EPOCH	1970년 1월 1일 00:00:00 UTC 부터 현재까지의 초
HOUR	0 ~ 23 에 해당하는 시간정보
MILLISECONDS	1/1000에 해당하는 밀리초
MINUTE	0 ~ 59에 해당하는 분 정보
MONTH	1 ~ 12에 해당하는 월 정보
QUARTER	1(1~3월), 2(4~6월), 3(7~9월), 4(10~12월) 분기로 나뉘어지는 정보
SECOND	0 ~ 59에 해당하는 초 정보
WEEK	주 정보 (1월 1일 : 1, 12월 31일: 52~53)
YEAR	연도 정보.

위의 인수에 대한 사용 예제에 따른 결과값을 알아보자!

SELECT EXTRACT('CENTURY'      FROM '2023-07-25 15:23:22.23242'::timestamp); -- 21.0
SELECT EXTRACT('DAY'          FROM '2023-07-25 15:23:22.23242'::timestamp); -- 25.0
SELECT EXTRACT('DOW'          FROM '2023-07-25 15:23:22.23242'::timestamp); -- 2.0
SELECT EXTRACT('DOY'          FROM '2023-07-25 15:23:22.23242'::timestamp); -- 206.0
SELECT EXTRACT('EPOCH'        FROM '2023-07-25 15:23:22.23242'::timestamp); -- 1690298602.23242
SELECT EXTRACT('HOUR'         FROM '2023-07-25 15:23:22.23242'::timestamp); -- 15.0
SELECT EXTRACT('MILLISECONDS' FROM '2023-07-25 15:23:22.23242'::timestamp); -- 22232.42
SELECT EXTRACT('MINUTE'       FROM '2023-07-25 15:23:22.23242'::timestamp); -- 23.0
SELECT EXTRACT('MONTH'        FROM '2023-07-25 15:23:22.23242'::timestamp); -- 7.0
SELECT EXTRACT('QUARTER'      FROM '2023-07-25 15:23:22.23242'::timestamp); -- 3.0
SELECT EXTRACT('SECOND'       FROM '2023-07-25 15:23:22.23242'::timestamp); -- 22.23242
SELECT EXTRACT('WEEK'         FROM '2023-07-25 15:23:22.23242'::timestamp); -- 30.0
SELECT EXTRACT('YEAR'         FROM '2023-07-25 15:23:22.23242'::timestamp); -- 2023.0

앞서 말했듯 결괏값은 double 타입으로 반환되는걸 볼 수 있다.

그리고 더불어 알게된 ISDOW!

NOW()는 포스팅 날짜인 25일 기준이다

SELECT EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '1 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day1
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '2 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day2
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '3 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day3
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '4 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day4
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '5 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day5
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '6 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day6
     , EXTRACT(ISODOW FROM TO_CHAR(NOW()+ INTERVAL '7 day', 'yyyymmdd')::TIMESTAMP )::VARCHAR AS day7;

-- Result
--|day1|day2|day3|day4|day5|day6|day7|
-- ----+----+----+----+----+----+----+
--|3   |4   |5   |6   |7   |1   |2   |

프로젝트에서 기준일을 기점으로 요일을 미리 계산하는 로직을 구현해야 했는데 DB에서 할 수 밖에 없는 상황으로,

위와 같은 쿼리문을 작성하게 되었다.

EXTRACT 함수의 인자 값으로 filed에 ISODOW, source에 timestamp 타입의 날짜가 들어있다.

위에서 소개한 인자로 'DOW'는 일요일(0) ~ 토요일(6) 이지만, 'ISODOW' 같은 경우 월요일(1) ~ 일요일(7)이 된다.

따라서 포스팅 기준일인 25일이 화요일이므로 day1은 3, 즉 수요일을 뜻하고,

day7 까지 순차적으로 날짜가 더해져 2인 화요일이 된다.

 

SQL에서 날짜를 다루는 경우가 종종 많이 나오므로,

한번 정리할 때 알아두고, 조건문이나 반복문 사용하듯이 내가 필요할때마다,

꺼내쓰는게 비용적으로도 효율적일것 같아 이렇게 정리해 보았다.

기억에서 도망치지 않도록, 꼭! 꼭! 포스팅으로 틀어막기!!!!!!!!!

언제나 새로운 정보 공유와 잘못된 정보

비판/지적/태클은 환영입니다!

끝.

Reference

https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-extract/

PostgreSQL EXTRACT: Extracting Year, Month, Day, etc., from a Date