MySQL에서 VARCHAR로 저장된 날짜 BETWEEN 검색 방법 (STR_TO

MySQL에서 VARCHAR로 저장된 날짜 BETWEEN 검색하기

MySQL에서 날짜가 VARCHAR 타입으로 저장되어 있을 때는 STR_TO_DATE() 함수를 사용하면 BETWEEN 조건으로 원하는 기간의 데이터를 검색할 수 있습니다. 기본 문법은 다음과 같습니다.

select * from yourTableName
where STR_TO_DATE(LEFT(yourColumnName, LOCATE(' ', yourColumnName)), '%m/%d/%Y')
BETWEEN 'yourDateValue1' AND 'yourDateValue2';

이 문법이 실제로 어떻게 동작하는지 예제 테이블을 만들어 단계별로 확인해 보겠습니다.

1. 테이블 생성

먼저 배송일자(ShippingDate)를 VARCHAR로 저장하는 테이블을 생성합니다.

mysql> create table SearchDateAsVarchar
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> ShippingDate varchar(100),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.99 sec)

2. 샘플 데이터 삽입

INSERT 명령으로 날짜와 시간이 함께 포함된 문자열 데이터를 입력합니다.

mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/28/2011 9:58 AM');
Query OK, 1 row affected (0.19 sec)
mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/18/2011 10:50:39 AM');
Query OK, 1 row affected (0.55 sec)
mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/22/2011 11:45:40 AM');
Query OK, 1 row affected (0.18 sec)

3. 전체 데이터 조회

SELECT 문으로 저장된 모든 레코드를 확인합니다.

mysql> select *from SearchDateAsVarchar;

실행 결과는 다음과 같습니다.

+----+-----------------------+
| Id | ShippingDate          |
+----+-----------------------+
|  1 | 6/28/2011 9:58 AM     |
|  2 | 6/18/2011 10:50:39 AM |
|  3 | 6/22/2011 11:45:40 AM |
+----+-----------------------+
3 rows in set (0.00 sec)

4. BETWEEN으로 날짜 범위 검색

VARCHAR로 저장된 날짜에서 특정 기간 사이의 데이터를 검색하는 쿼리입니다.

mysql> select *from SearchDateAsVarchar where
STR_TO_DATE(LEFT(ShippingDate,LOCATE(' ',ShippingDate)),'%m/%d/%Y') BETWEEN
'2011-06-20' AND '2011-06-28';

실행 결과는 다음과 같습니다.

+----+-----------------------+
| Id | ShippingDate          |
+----+-----------------------+
|  1 | 6/28/2011 9:58 AM     |
|  3 | 6/22/2011 11:45:40 AM |
+----+-----------------------+
2 rows in set (0.00 sec)

결과를 보면 2011년 6월 20일부터 6월 28일 사이에 해당하는 두 개의 레코드만 반환되었습니다. 6월 18일 데이터는 지정한 범위에 포함되지 않아 자동으로 제외된 것을 확인할 수 있습니다.

쿼리 동작 원리

이 쿼리가 동작하는 과정을 단계별로 살펴보면 다음과 같습니다.

LOCATE(' ', ShippingDate): 문자열에서 첫 번째 공백의 위치를 찾습니다. 이 위치는 날짜 부분 바로 뒤, 즉 시간 정보가 시작되기 전 지점입니다.
LEFT(ShippingDate, LOCATE(...)): 공백 위치까지의 문자열, 즉 날짜 부분만 추출합니다. 예를 들어 '6/28/2011 '처럼 됩니다.
STR_TO_DATE(..., '%m/%d/%Y'): 추출한 문자열을 '%m/%d/%Y' 형식(월/일/연도)에 맞춰 DATE 타입으로 변환합니다.
BETWEEN: 변환된 날짜가 지정한 시작일과 종료일 사이에 속하는지 비교하여 조건에 맞는 행만 필터링합니다.

성능 관련 참고사항

VARCHAR로 저장된 날짜를 STR_TO_DATE()로 매번 변환하면 인덱스를 활용할 수 없기 때문에, 데이터 양이 많아지면 조회 성능이 크게 저하될 수 있습니다. 새로운 테이블을 설계한다면 날짜 컬럼은 가급적 DATE 또는 DATETIME 타입으로 정의하는 것이 좋습니다. 기존 데이터를 마이그레이션해야 하는 경우에는 ALTER TABLE과 STR_TO_DATE()를 조합해 컬럼 타입을 변경한 후 검색하는 방식을 권장합니다.