[MySQL] TimeStamp, DateTime 필드

오랜만에 포스팅을 하게 되었다.

오늘 DB 쿼리를 짜게 되던중에 시간별로 되어 있는 리포트의 3시간 또는 12시간 등에 따라서 GroupBy를 해야 하는 상황이 발생하였다.

현재 Time 필드의 타입은 DateTime으로 되어 있었다.

따라서 쿼리로 Group By를 하기 위해서 리서치를 하던중에 다음과 같은 해법을 찾았다.

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2017-08-11 11:00:00') / 10800) * 10800);

위의 쿼리를 간단하게 설명하자면,

1. (UNIX_TIMESTAMP('2017-08-11 11:00:00'))

현재 텍스트의 시간을 UNIX_TIMESTAMP로 변환한다.

2.  나누기 10800

3시간(60*60*3)으로 나눠서 분,초,밀리세컨드 등을 날려버린다. 

3. FLOOR 

나누기 전의 값을 찍어 보면 알겠지만 소수점 자리수등이 포함되어 있다. 

4. 곱하기 10800

원래 시간을 구하기 위해 다시 구하고자 하는 시간의 값을 곱해준다

EX) 68초의 시간을 10초단위로 묶는 다고 한다면,  68 / 10 = 6  그리고 다시 10을 곱해서 60을 만들어주는건다.

그런데 이때 문제가 발생했다. 

DB로 부터 읽어온 날짜를 계산하고 나면 결과날짜의 값이 +09:00 가 되어 있는것이다. 

이때 09:00 라는 숫자가 타임존을 의미하는것 같아서 이것저것 테스트 해보았다.

시간단위로 구할때는 문제를 발견하지 못하였다.

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2017-08-11 11:00:00') / 3600) * 3600);

그런데 일자가 바뀌니까 같은 문제가 발생하는것이였다.

SELECT '2017-08-11 11:00:00', FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP('2017-08-11 11:00:00') / 86400) * 86400);

그래서 결론적으로 생각한것은

UNIX_TIMESTAMP로 변환한 값이 타임존에 영향을 받지 않고 그냥 들어가는데, 보일때만 타임존을 적용해서 보이는것 같았다.

무슨 말이냐면, 

UNIX_TIMESTAMP("2017-08-11 11:00:00") 을 하면 내가 원했던 타임의 값은 1502416800 인데,

실제로는 로컬 타임 기준이 아닌 GMT타임 기준으로 변환되어서 1502449200로 변환되는것같았다. 그래서 

SELECT FLOOR(UNIX_TIMESTAMP('2017-08-11 11:00:00'));

직접 타임스탬프를 찍어 보았으나 정상적으로 작동 하였다. 

SELECT FLOOR(UNIX_TIMESTAMP('2017-08-11 11:00:00') / 86400) * 86400;

그래서 나눈다음에 버림을 하고 다시 곱하였더니 바로 문제의 +09:00시간이 추가되는것이다.

1502416800 / 86400 =  17389.0833

소수점 자리수가 버림 되면서 해당 문제가 발생한것으로 결론을 내렸다.

그래서 결론은 

SELECT '2017-08-11 11:00:00', FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP('2017-08-11 11:00:00')  + 32400) / 86400) * 86400 - 32400);

타임존을 더해주고 빼주었다.

사실 CONVERT_TZ 함수를 쓰는게 더 좋을것 같으나.. 그냥 9시간을 더해주고 빼주었따. 

도움이 되시는 분들이 있기를