[mariadb] CONVERT_TZ 사용 (feat. null return)

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

syntax

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ()는 datetime 값 dt 를 from_tz 가 제공하는 시간대에서 to_tz 가 제공하는 시간대로 변환 하고 결과 값을 반환합니다.

tz에는 '+09:00' 이나 'America/New_York' 등을 넣을 수 있다.

example

SELECT CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00');
+-----------------------------------------------------+
| CONVERT_TZ('2016-01-01 12:00:00','+00:00','+10:00') |
+-----------------------------------------------------+
| 2016-01-01 22:00:00                                 |
+-----------------------------------------------------+

 

null return (feat. ubuntu 20.04)

근데 했는데 null이 나오는 경우가 있다.

timestamp 테이블이 없거나 안에 데이터가 없어서 그렇다.

 

서버에 대한 충분한 권한이 있는 경우 zoneinfo 파일이 /usr/share/zoneinfo(CentOS, Red Hat Enterprise Linux, Debian 설정에 있음)에 있다고 가정하고 명령줄에서 다음과 같은 명령을 실행할 수 있습니다.

# bash
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

error

ERROR 1140 (42000) at line 2: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

위와 같이 에러가 나는 경우 sql_mode에 ONLY_FULL_GROUP_BY가 있는 경우이다.

잠깐 옵션을 꺼두도록 하자.

 

-- sql
-- sql mode 확인
SELECT @@GLOBAL.SQL_MODE;

-- ONLY_FULL_GROUP_BY 항목 제거
SET GLOBAL sql_mode=(SELECT REPLACE(@@GLOBAL.SQL_MODE,'ONLY_FULL_GROUP_BY',''));

# bash
# mysql databse에 권한이 있는 계정으로 접속
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

-- sql
-- ONLY_FULL_GROUP_BY 항목 추가
SET GLOBAL sql_mode=(SELECT CONCAT(@@GLOBAL.SQL_MODE,', ONLY_FULL_GROUP_BY'));

 

추가 정보

+ 시스템 timezone 설정

https://mariadb.com/kb/en/time-zones/

Time Zones

 

참조

https://electrictoolbox.com/mysql-convert-tz-returns-null/

MySQL CONVERT_TZ returns null | The Electric Toolbox Blog

https://www.thisprogrammingthing.com/2015/what-to-do-when-convert_tz-returns-null/

What To Do When convert_tz Returns Null

https://velog.io/@ehrbs2021/MySQL-CONVERTTZ%ED%95%A8%EC%88%98-null-%EB%B0%98%ED%99%98-%ED%95%B4%EA%B2%B0

MySQL CONVERT_TZ함수 null 반환 해결