PostgreSQL - DATE_TRUNC 알아보기

postgresql, 날짜형식, 더하기, 빼기, 쿼리, SQL

날짜 자르기 어떻게 하면 좋을까요?

쿼리를 사용하다 보면 날짜 형식을 서로 다른 형식으로 되어있는 것을 비교하는 경우가 많이 있습니다. 이럴 때 쉽게 자를 수 있는 방법이 있으면 좋을 거라는 생각을 하게 됩니다. 날짜 비교를 할 수 있는 다양한 방법을 지원을 하고 있는데 이번엔 그중 DATE_TRUNC에 대해서 알아보도록 하겠습니다.

목차

DATE_TRUNC

사용해보기

PostreSQL기준으로 설명을 먼저 드리면 공식문서[#]를 참고하여 설명합니다.

DATE_TRUNC(FIELD, SOURCE [, TIME_ZONE ])

필드(Field)에 넣을 수 있는 것은 아래와 같이 존재하고 있습니다.

1. microseconds
2. milliseconds
3. second
4. minute
5. hour
6. day
7. week
8. month
9. quarter
10. year
11. decade
12. century
13. millennium

위와 같이 총 13가지를 넣을 수 있는데 보통 흔히 사용을 하는 것은 시간, 분, 초, 일, 월, 년도가 될 거라고 생각합니다. 그러면 이걸 활용하여 어떻게 자를 수 있는지 확인을 해보도록 하겠습니다.

-- YEAR
SELECT DATE_TRUNC('YEAR', NOW());
-- RUN : 2023-01-01T00:00:00Z

-- MONTH
SELECT DATE_TRUNC('MONTH', NOW());
-- RUN : 2023-11-01T00:00:00Z

-- DAY
SELECT DATE_TRUNC('DAY', NOW());
-- RUN : 2023-11-04T00:00:00Z

-- HOUR
SELECT DATE_TRUNC('HOUR', NOW());
-- RUN : 2023-11-04T23:00:00Z

-- MINUTE
SELECT DATE_TRUNC('MINUTE', NOW());
-- RUN : 2023-11-04T23:39:00Z

-- 시간 절사의 경우 DD HH24, MI등을 DATE_TRUNC에 넣으면 조금 더 정밀한 시간 비교를 통해 원하는
-- 조건등을 만들 수 있습니다.

DATE_TRUNC를 사용을 하여 조회를 하게 되면 위와 같이 조회가 되는 것을 확인을 할 수 있습니다. 그러면 여기서 조금 더 응용을 해서 날짜를 더하거나 뺄 수 있는 방법이 있을까라는 생각을 하게 되면 당연하게 하는 방법이 있습니다. 이럴 때 쉽고 간편하게 INTERVAL이라는 것을 사용을 하면 쉽게 할 수 있습니다.

OperatorDescriptionExample(s)

date + integer → date Add a number of days to a date date '2001-09-28' + 7 → 2001-10-05

date + interval → timestamp Add an interval to a date date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00

date + time → timestamp Add a time-of-day to a date date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00

interval + interval → interval Add intervals interval '1 day' + interval '1 hour' → 1 day 01:00:00

timestamp + interval → timestamp Add an interval to a timestamp timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00

time + interval → time Add an interval to a time time '01:00' + interval '3 hours' → 04:00:00

- interval → interval Negate an interval - interval '23 hours' → -23:00:00

date - date → integer Subtract dates, producing the number of days elapsed date '2001-10-01' - date '2001-09-28' → 3

date - integer → date Subtract a number of days from a date date '2001-10-01' - 7 → 2001-09-24

date - interval → timestamp Subtract an interval from a date date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00

time - time → interval Subtract times time '05:00' - time '03:00' → 02:00:00

time - interval → time Subtract an interval from a time time '05:00' - interval '2 hours' → 03:00:00

timestamp - interval → timestamp Subtract an interval from a timestamp timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00

interval - interval → interval Subtract intervals interval '1 day' - interval '1 hour' → 1 day -01:00:00

timestamp - timestamp → interval Subtract timestamps (converting 24-hour intervals into days, similarly to justify_hours()) timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' → 63 days 15:00:00

interval * double precision → interval Multiply an interval by a scalar interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00

interval / double precision → interval Divide an interval by a scalar interval '1 hour' / 1.5 → 00:40:00

위 내용은 PostreSQL[#]의 내용 중 일부입니다.

-- INTERVAL을 사용을 하여 날짜 비교를 위해 더하거나 빼는 방법

SELECT DATE_TRUNC('DAY', NOW()) - INTERVAL '1 DAY';
-- 위 쿼리를 실행을 하게 되면 오늘 날짜에서 하루를 뺀 날짜를 가져오게 되며, DATE_TRUNC를 사용을
-- 하였기 때문에 뒤에 DAY 외엔 가져오질 않아 날짜 비교를 하기 쉽습니다.

INTERVAL에도 똑같이 DAY 대신 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND를 넣어 비교를 할 수 있습니다. 당연히 비교를 하기 위해선 날짜 형식이든 문자열 형식이든 맞추는 것이 좋기 때문에 TO_CHAR(), TO_DATE()를 통해 변경 후 비교해야 합니다.

마무리

시간을 자르고 비교할 수 있는 방법에 대해서 알아봤습니다. 여러분은 어떤 방법을 통해 날짜를 비교하거나 원하는 날짜 모습을 나오도록 하는가요? 오라클에서도 사용을 할 수 있으니 여러 방법을 사용을 해보시는 것을 적극 추천해 드리며 혹시나 궁금한 사항이나 내용에 문제가 있다면 댓글을 통해 남겨주시면 답변드리도록 하겠습니다.