PostgreSQL 날짜 API 다루기( 두 날짜 사이 시간 계산, extract, age 등 )

들어가며

PostgreSQL에서 날짜와 관련된 API를 다루는 방법을 알아봅니다. 다음의 샘플테이블을 먼저 준비합니다.

Sample Table

CREATE TABLE foods (
   id SERIAL PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   brand VARCHAR(255),
   category VARCHAR(255) NOT NULL,
   quantity INT NOT NULL,
   price NUMERIC(10, 2) NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT NOW(),
   expired_at TIMESTAMP NOT NULL
);

INSERT INTO foods (name, brand, category, quantity, price, expired_at)
VALUES
   ('Apple', 'Granny Smith', 'Fruit', 10, 0.99, now() + interval '7 days 20 hours'),
   ('Bread', 'Wonder Bread', 'Bakery', 5, 2.49, now() + interval '4 days'),
   ('Cheese', 'Cheddar', 'Dairy', 20, 3.99, now() + interval '34 days 2 hours 13 minute 30 second'),
   ('Eggs', 'Large Brown', 'Dairy', 30, 1.99, now() + interval '20 days'),
   ('Milk', 'Whole', 'Dairy', 2, 3.49, now() + interval '5 days');

SELECT * FROM FOODS;

id|name  |brand       |category|quantity|price|created_at             |expired_at             |
--+------+------------+--------+--------+-----+-----------------------+-----------------------+
 1|Apple |Granny Smith|Fruit   |      10| 0.99|2023-02-09 17:06:38.540|2023-02-17 13:06:38.540|
 2|Bread |Wonder Bread|Bakery  |       5| 2.49|2023-02-09 17:06:38.540|2023-02-13 17:06:38.540|
 3|Cheese|Cheddar     |Dairy   |      20| 3.99|2023-02-09 17:06:38.540|2023-03-15 19:20:08.540|
 4|Eggs  |Large Brown |Dairy   |      30| 1.99|2023-02-09 17:06:38.540|2023-03-01 17:06:38.540|
 5|Milk  |Whole       |Dairy   |       2| 3.49|2023-02-09 17:06:38.540|2023-02-14 17:06:38.540|

1. 유통기한(expired_at)이 현재일자로부터 7일 이내인 레코드 검색

SELECT NAME
     , EXPIRED_AT 
     , NOW()
  FROM FOODS
 WHERE EXPIRED_AT BETWEEN NOW() AND NOW() + INTERVAL '7 DAYS'

name |expired_at             |now                          |
-----+-----------------------+-----------------------------+
Bread|2023-02-13 17:06:38.540|2023-02-09 17:49:54.283 +0900|
Milk |2023-02-14 17:06:38.540|2023-02-09 17:49:54.283 +0900|

• 현재일자를 timestamp형태로 리턴하는 now()
• INTERVAL 키워드 뒤에 숫자 + 날짜 키워드를 지정하여 표현할 수 있습니다.

2. interval

interval은 날짜의 기간을 나타냅니다. 위에서 본 것 처럼 timestamp 에 가감하여 사용할 수 도 있고
interval 자체로도 사용할 수 있습니다.

select interval '3 month' + interval '2 days' + interval '4 days'

?column?     |
-------------+
3 mons 6 days|

select now() + interval '1 month' + interval '40 days' + interval '100 minutes' 

?column?                     |
-----------------------------+
2023-04-18 19:38:54.976 +0900|

3. age 날짜간 차이 계산

select EXPIRED_AT - created_at as calc 
     , age(expired_at, created_at) as age
     , extract(month from age(EXPIRED_AT, created_at)) as month
     , extract(day from age(EXPIRED_AT, created_at)) as day
     , extract(hour from age(EXPIRED_AT, created_at)) as hour
     , extract(minute from age(EXPIRED_AT, created_at)) as minute
     , extract(second from age(EXPIRED_AT, created_at)) as second
  from foods

calc            |age                  |month|day|hour|minute|second   |
----------------+---------------------+-----+---+----+------+---------+
 7 days 20:00:00|      7 days 20:00:00|    0|  7|  20|     0| 0.000000|
          4 days|               4 days|    0|  4|   0|     0| 0.000000|
34 days 02:13:30|1 mon 6 days 02:13:30|    1|  6|   2|    13|30.000000|
         20 days|              20 days|    0| 20|   0|     0| 0.000000|
          5 days|               5 days|    0|  5|   0|     0| 0.000000|

4. 한국 시간 표기법으로 표현

age에서 출력되는 날짜를 to_char를 사용하여 한국어로 변환할 수 있습니다.

SET LC_TIME = 'ko_KR.UTF-8';
SELECT name
     , age(expired_at, created_at) as age
     , to_char(age(expired_at, created_at), 'MM월 DD일 HH24시 MI분 SS초') as gap_ko
FROM foods;

name  |age                  |gap_ko             |
------+---------------------+-------------------+
Apple |      7 days 20:00:00|00월 07일 20시 00분 00초|
Bread |               4 days|00월 04일 00시 00분 00초|
Cheese|1 mon 6 days 02:13:30|01월 06일 02시 13분 30초|
Eggs  |              20 days|00월 20일 00시 00분 00초|
Milk  |               5 days|00월 05일 00시 00분 00초|

5. date_trunc 활용, 유통기한 월별로 그룹

SELECT DATE_TRUNC('MONTH', EXPIRED_AT) AS MONTH, COUNT(*)
FROM FOODS
GROUP BY MONTH
ORDER BY MONTH;

month                  |count|
-----------------------+-----+
2023-02-01 00:00:00.000|    3|
2023-03-01 00:00:00.000|    2|