날짜 데이터란?
일상에서 발생하는 대다수의 데이터는 “시점”(시간의 흐름 가운데 어느 한 순간) 속성을 가지고 있다. “언제 병원에서 진료를 했다. 언제 전철을 탔다. 언제 음식을 주문했다. 언제 태어났다”와 같은 “언제”가 바로 “시점”이다. 대다수의 DBMS는 “시점”을 효율적으로 관리할 수 있도록 날짜 자료형을 제공한다. 데이터를 활용하거나 분석하는 데 있어 “시점”은 매우 중요한 정보다. 그만큼 날짜 자료형의 값을 잘 다룰 수 있어야 한다.
본격적인 설명에 앞서 ‘일시’와 ‘일자’, 두 개 용어를 아래와 같이 정리하니 참고바란다.
•
일시: 날짜(년,월,일)와 시간(시,분,초)을 포함한 표현으로 사용
•
일자: 시간(시,분,초)을 제외한 날짜(년,월,일)만 뜻하는 표현
NOW와 SYSDATE
MySQL에는 현재 일시를 출력해주는 함수로 NOW와 SYSDATE가 있다.
•
NOW: SQL이 실행된 시점의 일시가 출력
◦
1,000 건의 데이터를 출력하면서 NOW를 사용하면 1,000건의 일시 값은 모두 같다.
•
SYSDATE: 결과 레코드별로 추출되는 시점의 일시가 출력
◦
1,000 건의 데이터를 출력하면서 SYSDATE를 사용하면 레코드별로 일시 값이 모두 다르다.
아래의 NOW와 SYSDATE 예제를 실행해보자. SELECT 절에 사용한 SLEEP 함수는 데이터 출력 중간에 SLEEP을 수행한다. 이를 통해 MySQL 내부적으로 데이터를 가져오면서 1초씩의 텀이 발생된다.
-- [SQL-3-11-1]
-- SLEEP 함께 NOW() 사용
SELECT T1.ItemId ,T1.ItemNm, SLEEP(1) SLP ,NOW() NowDtm
FROM startdb.Item T1
WHERE T1.ItemCat = 'BKR'
ORDER BY T1.ItemId;
ItemId ItemNm SLP NowDtm
------ ------------------- --- -------------------
BGLR Bagel(R) 0 2024-05-24 08:33:39
BMFR Blueberry Muffin(R) 0 2024-05-24 08:33:39
CMFR Chocolate Muffin(R) 0 2024-05-24 08:33:39
MACA Macaron(R) 0 2024-05-24 08:33:39
-- [SQL-3-11-2]
-- SLEEP과 함께 SYSDATE() 사용
SELECT T1.ItemId ,T1.ItemNm, SLEEP(1) SLP ,SYSDATE() SysDtm
FROM startdb.Item T1
WHERE T1.ItemCat = 'BKR'
ORDER BY T1.ItemId;
ItemId ItemNm SLP SysDtm
------ ------------------- --- -------------------
BGLR Bagel(R) 0 2024-05-24 08:33:57
BMFR Blueberry Muffin(R) 0 2024-05-24 08:33:58
CMFR Chocolate Muffin(R) 0 2024-05-24 08:33:59
MACA Macaron(R) 0 2024-05-24 08:34:00
SQL
복사
NOW를 사용한 경우, SLEEP과 상관없이 SQL 실행 시점의 일시 값으로 모든 레코드가 출력된다. 반면에 SYSDATE는 레코드마다 출력된 일시가 모두 다르다. 업무에 맞게 NOW와 SYSDATE를 선택해서 사용하면 되는데, 보통은 NOW를 사용한다.
Tip. 오라클의 SYSDATE와 MySQL의 SYSDATE
오라클은 일시를 출력하기 위해 SYSDATE를 사용한다. 오라클의 SYSDATE는 MySQL의 SYSDATE가 아닌 NOW와 같다. 그러므로 오라클의 SQL을 MySQL로 변환할 때는 SYSDATE는 NOW로 변경해야 한다.
날짜 데이터타입
MySQL은 날짜(일시 또는 일자)를 관리하기 위해 다음과 같은 데이터타입(자료형)을 제공한다.
•
DATE: 일자만 관리(시간은 저장되지 않는다.)
•
DATETIME: 일시를 관리(일자와 시간 모두를 관리)
•
TIMESTAMP: 일시를 관리하며, TimeZone(시간대 지역)까지 저장할 수 있는 자료형
Tip 오라클의 DATE와 MySQL의 DATE
오라클과 MySQL의 DATE 자료형은 차이가 있다. 오라클의 DATE 자료형은 일시(일자와 시간)를 관리하는 자료형이다. MySQL로 따지면 DATETIME과 같다. 혹시라도 오라클을 MySQL로 변경하는 프로젝트라면 이 부분을 주의해서 자료형을 설정해야 한다.
위에서 설명한 DATE, DATETIME, TIMESTAMP와 같은 공식적인 날짜 데이터타입 외에 VARCHAR나 CHAR와 같은 문자 데이터타입으로 날짜를 관리하기도 한다. 2020년 3월 20일이라는 값을 '20200320'이나 ‘2020-03-20’과 같이 문자열로 입력해 사용하는 방법이다. 생각보다 많은 시스템이 이와 같은 방법을 사용한다. 이 방법은 특정 월의 데이터를 조회할 때 조건 처리가 편한 장점이 있다. 2020년 3월 20일의 데이터를 모두 조회하고 싶다면 아래와 같이 LIKE를 활용할 수 있다.
•
컬럼명 LIKE '202003%'
또한, 시간은 제외하고 날짜만 관리하고 싶을 때 유용하다. 특히 오라클에는 날짜만 관리할 수 있는 공식 데이터타입이 없기 때문에 문자 형태 날짜는 좋은 대안이 되기도 한다. 하지만 이와 같은 문자열로 날짜를 관리할 때 가장 큰 문제점은 ‘20200230’(2020년 2월 30일)과 같이 존재하지 않는 날짜 값이 들어갈 수 있다는 점이다. 그뿐만 아니라, 날짜를 문자열로 관리하면 날짜에 대한 계산 처리 과정에서 데이터 변환이 자주 발생하게 된다. 이러한 부분을 인지하고 날짜 관리 컬럼에 DATE 자료형을 사용 할지 문자 자료형을 사용 할지 고민해야 한다.
날짜 컬럼 보유한 테이블 살펴보기
날짜 데이터를 다루기 위해 날짜 컬럼을 보유한 테이블들을 살펴보자. DESC 명령어를 사용해 컬럼별로 자료형도 확인해보기 바란다.
STR_TO_DATE(String To Date)
날짜 데이터를 잘 다루려면 날짜 관련 함수를 자유자재로 사용할 수 있어야 한다. 가장 많이 사용하는 날짜 관련 함수는 문자열을 날짜 형태로 변환하는 STR_TO_DATE(String To Date) 함수다.
•
STR_TO_DATE: 문자열을 날짜 형태로 변환하는 함수
◦
STR_TO_DATE(’문자열’,’패턴 지정’)과 같은 형태로 사용
◦
사용할 수 있는 패턴은 다음과 같다.
▪
%Y: 네 자리 년도(대문자 Y)
▪
%m: 두 자리 월(소문자 m)
▪
%d: 두자리 일(소문자 d)
▪
%H: 두 자리 시간(24시간. 대문자 H)
▪
%i: 두 자리 분(소문자 i)
▪
%s: 두 자리 초(소문자 s)
STR_TO_DATE는 문자열을 날짜 형태로 변환한다. 이때, 문자열의 각 부분이 날짜의 어떤 부분인지를 패턴으로 지정해주어야 합니다. 아래 SQL은 STR_TO_DATE를 사용해 ‘20240501’을 날짜 형태로 변환하는 예다. ‘20240501’라는 문자열의 각 부분이 날짜의 어느 부분에 해당하는지 ‘%Y%m%d’ 패턴으로 지정했다. 패턴에 의해 ‘20240501’은 ‘2024년 5월 1일’이라는 날짜 값으로 변환된다.
-- [SQL-3-11-3]
SELECT STR_TO_DATE('20240501','%Y%m%d') DT FROM DUAL;
DT
----------
2024-05-01
SQL
복사
아래는 문자열 ‘05/01/2024’를 날짜 형태로 변환하고 있다. 패턴 문자로 ‘%m/%d/%Y’를 사용했으므로 가장 앞의 두 자리 문자가 월다. 다시 말해 아래 결과는 2024년 5월 1일이다.
-- [SQL-3-11-4]
SELECT STR_TO_DATE('05/01/2024','%m/%d/%Y') DT FROM DUAL;
DT
----------
2024-05-01
SQL
복사
이번에는 문자열 ‘05/01/2024’의 패턴을 ‘%d/%m/%Y’로 지정해보자. 패턴 가장 앞에 ‘%d’가 있으므로 ‘05/01/2024’의 가장 앞의 ‘05’는 일자 값이 된다. 결과적으로 아래 결과는 2024년 1월 5일이다.
-- [SQL-3-11-5]
SELECT STR_TO_DATE('05/01/2024','%d/%m/%Y') DT FROM DUAL;
DT
----------
2024-01-05
SQL
복사
상품(Item) 테이블의 출시일자(LaunchDt) 컬럼은 DATE 자료형이다. 그러므로 DATE 자료형인 출시일자 컬럼에 조건을 줄 때는 아래와 같이 STR_TO_DATE를 사용해 문자를 날짜로 변환해 처리하는 것이 정석이다.
-- [SQL-3-11-6]
SELECT T1.ItemId ,T1.ItemNm ,T1.LaunchDt
FROM startdb.Item T1
WHERE T1.LaunchDt = STR_TO_DATE('20230401','%Y%m%d');
ItemId ItemNm LaunchDt
------ ------------------ ----------
CITR Yuzu Ade(R) 2023-04-01
EINR Einspanner(R) 2023-04-01
FLTR Flat White(R) 2023-04-01
IEINR Iced Einspanner(R) 2023-04-01
IFLTR Iced Flat White(R) 2023-04-01
MACA Macaron(R) 2023-04-01
ZAMB Grapefruit Ade(R) 2023-04-01
SQL
복사
DATE_FORMAT
DATE_FORMAT은 날짜 값을 문자열로 변환하는 함수다. 앞에서 살펴본 STR_TO_DATE와는 반대의 역할을 한다.
•
DATE_FORMAT: 날짜 값을 문자열로 변환하는 함수.
◦
날짜를 FORMAT에 맞게 문자열로 변형
◦
DATE_FORMAT(날짜값,’패턴 지정’)과 같은 형태로 사용
▪
STR_TO_DATE는 매개 변수로 문자열을 받고, 문자열의 패턴을 알려주어야 한다.
▪
DATE_FORMAT은 매개 변수로 날짜 값을 받고, 날짜를 어떤 문자열 패턴으로 반환할지 지정한다.
◦
STR_TO_DATE와 동일한 패턴 문자를 사용한다.
▪
Y%, %m, %d, %H, %i, %s
아래는 상품(Item) 테이블의 출시일자(LaunchDt) 컬럼을 DATE_FORMAT을 사용해 문자열로 출력하는 SQL이다.
-- [SQL-3-11-7]
SELECT T1.ItemId ,T1.ItemNm ,T1.LaunchDt
,DATE_FORMAT(T1.LaunchDt,'%Y-%m-%d') Launch_YMD
,DATE_FORMAT(T1.LaunchDt,'%m/%d/%Y') Launch_MDY
FROM startdb.Item T1
WHERE T1.ItemId IN ('AMR','FLTR');
ItemId ItemNm LaunchDt Launch_YMD Launch_MDY
------ ------------- ---------- ---------- ----------
AMR Americano(R) 2019-01-01 2019-01-01 01/01/2019
FLTR Flat White(R) 2023-04-01 2023-04-01 04/01/2023
SQL
복사
STR_TO_DATE와 DATE_FORMAT 둘 다 패턴을 지정하므로 사용법이 약간 혼란스러울 수 있다. 아래와 같이 정리해보자.
•
STR_TO_DATE의 패턴 지정: 문자열의 각 부분이 날짜의 어떤 부분인지를 지정
•
DATE_FORMAT의 패턴 지정: 날짜 값을 어떤 형태(패턴)의 문자열로 표현할지를 지정
Tip. 오라클의 날짜 변환 함수
오라클은 문자열을 날짜로 변환하기 위해 TO_DATE 함수를, 날짜를 문자열로 변환하기 위해 TO_CHAR를 제공한다. TO_DATE는 MySQL의 STR_TO_DATE와 같고 TO_CHAR는 MySQL의 DATE_FORMAT과 같다. TO_DATE와 TO_CHAR도 패턴 문자로 문자열의 패턴을 지정한다. 오라클에서는 네 자리 년도를 ‘YYYY’로 두 자리 월을 ‘MM’으로, 두 자리 일자를 ‘DD’로 패턴을 지정한다. TO_CHAR(’20240201’,’YYYYMMDD’)와 같은 방식으로 사용한다.
아래는 DATE_FORMAT을 다양하게 사용한 예제다. 특히 날짜를 년월 형태로 변환하는 방법은 잘 기억해두기 바란다.
-- [SQL-3-11-8]
SELECT T1.ShopId ,T1.OrdNo ,T1.OrdDtm
,DATE_FORMAT(T1.OrdDtm,'%Y%m') YYYYMM -- > 날짜를 년월로 변환
,DATE_FORMAT(T1.OrdDtm,'%Y-%m') `YYYY-MM` -- > 날짜를 년-월로 변환
,DATE_FORMAT(T1.OrdDtm,'%Y%m%d') YMD
,DATE_FORMAT(T1.OrdDtm,'%Y-%m-%d') `Y-M-D`
,DATE_FORMAT(T1.OrdDtm,'%H시%i분%s초') 시분초
,DATE_FORMAT(T1.OrdDtm,'%W') 요일1
,DATE_FORMAT(T1.OrdDtm,'%a') 요일2
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220601','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220610','%Y%m%d')
AND T1.ShopID = 'S016'
ORDER BY T1.OrdNo;
ShopId OrdNo OrdDtm YYYYMM YYYY-MM YMD Y-M-D 시분초 요일1 요일2
------ ----- ------------------- ------ ------- -------- ---------- ------------ --------- -----
S016 30098 2022-06-01 08:00:00 202206 2022-06 20220601 2022-06-01 08시00분00초 Wednesday Wed
S016 30103 2022-06-02 08:00:00 202206 2022-06 20220602 2022-06-02 08시00분00초 Thursday Thu
S016 30108 2022-06-03 08:00:00 202206 2022-06 20220603 2022-06-03 08시00분00초 Friday Fri
S016 30113 2022-06-04 08:00:00 202206 2022-06 20220604 2022-06-04 08시00분00초 Saturday Sat
S016 30242 2022-06-05 08:00:00 202206 2022-06 20220605 2022-06-05 08시00분00초 Sunday Sun
S016 30247 2022-06-06 08:00:00 202206 2022-06 20220606 2022-06-06 08시00분00초 Monday Mon
S016 30379 2022-06-07 08:00:00 202206 2022-06 20220607 2022-06-07 08시00분00초 Tuesday Tue
S016 30384 2022-06-08 08:00:00 202206 2022-06 20220608 2022-06-08 08시00분00초 Wednesday Wed
S016 30522 2022-06-09 08:00:00 202206 2022-06 20220609 2022-06-09 08시00분00초 Thursday Thu
SQL
복사
날짜 조건 사용하기
MySQL의 DATETIME 자료형은 일자와 함께 시분초가 입력된다. 이때 일시를 00시 00분 00초로 저장할 수도 있다. 회원(Member) 테이블의 가입일시(JoinDtm) 컬럼이 이와 같이 구성되어 있다. DATETIME 자료형이지만 시분초 값은 00시 00분 00초로 모두 처리되어 있다. 이와 같은 데이터는 아래와 같이 시분초 값을 지정하지 않고 해당 일자의 조건만 사용하면 데이터를 조회할 수 있다.
-- [SQL-3-11-9]
SELECT T1.MemberId ,T1.NickNm ,T1.MemberGd ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.MemberGd = 'PLAT'
AND T1.JoinDtm = STR_TO_DATE('20190328','%Y%m%d');
MemberId NickNm MemberGd JoinDtm
-------- ------ -------- -------------------
M0001 Air PLAT 2019-03-28 00:00:00
M0043 Storm PLAT 2019-03-28 00:00:00
M0202 Apple4 PLAT 2019-03-28 00:00:00
M0550 Wind10 PLAT 2019-03-28 00:00:00
SQL
복사
반면에 주문(Ord) 테이블의 주문일시(OrdDtm) 컬럼은 주문이 발생한 시분초가 저장되고 있다. 그러므로 00시 00분 00초인 데이터가 없다. 이 경우, 아래와 같이 조건 값에 시분초를 지정하지 않으면 제대로 데이터가 조회되지 않는다. 아래와 같이 조건을 주면 주문일시가 2021년 12월 24일 00시 00분 00초 데이터만 조회가 가능하다.
-- [SQL-3-11-10]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm = STR_TO_DATE('20211224','%Y%m%d')
ORDER BY T1.OrdNo;
Result
-------
No-Data
SQL
복사
주문일시(OrdDtm) 조건으로 특정 데이터를 정확히 조회하려면 시분초까지 조건으로 사용해야 한다.
-- [SQL-3-11-11]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm = STR_TO_DATE('20211224 07:30:00','%Y%m%d %H:%i:%s')
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId
----- ------------------- --------
22974 2021-12-24 07:30:00 M1020
22975 2021-12-24 07:30:00 M1050
22976 2021-12-24 07:30:00 M2061
SQL
복사
만약에 2021년 1월 1일의 하루치 주문을 조회하고 싶다면 아래와 같이 범위 조건을 사용해야 한다. 조건을 줄 때 주의할 점은 21년 1월 1일보다는 이상(>=)으로 조건을 처리하고 21년 1월 2일보다는 미만(<)으로 처리해야 한다.
-- [SQL-3-11-12]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20210102','%Y%m%d')
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId
----- ------------------- --------
10835 2021-01-01 08:00:00 M0001
10836 2021-01-01 08:00:00 M1001
10837 2021-01-01 08:00:00 M2001
SQL
복사
2022년 12월 한 달 간의 모든 주문을 조회하고 싶다면 아래와 같이 조건을 처리할 수 있다.
-- [SQL-3-11-13]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d') -- > 2022년 12월 1일 이상
AND T1.OrdDtm < STR_TO_DATE('20230101','%Y%m%d') -- > 2023년 1월 1일 미만
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId
----- ------------------- --------
49630 2022-12-01 08:00:00 M0001
49631 2022-12-01 08:00:00 M1001
49632 2022-12-01 08:00:00 M2001
49633 2022-12-01 08:00:00 M3001
... 생략 ...
SQL
복사
날짜 계산 함수: DATE_ADD
특정 날짜에 일수를 더하거나 빼기 위해 사용하는 함수다.
•
DATE_ADD(날짜값, interval n [interval 키워드])
◦
n에는 더하거나 빼려는 숫자 값을 입력
◦
[interval 키워드]에는 아래의 키워드 중 하나를 사용
▪
MICROSECOND
▪
SECOND
▪
MINUTE
▪
HOUR
▪
DAY
▪
WEEK
▪
MONTH
▪
QUARTER
▪
YEAR
2022년 6월 28일부터 7일 이후의 주문까지 조회하기 위해 아래와 같이 DATE_ADD를 사용할 수 있다.
-- [SQL-3-11-14]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220628','%Y%m%d')
AND T1.OrdDtm < DATE_ADD(STR_TO_DATE('20220628','%Y%m%d'), interval 7 day)
AND T1.MemberId = 'M0001'
ORDER BY T1.OrdNo;
OrdNo OrdDtm MemberId
----- ------------------- --------
30911 2022-06-28 08:00:00 M0001
30916 2022-06-29 08:00:00 M0001
30921 2022-06-30 08:00:00 M0001
30926 2022-07-01 08:00:00 M0001
30931 2022-07-02 08:00:00 M0001
31206 2022-07-03 08:00:00 M0001
31211 2022-07-04 08:00:00 M0001
SQL
복사
아래는 주문(Ord)을 조회하면서 주문일시(OrdDtm)에 무조건 5분을 더해서 제조완료예상일시로 출력하는 SQL이다.
-- [SQL 3-11-15]
SELECT T1.OrdNo ,T1.OrdDtm
,DATE_ADD(T1.OrdDtm, interval 5 minute) 제조완료예상일시
FROM startdb.Ord T1
WHERE T1.OrdNO = 91947;
OrdNo OrdDtm 제조완료예상일시
----- ------------------- -------------------
91947 2023-03-31 20:30:00 2023-03-31 20:35:00
SQL
복사
날짜 계산 함수: DATEDIFF와 TIMESTAMPDIFF
DATEDIFF와 TIMESTAMPDIFF 모두 날짜 간의 차이를 계산하는 함수다. 단, DATEDIFF는 두 날짜간의 일수 차이만 계산이 가능하다. 시간 단위 차이 계산은 할 수 없다. 시간 단위 차이는 TIMESTAMPDIFF를 사용해야 한다.
•
DATEDIFF: 두 날짜에 대한 일 수 차이 계산(시간 계산 불가)
•
TIMESTAMPDIFF: 두 날짜에 대한 차이 계산(시간 차이 계산 가능)
◦
TIMESTAMPDIFF는 아래의 키워드를 사용해 계산할 단위를 지정합니다.
◦
MICROSECOND
◦
SECOND
◦
MINUTE
◦
HOUR
◦
DAY
◦
WEEK
◦
MONTH
◦
QUARTER
◦
YEAR
아래는 DATEDIFF를 사용해 회원(Member)의 회원유지일수(탈퇴일시-가입일시)를 계산해 출력하는 SQL이다.
-- [SQL-3-11-16]
SELECT T1.MemberId ,T1.JoinDtm 가입일시 ,T1.LeaveDtm 탈퇴일시
,DATEDIFF(T1.LeaveDtm,T1.JoinDtm) 회원유지일수
FROM startdb.Member T1
WHERE T1.MemberSt = 'INAC'
AND T1.MemberId = 'M1406';
MemberId 가입일시 탈퇴일시 회원유지일수
-------- ------------------- ------------------- ------------
M1406 2020-01-01 00:00:00 2021-02-20 00:00:00 416
SQL
복사
아래는 TIMESTAMPDIFF를 사용해 주문일시부터 제조완료까지 몇 초가 걸렸는지를 계산해 출력하는 SQL이다.
-- [SQL-3-11-17]
SELECT T1.OrdNo ,T1.ShopID ,T1.OrdDtm 주문일시,T1.PrepareCmpDtm 제조완료일시
,TIMESTAMPDIFF(SECOND, T1.OrdDtm, T1.PrepareCmpDtm) AS 제조까지걸린초
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220623','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220624','%Y%m%d')
ORDER BY 제조까지걸린초 DESC;
OrdNo ShopID 주문일시 제조완료일시 제조까지걸린초
----- ------ ------------------- ------------------- --------------
30887 S007 2022-06-23 08:00:00 2022-06-23 08:10:00 600
30889 S016 2022-06-23 08:00:00 2022-06-23 08:09:00 540
30886 S003 2022-06-23 08:00:00 2022-06-23 08:06:00 360
30888 S011 2022-06-23 08:00:00 2022-06-23 08:04:00 240
30890 S020 2022-06-23 08:00:00 2022-06-23 08:03:00 180
SQL
복사
DATEDIFF와 TIMESTAMPDIFF는 비슷한 기능을 하지만 용도와 사용법에 차이가 있습니다. DATEDIFF는 일 수 차이만 계산할 수 있으며, TIMESTAMPDIFF는 시분초 단위도 계산이 가능하다. 두 함수는 입력되는 날짜 값 매개변수의 위치도 서로 다르다.
•
DATEDIFF(값1, 값2) = 값1에서 값2를 뺀 차이.
◦
DATEDIFF(뒷날짜, 앞날짜) -> 뒷날짜에서 앞날짜를 뺀 일수
•
TIMESTAMPDFF(단위, 값1, 값2) = 값1에서 값2까지 걸린 단위
◦
TIMESTAMPDIFF(HOUR, 앞날짜, 뒷날짜) -> 앞날짜에서 뒷날짜까지 걸린 HOUR
날짜 자료형을 확인하고 SQL을 작성하자
앞에서 설명한 것처럼 날짜 데이터를 관리하기 위한 자료형에는 DATE, DATETIME, TIMESTAMP 그리고 문자열도 있다. 우리가 실습 중인 startdb는 날짜 값 컬럼에 이와 같은 자료형을 다양하게 사용하고 있다. 다양한 테스트를 할 수 있게 하기 위함이며, 실제 하나의 시스템에서 이처럼 다양한 방법으로 날짜를 관리하는 경우는 드물다.
먼저 DESC를 통해 상품(Item) 테이블, 주문(Ord) 테이블, 매장(Shop) 테이블을 살펴보자.
-- [SQL-3-11-18]
DESC startdb.Item;
-- 결과
Field Type Null Key Default Extra
---------- ------------ ---- --- ------- -----
ItemId varchar(40) NO PRI NULL
ItemNm varchar(100) YES NULL
ItemCat varchar(40) YES MUL NULL
ItemSizeCd varchar(40) YES NULL
HotColdCd varchar(40) YES NULL
LaunchDt date YES NULL
-- [SQL-3-11-19]
DESC startdb.Ord;
-- 결과
Field Type Null Key Default Extra
------------- --------------- ---- --- ------- -----
OrdNo bigint unsigned NO PRI NULL
OrdDtm datetime YES NULL
PrepareCmpDtm datetime YES NULL
PickupDtm datetime YES NULL
MemberId varchar(40) YES MUL NULL
ShopId varchar(40) YES MUL NULL
OrdSt varchar(40) YES NULL
OrdAmt decimal(18,3) YES NULL
PayTp varchar(40) YES NULL
-- [SQL-3-11-20]
DESC startdb.Shop;
-- 결과
Field Type Null Key Default Extra
------------ ------------ ---- --- ------- -----
ShopId varchar(40) NO PRI NULL
ShopNm varchar(100) YES NULL
ShopSize int YES NULL
ShopOperTp varchar(40) YES NULL
TableQty int YES NULL
ChairQty int YES NULL
OpenTime varchar(100) YES NULL
CloseTime varchar(100) YES NULL
ShopSt varchar(40) YES NULL
ShopStartYmd varchar(8) YES NULL
ShopEndYmd varchar(8) YES NULL
SQL
복사
날짜 컬럼에 설정한 자료형을 정리하면 다음과 같습니다.
•
Item(상품)의 LaunchDt(출시일자): DATE
•
Ord(주문)의 OrdDtm(주문일시), PrepareCmpDtm(준비완료일시), PickupDtm(픽업일시): DATETIME
•
Shop(매장)의 ShopStartYmd(매장시작일자), ShopEndYmd(매장종료일자): VARCHAR(8)
Item의 LaunchDt는 DATE 자료형으로 일자만 저장된 날짜 자료형이다. 그러므로 특정일의 데이터를 출력하기 위해서는 다음과 같이 SQL을 사용하면 된다.
-- [SQL-3-11-21]
SELECT T1.ItemId ,T1.ItemNm ,T1.LaunchDt ,T1.ItemCat
FROM startdb.Item T1
WHERE T1.LaunchDt = STR_TO_DATE('20190101','%Y%m%d')
AND T1.ItemCat = 'BKR';
ItemId ItemNm LaunchDt ItemCat
------ ------------------- ---------- -------
BGLR Bagel(R) 2019-01-01 BKR
BMFR Blueberry Muffin(R) 2019-01-01 BKR
CMFR Chocolate Muffin(R) 2019-01-01 BKR
SQL
복사
Ord의 OrdDtm은 DATETIME 자료형이므로 특정일 하루에 해당하는 데이터를 조회하려면 다음과 같이 OrdDtm에 범위로 조건을 처리해야 한다.
-- [SQL-3-11-22]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20200102','%Y%m%d');
OrdNo OrdDtm MemberId
----- ------------------- --------
1212 2020-01-01 08:00:00 M0001
1213 2020-01-01 08:00:00 M1001
SQL
복사
Shop(매장)의 ShopStartYmd(매장시작일자)는 VARCHAR 자료형이다. 그러므로 다음과 같이 문자 형태로 조건을 주면 하루치의 데이터를 조회할 수 있다. 컬럼의 자료형이 문자 형태인 VARCHAR이므로 STR_TO_DATE 를 사용해 조건 값을 줄 필요가 없다.
-- [SQL-3-11-23]
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopStartYmd
FROM startdb.Shop T1
WHERE T1.ShopStartYmd = '20180403';
ShopId ShopNm ShopStartYmd
------ ------------- ------------
S001 New York-1st 20180403
S015 Charlotte-1st 20180403
S034 Columbus-2nd 20180403
S035 Charlotte-2nd 20180403
S043 Chicago-3rd 20180403
SQL
복사
VARCHAR와 같은 문자 자료형으로 날짜를 관리하면 다음과 같이 특정월 데이터를 조회하기 위해 LIKE를 사용할 수도 있다.
-- [SQL-3-11-24]
SELECT T1.ShopId ,T1.ShopNm ,T1.ShopStartYmd
FROM startdb.Shop T1
WHERE T1.ShopStartYmd LIKE '201804%'
ORDER BY T1.ShopStartYmd ASC;
ShopId ShopNm ShopStartYmd
------ ----------------- ------------
S030 San Jose-2nd 20180401
S003 Chicago-1st 20180402
S017 Indianapolis-1st 20180402
S032 Jacksonville-2nd 20180402
... 생략 ...
SQL
복사
자신이 다룰 날짜 데이터가 어떤 자료형으로 되어 있는지 반드시 확인하고 그에 맞게 적절하게 조건을 사용하기 바란다.
자동형변환
데이터와 데이터를 비교할 때는 같은 데이터 타입(자료형)에 대해서만 비교가 가능하다. 숫자는 숫자끼리 문자는 문자끼리, 날짜는 날짜끼리만 비교할 수 있다. 만약에 서로 다른 데이터 타입 사이에 비교가 발생하면 어느 한쪽의 데이터 타입을 변형해 같은 데이터 타입으로 맞춘 후에 비교를 진행한다. 예를 들어 숫자와 문자 사이에 비교가 발생하면 문자를 숫자로 변형한 후 비교를 수행한다.
아래는 Ord(주문) 테이블의 OrdNo(주문번호, 숫자 자료형) 컬럼에 문자 조건을 사용한 SQL이다. 이 경우 문자 조건 값을 숫자 형태로 변환 후 비교가 이루어진다.
-- [SQL-3-11-25]
SELECT T1.OrdNo, T1.MemberId ,T1.OrdDtm
FROM startdb.Ord T1
WHERE T1.OrdNo = '30';
SQL
복사
이처럼 상황에 따라 DBMS(MySQL)가 자동으로 값의 형태를 변환하는 것을 자동형변환이라고 한다.
아래 SQL은 DATETIME 자료형인 JoinDtm(가입일시) 컬럼에 문자 형태로 조건을 처리하고 있다.
-- [SQL-3-11-26]
SELECT T1.MemberId ,T1.NickNm ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.JoinDtm = '20190320'
SQL
복사
DATETIME 자료형인 JoinDtm 컬럼에 문자열로 조건을 주었지만 SQL은 정상적으로 실행된다. 날짜와 문자 간에 자동형변환이 발생하게 때문이다. 일반적으로 날짜와 문자 간에 비교가 발생하면 문자를 날짜로 변환한다. 그러므로 위 SQL은 아래와 같이 STR_TO_DATE를 적용한 것과 같다.
-- [SQL-3-11-27]
SELECT T1.MemberId ,T1.NickNm ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.JoinDtm = STR_TO_DATE('20190320','%Y%m%d')
SQL
복사
이처럼 자동형변환이라는 기능이 있어 날짜 컬럼에 조건을 줄 때 STR_TO_DATE를 생략해도 결과에는 이상이 없다. 하지만 이러한 자동형변환은 상황(DBMS 종류, 버젼, DBMS 설정)에 따라서 작동이 안될 수도 있다. 또는 원하지 않는 방향으로 자동형변환이 될 수도 있다. 가능하면 날짜 컬럼에 조건을 줄 때는 STR_TO_DATE와 같은 함수를 사용해 문자를 날짜로 변환해서 처리하는 습관을 갖는 것이 좋다. (오라클이라면 TO_DATE를 사용하면 된다.)
날짜를 다루는 최악의 방법
WHERE 절에서 가장 조심해야 할 패턴이 있다. 바로 테이블의 컬럼을 변환하는 것이다. 아래는 2022년 6월 주문 데이터를 조회하기 위해 OrdDtm 컬럼을 DATE_FORMAT으로 변환 처리했다. 이와 같은 컬럼의 변형은 SQL의 성능에 좋지 않을 가능성이 높다. SQL 성능은 별도 공부해야 할 내용이므로 여기서 다루지는 않겠다. 다만 아래와 같은 배드 패턴은 잘 기억하고 사용하지 않기 바란다.
-- [SQL-3-11-28]
-- 특정월 데이터 조회: 잘못된 패턴
-- 테이블의 컬럼을 WHERE 절에서 변형하지 않습니다.
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE DATE_FORMAT(T1.OrdDtm,'%Y%m') = '202206'
ORDER BY T1.OrdNo;
-- [SQL-3-11-29]
-- 특정 일 데이터 조회: 잘못된 패턴
-- 테이블의 컬럼을 WHERE 절에서 변형하지 않습니다.
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220623'
ORDER BY T1.OrdNo;
SQL
복사
위 두 개의 SQL은 다음과 같이 변경해야 한다. 테이블의 컬럼 변형은 피하면서 같은 결과가 나오도록 조건을 처리해야 한다.
-- [SQL-3-11-30]
-- 특정월 데이터 조회: 올바른 패턴
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220601','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220701','%Y%m%d')
ORDER BY T1.OrdNo;
-- [SQL-3-11-31]
-- 특정일 데이터 조회: 올바른 패턴
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220623','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220624','%Y%m%d')
ORDER BY T1.OrdNo;
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 3-11-1
S003 매장에서 M0001 회원이 2022년에 주문한 목록을 보여주세요. 주문일시에 대한 년월도 추가해서 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: ShopId가 S003 이고, MemberId가 M0001이고, 주문일시(OrdDtm)가 2022년에 속하는 모든 주문
•
조회 컬럼: OrdNo, ShopId, MemberId, OrdDtm, OrdYM
•
추가 조건
◦
OrdYM은 문자열 년월 값으로 OrdDtm를 DATE_FORMAT으로 변환해서 처리하시오.
•
정렬 기준: OrdDtm으로 오름차순 정렬하시오.
OrdNo ShopId MemberId OrdDtm OrdYM
----- ------ -------- ------------------- -------
25278 S003 M0001 2022-01-01 08:00:00 2022-01
25281 S003 M0001 2022-01-02 08:00:00 2022-01
25373 S003 M0001 2022-01-03 08:00:00 2022-01
25376 S003 M0001 2022-01-04 08:00:00 2022-01
… 생략 …
64773 S003 M0001 2022-12-30 08:00:00 2022-12
67268 S003 M0001 2022-12-31 08:00:00 2022-12
SQL
복사
BOOSTER QUIZ 3-11-2
2022년 6월 1일 주문에 대해 주문일시와 픽업일시, 그리고 주문에서 픽업까지 걸린 분수를 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2022년 6월 1일인 모든 데이터
•
조회 컬럼: OrdNo, OrdDtm, PickupDtm, 픽업까지걸린시간(분)
•
추가 조건
◦
픽업까지걸린시간(분)은 OrdDtm에서 PickupDtm까지 걸린 분(MINUTE) 수 입니다.
▪
TIMESTAMPDIFF를 사용해 처리하시오.
◦
픽업까지걸린시간(분)과 같이 컬럼별칭에 괄호가 포함되면 컬럼별칭에 백틱(`)을 사용할 것에 주의
•
정렬 기준: 픽업까지걸린시간(분)으로 오름차순 정렬하시오.
OrdNo OrdDtm PickupDtm 픽업까지걸린시간(분)
----- ------------------- ------------------- --------------------
30099 2022-06-01 08:00:00 2022-06-01 08:04:00 4
30097 2022-06-01 08:00:00 2022-06-01 08:05:00 5
30095 2022-06-01 08:00:00 2022-06-01 08:07:00 7
30098 2022-06-01 08:00:00 2022-06-01 08:10:00 10
30096 2022-06-01 08:00:00 2022-06-01 08:11:00 11
SQL
복사
BOOSTER QUIZ 3-11-3
2021년 8월에 탈퇴한 회원들의 가입일시, 탈퇴일시와 회원을 유지한 일수도 보여주세요. 추가로 가입월과 탈퇴월도 보여주세요.
•
대상 테이블: 회원(Member)
•
조회 조건: LeaveDtm(탈퇴일시)가 2021년 8월인 회원 데이터
•
조회 컬럼: MemberId ,JoinDtm, LeaveDtm, 가입월, 탈퇴월, 회원유지일수
•
추가 조건
◦
가입월은 JoinDtm을 DATE_FORMAT을 사용해 YYYY-MM 형태의 월로 변경한 내용입니다.
◦
탈퇴월은 LeaveDtm을 DATE_FORMAT을 사용해 YYYY-MM 형태의 월로 변경한 내용입니다.
◦
회원유지일수는 JoinDtm 이후 LeaveDtm까지 몇일인지 계산한 일수입니다.
▪
TIMESTAMPDIFF를 사용해 처리
•
정렬 기준: 회원유지일수 기준으로 오름차순 정렬하시오.
MemberId JoinDtm LeaveDtm 가입월 탈퇴월 회원유지일수
-------- ------------------- ------------------- ------- ------- ------------
M2436 2020-05-16 00:00:00 2021-08-05 00:00:00 2020-05 2021-08 446
M2446 2020-05-14 00:00:00 2021-08-13 00:00:00 2020-05 2021-08 456
M2456 2020-05-03 00:00:00 2021-08-12 00:00:00 2020-05 2021-08 466
M2466 2020-05-01 00:00:00 2021-08-20 00:00:00 2020-05 2021-08 476
SQL
복사
BOOSTER QUIZ 3-11-4
아래 SQL에서 성능적으로 문제가 있을만한 부분을 말하고 문제가 제거되도록 SQL을 수정하시오.
SELECT T1.OrdNo ,T1.OrdDtm
FROM startdb.Ord T1
WHERE DATE_FORMAT(T1.OrdDtm,'%Y-%m') = '2022-01';
SQL
복사
