안녕하세요. 최문형입니다.
두번째 글을 쓰게 되는군요. 오늘은 쿼리시에 간단한 팁을 하나 말씀드릴까 합니다.
보통 웹프로그램을 하다 보면, 날짜 관련 작업을 하기 위해 스크립트 코드로 온갖 삽질을 하게 됩니다. 오랜만에 쓰지 않다가, 누가 메신저로 물어봐서 찾아본 쿼리로 날짜간 차이 구하기, 시간차이 구하기 등의 팁을 올려볼까 합니다.
1. 사전에 익혀두어야 할 지식
보통 Oracle에서의 데이터타입이 내부적으로 저장되는 부분에 관해선 사실 잘 모르는 경우가 많습니다. SQL만 잘 알면 되지.. 하는 분들이 워낙 많아서..(저도 공부하기 전까진 잘 몰랐죠)
이 팁을 이해하기 위해 필요한 지식은 Oracle의 Date 타입의 형태가 대체 어떤 넘이냐... 하는 것입니다.
사실, DATE 타입과 NUMBER 타입은 같은 형태랍니다. Java를 공부해본 분이나 VB 등의 클라이언트 프로그램을 공부해본 분은 아시겠지만, 시간이라는 것은 [기준점으로부터의 기간을 숫자로 환산하여 표시가 가능한] 타입의 데이터입니다. Oracle 등의 프로그램에서는 이 기준점을 1970년 1월 1일로 잡고 있습니다.
이 날짜를 기준으로, 1일이 1인 단위로 수치를 변경하여 저장합니다. 즉, NUMBER 타입인 데이터는 DATE로 바로 변환도 어렵지 않다는 것이죠.
2. 계산의 기본 아이디어
(1) 두 DATE 타입의 날짜의 차이 구하기
이게 뭔 소리냐? 한다면, 두 DATE 타입의 시간차를 구하기 위해서, 날짜가 뒤인 값에서 앞인 값을 뺍니다. 그러면 이 데이터가 NUMBER 타입으로 나오게 되는데, 이 데이터에서 소수점을 떼고 나면 [날짜의 차이]만이 나오게 됩니다.
(예를 들어, 2000년 5월 2일 오후 1시에서, 2000년 5월 3일 오후 2시 30분 사이의 시간은 1일하고도 1시간 30분 차이가 아니겠습니까? 이 경우에 시간/분 간격은 중요하지 않다면, 시간/분 차이를 버리고 1일 차이라고 말하게 되겠죠. 이 간격이 바로 날짜차이가 되는 겁니다)
자, 백문이 불여일견. 간단한 날짜차이를 구하는 SQL문을 봅시다.
SELECT TRUNC ( TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS')
- TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS')
)
as day_diff_cnt from dual;
실행시켜보세요. 얼마가 나오나요?
(2) 두 DATE 타입의 진정한 날짜차이 구하기
[진정한] 날짜차이 라니? 라고 말하실 지 모르지만, 이런 경우가 존재할 수 있습니다.
- 2000년 5월 2일 오후 1시에서, 2000년 5월 3일 오후 2시 30분 사이의 날짜 차이
- 2000년 5월 2일 오후 3시에서, 2000년 5월 3일 오후 2시 30분 사이의 날짜 차이
위 (1)의 계산대로 하게 되면, 첫번째 경우에는 1이 나오지만, 두번째 경우는 0이 나옵니다(확인해보세요) 왜그럴까요?
원인은 간단합니다. 두 시간의 차이가 24시간이 되지 않기 때문입니다. 그런데, 보통 저런 경우 우리는 양쪽 모두 하루 차이 나는 걸로 생각하죠. 바로 이런 계산법을 가리켜 [진정한] 날짜차이라고 말을 하겠습니다.
이 경우의 팁은 사실 별거 없습니다. 00시 00분으로 모두 바꾸어치기해서 차이를 계산해버리면 되겠죠. 백문이 불여일견!
SELECT TRUNC ( TO_DATE(TO_CHAR(TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
- TO_DATE(TO_CHAR(TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
)
as day_diff_cnt from dual;
앞의 쿼리문과 비교해서, 좀 복잡해 보이지만, 사실 아이디어는 여기에 있습니다.
비교대상의 데이터를 00시00분의 DATE 값으로 변경 -> 차이 계산 -> 소수점 이하 날리기 입니다.
비교대상의 데이터를 00시00분의 DATE값으로 변경하는 부분은 아래와 같습니다.
TO_DATE(TO_CHAR(TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
그 다음은 차이를 구하고 소수점 이하를 날려버리는 방법은 이전과 같습니다.
시험해보세요. 양쪽 모두 잘 되죠?
(3) 시간차이를 시간/분/초 단위로 환산하기
이는 어렵지 않으실 겁니다. 원래의 단위가 1일이 1인 단위인 NUMBER 타입이므로,
- 시간 단위를 계산하기 위해선 1시간을 1로 바꿔주면 됩니다. 즉 24를 곱하면 되죠.
- 분 단위로 계산하기 위해선 24 * 60 을 곱해주면 되고,
- 초 단위로 계산하기 위해선 24 * 60 * 60 을 곱해주면 되겠지요.
SELECT TRUNC ( ( TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS')
- TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS')
)
* 24
) AS hour_diff_cnt
FROM DUAL;
계산결과를 확인해보도록 하세요.
(4) 그럼 날짜/시간/분/초 단위로 환산하기는 어떻게 하느냐? 이건 숙제로 내주겠습니다.
이 정도 해설해드렸으면 조금 궁리해보시면 쉽게 해답을 내놓을 수 있을 겁니다.
두번째 글을 쓰게 되는군요. 오늘은 쿼리시에 간단한 팁을 하나 말씀드릴까 합니다.
보통 웹프로그램을 하다 보면, 날짜 관련 작업을 하기 위해 스크립트 코드로 온갖 삽질을 하게 됩니다. 오랜만에 쓰지 않다가, 누가 메신저로 물어봐서 찾아본 쿼리로 날짜간 차이 구하기, 시간차이 구하기 등의 팁을 올려볼까 합니다.
1. 사전에 익혀두어야 할 지식
보통 Oracle에서의 데이터타입이 내부적으로 저장되는 부분에 관해선 사실 잘 모르는 경우가 많습니다. SQL만 잘 알면 되지.. 하는 분들이 워낙 많아서..(저도 공부하기 전까진 잘 몰랐죠)
이 팁을 이해하기 위해 필요한 지식은 Oracle의 Date 타입의 형태가 대체 어떤 넘이냐... 하는 것입니다.
사실, DATE 타입과 NUMBER 타입은 같은 형태랍니다. Java를 공부해본 분이나 VB 등의 클라이언트 프로그램을 공부해본 분은 아시겠지만, 시간이라는 것은 [기준점으로부터의 기간을 숫자로 환산하여 표시가 가능한] 타입의 데이터입니다. Oracle 등의 프로그램에서는 이 기준점을 1970년 1월 1일로 잡고 있습니다.
이 날짜를 기준으로, 1일이 1인 단위로 수치를 변경하여 저장합니다. 즉, NUMBER 타입인 데이터는 DATE로 바로 변환도 어렵지 않다는 것이죠.
2. 계산의 기본 아이디어
(1) 두 DATE 타입의 날짜의 차이 구하기
이게 뭔 소리냐? 한다면, 두 DATE 타입의 시간차를 구하기 위해서, 날짜가 뒤인 값에서 앞인 값을 뺍니다. 그러면 이 데이터가 NUMBER 타입으로 나오게 되는데, 이 데이터에서 소수점을 떼고 나면 [날짜의 차이]만이 나오게 됩니다.
(예를 들어, 2000년 5월 2일 오후 1시에서, 2000년 5월 3일 오후 2시 30분 사이의 시간은 1일하고도 1시간 30분 차이가 아니겠습니까? 이 경우에 시간/분 간격은 중요하지 않다면, 시간/분 차이를 버리고 1일 차이라고 말하게 되겠죠. 이 간격이 바로 날짜차이가 되는 겁니다)
자, 백문이 불여일견. 간단한 날짜차이를 구하는 SQL문을 봅시다.
SELECT TRUNC ( TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS')
- TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS')
)
as day_diff_cnt from dual;
실행시켜보세요. 얼마가 나오나요?
(2) 두 DATE 타입의 진정한 날짜차이 구하기
[진정한] 날짜차이 라니? 라고 말하실 지 모르지만, 이런 경우가 존재할 수 있습니다.
- 2000년 5월 2일 오후 1시에서, 2000년 5월 3일 오후 2시 30분 사이의 날짜 차이
- 2000년 5월 2일 오후 3시에서, 2000년 5월 3일 오후 2시 30분 사이의 날짜 차이
위 (1)의 계산대로 하게 되면, 첫번째 경우에는 1이 나오지만, 두번째 경우는 0이 나옵니다(확인해보세요) 왜그럴까요?
원인은 간단합니다. 두 시간의 차이가 24시간이 되지 않기 때문입니다. 그런데, 보통 저런 경우 우리는 양쪽 모두 하루 차이 나는 걸로 생각하죠. 바로 이런 계산법을 가리켜 [진정한] 날짜차이라고 말을 하겠습니다.
이 경우의 팁은 사실 별거 없습니다. 00시 00분으로 모두 바꾸어치기해서 차이를 계산해버리면 되겠죠. 백문이 불여일견!
SELECT TRUNC ( TO_DATE(TO_CHAR(TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
- TO_DATE(TO_CHAR(TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
)
as day_diff_cnt from dual;
앞의 쿼리문과 비교해서, 좀 복잡해 보이지만, 사실 아이디어는 여기에 있습니다.
비교대상의 데이터를 00시00분의 DATE 값으로 변경 -> 차이 계산 -> 소수점 이하 날리기 입니다.
비교대상의 데이터를 00시00분의 DATE값으로 변경하는 부분은 아래와 같습니다.
TO_DATE(TO_CHAR(TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS'), 'YYYYMMDD'), 'YYYYMMDDHH24MISS')
그 다음은 차이를 구하고 소수점 이하를 날려버리는 방법은 이전과 같습니다.
시험해보세요. 양쪽 모두 잘 되죠?
(3) 시간차이를 시간/분/초 단위로 환산하기
이는 어렵지 않으실 겁니다. 원래의 단위가 1일이 1인 단위인 NUMBER 타입이므로,
- 시간 단위를 계산하기 위해선 1시간을 1로 바꿔주면 됩니다. 즉 24를 곱하면 되죠.
- 분 단위로 계산하기 위해선 24 * 60 을 곱해주면 되고,
- 초 단위로 계산하기 위해선 24 * 60 * 60 을 곱해주면 되겠지요.
SELECT TRUNC ( ( TO_DATE ('20010502223443', 'YYYYMMDDHH24MISS')
- TO_DATE ('20010501213344', 'YYYYMMDDHH24MISS')
)
* 24
) AS hour_diff_cnt
FROM DUAL;
계산결과를 확인해보도록 하세요.
(4) 그럼 날짜/시간/분/초 단위로 환산하기는 어떻게 하느냐? 이건 숙제로 내주겠습니다.
이 정도 해설해드렸으면 조금 궁리해보시면 쉽게 해답을 내놓을 수 있을 겁니다.
다음검색