SQL 문법 맛보기

DISTINCT

• 중복을 제거할 때 사용. SELECT의 옵션

SELECT DISTINCT collunm
FROM ~
WHERE ~;

CONVERT

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

• 데이터 형을 변환할 때 사용

// 소숫점이 나오지 않도록 signed int로 형변환
SELECT CONVERT(AVG(collunm), SIGNED INTEGER) AS 'alias'
FROM ~
WHERE ~;

DATE_ADD

DATE_ADD(date,INTERVAL expr unit)

• 날짜를 산술연산할 때 사용

SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
//2001-01-01 00:00:00
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
//2011-01-01 23:59:59
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
//2101-01-01 00:01:00
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
//1899-12-30 14:00:00
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
//1993-01-01 00:00:01.000001

DATEDIFF

DATEDIFF(expr1,expr2)

• 두 날의 차이를 계산한다. expr1 - expr2을 반환하며, Day 이상의 값만 계산한다.

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
//1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
//-31

정리

SELECT DISTINCT 
	CONVERT(
		DATE_ADD('2021-04-10 23:59:59', INTERVAL 5 DAY), DATE
	) 
	AS RecordingDate
FROM recordmediaindexasoneday
WHERE