MySQL 날짜, 시간 더하고 빼기 및 타임존 변경(DATE_ADD, DATE_SUB, CONVERT_TZ)

MySQL 날짜, 시간 더하고 빼기 및 타임존 변경

DATA_ADD, DATA_SUB, CONVERT_TZ

mysql에서 날짜 또는 시간을 더하고 빼는 함수인 'DATA_ADD', 'DATA_SUB'와 타임존을 변경할 때 사용되는 'CONVERT_TZ' 함수에 대한 사용법을 정리한 내용입니다.

 

---

0. time zone 확인

@@time_zone, @@system_time_zone

SELECT @@time_zone, @@system_time_zone;

먼저 위 쿼리를 통해 mysql에 설정된 time_zone 정보를 조회하였습니다.

time_zone이 SYSTEM으로 설정되어 있다는 것은 별도의 타임존이 설정되어 있지 않다는 것이며, 시스템의 타임존을 사용하겠다는 것인데요.

해당 테스트 환경에 설정된 mysql의 time zone은 KST(Korea Standard Time)이라는 것을 알 수 있습니다.

 

 

---

1. DATA_ADD, DATA_SUB

이어서 mysql에서 날짜 또는 시간을 더하고 빼는 데 사용되는 함수인 DATA_ADD, DATA_SUB를 살펴보겠습니다.

 

 

- 시간 더하기

DATA_ADD(기준 날짜 시간, INTERVAL value interval_type)

DATE_ADD는 기준 날짜에 입력된 시간(또는 기간)을 더하는 함수입니다.

여기서 value는 더할 시간 값이며, interval_type은 시간 유형을 의미하는데요. interval_type에 들어갈 수 있는 유형으로는 MICROSECOND, SECOND, MINUTE, HOUR, DAY, MONTH, YEAR가 있습니다.

 

//현재 시간 조회 
SELECT NOW();
// 2023-04-07 00:00:00

//현재 시간에서 한 시간 더하기
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);
// 2023-04-07 01:00:00

//현재 시간에서 1일 더하기
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
// 2023-04-08 00:00:00

(date_add 예시)

 

 

- 시간 빼기

DATE_SUB(기준 날짜 시간, INTERVAL value interval_type)

DATE_SUB는 기준 날짜에 입력된 시간(또는 기간)을 빼는 함수입니다.

DATE_ADD와 마찬가지로 value는 뺄 시간 값을 나타내고, interval_type은 시간 유형을 의미합니다.

 

//현재 시간 조회 
SELECT NOW();
// 2023-04-07 00:00:00

//현재 시간에서 10분 빼기
SELECT DATE_SUB(NOW(), INTERVAL 10 MINUTE);
// 2023-04-06 23:50:00

//현재 시간에서 한 달 빼기
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
// 2023-03-07 00:00:00

(date_sub 예시)

 

 

***

여기서 시간 유형의 조합을 통해 조금 더 향상된 기능을 적용할 수도 있는데요.

시간 유형의 조합이라고 하면 위에서 적용하던 interval_type 여러 개를 함께 적용시키는 것을 의미하며, 예시는 아래와 같습니다.

 

//현재 시간 조회 
SELECT NOW();
// 2023-04-07 00:00:00

//현재 시간에서 1시간 20분 10초 더하기
SELECT DATE_ADD(NOW(), INTERVAL '1:20:10' HOUR_SECOND);
// 2023-04-07 01:20:10

//현재 시간에서 1년 2개월 더하기
SELECT DATE_ADD(NOW(), INTERVAL '1-2' YEAR_MONTH);
// 2024-06-07 00:00:00

위와 같은 형식으로 적용할 수 있는 시간 유형의 조합은 아래와 같습니다.

 

 

조합 매개변수	형식	예시
SECOND_MICROSECOND	초.마이크로초	30.5000
MINUTE_MICROSECOND	분:초.마이크로초	10:30.5000
MINUTE_SECOND	분:초	10:30
HOUR_MICROSECOND	시간:분:초.마이크로초	1:10:30.5000
HOUR_SECOND	시간:분:초	1:10:30
HOUR_MINUTE	시간:분	1:10
DAY_MICROSECOND	일 시간:분:초:마이크로초	3 1:10:30.5000
DAY_SECOND	일 시간:분:초	3 1:10:30
DAY_MINUTE	일 시간:분	3 1:10
DAY_HOUR	일 시간	3 1
YEAR_MONTH	년-월	1-2

 

---

2. CONVERT_TZ

다음으로 타임존 변경 시 사용되는 CONVERT_TZ 함수를 살펴보겠습니다.

 

CONVERT_TZ(기준 날짜 시간, from_tz, to_tz)

CONVERT_TZ 함수는 기준 날짜 시간(datetime) 값을 from_time_zone가 제공하는 타임존에서 to_time_zone이 제공하는 타임존으로 변환하여 그 결과 값을 반환하는 함수입니다.

 

from_tz는 변환 전 시간대를 의미하며 '+00:00'의 형식 또는 'GMT', 'MET', 'UTC' 등의 값을 넣을 수 있습니다.

(Greenwich Mean Time, Middle European Time)

 

to_tz는 변환될 시간대를 의미하며 from과 마찬가지로 '+00:00'의 형식 또는 'GMT', 'MET', 'UTC' 등으로 넣을 수 있습니다.

 

***

주의해야 할 점으로는 from_tz에서 UTC로 변환될 때 값이 지원되는 TIMESTAMP 범위('1970-01-01 00:00:01'에서 '2038-01-19 05:14:07' UTC)를 벗어나면 변환이 수행되지 않습니다.

또한 인수가 유효하지 않은 경우에는 NULL을 반환하게 됩니다.

(Database에 timezone 관련 테이블이 없는 경우도 결과로 NULL 값을 반환합니다.)

 

 

//현재 시간 조회 
SELECT NOW();
// 2023-04-07 00:00:00

//현재 시간(KST)을 UTC로 변환하기
SELECT CONVERT_TZ(NOW(), '+00:00', '-09:00');
// 2023-04-06 15:00:00

 

 

 

< 참고 자료 >

https://needjarvis.tistory.com/793

https://mariadb.com/kb/en/convert_tz/

https://stackoverflow.com/questions/14454304/convert-tz-returns-null/14454465