[PostgreSQL/MYSQL] 날짜/시간 계산(INTERVAL타입, age함수) (DATEDIFF, TIMESTAMPDIFF)

DATASET QUERY

DROP TABLE IF EXISTS mst_users_with_dates;
CREATE TABLE mst_users_with_dates (
    user_id        varchar(255)
  , register_stamp varchar(255)
  , birth_date     varchar(255)
);

INSERT INTO mst_users_with_dates
VALUES
    ('U001', '2020-02-28 10:00:00', '2000-02-29')
  , ('U002', '2020-02-29 10:00:00', '1992-03-29')
  , ('U003', '2020-03-01 10:00:00', '2002-01-29')
  , ('U004', '2020-03-01 11:00:00', '1988-08-21')
  , ('U005', '2020-03-02 20:00:00', '1995-10-07')
  , ('U006', '2020-03-03 10:00:00', '2010-01-01')
;

 

미래/과거의 날짜/시간 계산

interval 타입은 연/월/일 등의 단위와 결합하여 날짜와 시간 연산이 가능한 타입이다.

select
	user_id
	, register_stamp::timestamp as register_stamp
	, register_stamp::timestamp + '1 hour'::interval as after_1hour
	, register_stamp::timestamp - '30 minutes'::interval as before_30minutes
	
	, register_stamp::date as register_date
	, (register_stamp::date + '1 day'::interval) as after_1day
	, (register_stamp::date - '1 month'::interval) as before_1month
from mst_users_with_dates;

 

날짜들끼리 차이 계산

select 
	user_id
	, current_date as today
	, register_stamp::date as register_date
	, current_date - register_stamp::date as diff
from mst_users_with_dates;
	

CF) MYSQL

DATEDIFF

SELECT DATEDIFF('2022-03-19 11:44:59', '2020-03-01 00:00:00');

=> 결과 : 748

TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(minute, '2020-03-01 00:00:00', '2022-03-19 11:44:59');

- TIMESTAMPDIFF(형식, 앞날짜, 뒷날짜) 순으로 세팅한다

=> 결과 1077824

형식에 아래와 같이 세팅 가능하다

• year : 년
• quarter : 분기
• month : 월
• week : 주
• day : 일
• hour : 시
• minute : 분
• second : 초

 

나이계산(Age)

PostgreSQL은 AGE함수를 지원합니다, 연도를 넣어주면 나이를 출력합니다. 특정날짜를 지정하면 해당 날짜에서의 나이를 출력합니다.

select 
	user_id
	, current_date as today
	, register_stamp::date as register_date
	, birth_date::date as birth_date
	, EXTRACT(year from age(birth_date::date)) as current_age
	, EXTRACT(year from age(register_stamp::date, birth_date::date)) as age_diff
from mst_users_with_dates;