11-4.날짜와 시간

1.날짜, 시간 함수

날짜형은 DBMS 별로 타입만 다른게 아니라 함수도 제각각이다. 개념은 다 비슷하지만 값을 다루는 형식과 절차가 다르니 어쩔 수 없이 사용하는 DBMS에 맞게 따로 배워야 한다. 먼저 오라클의 날짜 관련 함수를 보자.

함수	설명
SYSDATE	현재 날짜를 조사한다. 운영체제 시간 기준이다. 초단위
CURRENT_DATE	접속 세션의 현재 날짜를 조사한다. 초단위
SYSTIMESTAMP	시간대를 기준으로 한 현재 시간을 구한다. 나노초 단위
CURRENT_TIMESTAMP	접속 세션 기준으로 한 현재 시간을 구한다. 나노초 단위
TRUNC	시간 단위를 버림한다.
ROUND	정오를 기준으로 반올림한다.
LAST_DAY(d)	지정한 날짜가 속한 월의 마지막 날을 구한다.
ADD_MONTH(date, n)	n개월을 더한다.
MONTHS_BETWEEN(d1, d2)	두 날짜의 개월수 차이를 구한다.
NEXT_DAY(d, c)	d 날짜의 다음 c요일을 찾는다.
EXTRACT(what FROM d)	d날짜의 요소를 조사한다. YEAR, MONTH, DAY, HOUR, MINUE, SECOND 등의 요소를 지정한다.

현재 시간을 구하는 함수는 해상도와 기준 시간에 따라 4가지가 있다. 시스템 시간은 컴퓨터의 시간이며 세션은 세계 표준시를 의미한다. 보통은 SYSDATE 함수로 초단위의 시간을 구한다. 함수이지만 인수가 없어 괄호를 붙이지 않으며 오히려 괄호를 붙이면 에러임을 유의하자.

SELECT SYSDATE FROM dual; -- 20/10/17 12:21:42

컴퓨터의 타이머를 참조하여 현재 시간을 조사하므로 명령을 내릴 때마다 결과는 달라진다. 이 명령을 여러 번 실행하면 무심히 흘러가는 시간을 볼 수 있다. SQL 문내에서 오늘 날짜를 참조하거나 비교할 때 이 함수를 사용한다. 다음 쿼리문은 오늘 입사한 직원의 정보를 추가한다.

INSERT INTO tStaff VALUES ('김한슬', '기획실', '여', SYSDATE, '수습', 480, 50);

입사일인 joindate 자리에 SYSDATE 함수를 적으면 현재 날짜를 자동으로 입력한다. 새로 삽입하는 레코드는 항상 현재 시간으로 초기화한다면 테이블 정의문에 디폴트를 SYSDATE로 지정한다.

CREATE TABLE tMoMo

(

somedate DATE DEFAULT SYSDATE

);

레코드 삽입시 필드값을 지정하지 않으면 현재 시간을 자동으로 대입한다. 주문 테이블의 주문 시점이나 배송 테이블의 배송 출발 시간을 이렇게 지정해 놓으면 레코드를 생성할 때의 시간을 알아서 기록하므로 편리하다.

날짜간의 연산은 +, - 연산자를 사용한다. 피연산자로 수치값을 주는데 1은 하루를 의미한다. 오늘 날짜에서 12일 지난 날짜를 구하려면 12를 더한다. 이때 날짜가 월의 경계를 넘어가면 월의 값도 같이 증가한다. 예를 들어 6월 29일에서 12일을 더하면 6월 41일이 되는 것이 아니라 7월 11일이 된다. 각 월의 길이와 윤년까지 고려하여 정확히 계산한다.

SELECT SYSDATE + 12 FROM dual;

시나, 분을 더할 때는 하루를 시 단위나 분 단위로 나누어 실수값을 더한다. 다음은 각각 현재의 5시간 후, 30분 전, 80초 전을 구한다. 하루는 24시간 1440분, 86400초임을 알면 아주 쉽다. 가령 12시간은 12/24인 0.5이며 하루의 절반이다.

SELECT SYSDATE + 5/24 FROM dual;

SELECT SYSDATE - 30/1440 FROM dual;

SELECT SYSDATE - 80/86400 FROM dual;

날짜간의 간격을 구할 때는 날짜끼리 뺀다. 다음 쿼리는 입사 후 며칠이나 지났는지 계산한다.

SELECT name, sysdate - joindate FROM tStaff;

날짜에서 수치를 더하거나 빼면 결과는 날짜이지만 날짜끼리 빼면 결과는 날짜가 아닌 수치값임을 유의하자. 두 날짜간의 간격은 며칠 형식으로 정확히 표현할 수는 있어도 몇 년 몇 개월 며칠이라고는 표현할 수 없다. 두 날짜 사이에 윤년의 존재 여부에 따라 오차가 있어 일수로만 평가한다.

현재 시간을 기준으로 소수점 이하까지 계산하여 지저분해 보인다. 시간은 필요 없고 날짜까지만 구하려면 TRUNC 함수로 시간을 잘라 버리든가 아니면 ROUND 함수로 정오를 기준으로 반올림 처리한다.

2.날짜 타입 변환

날짜의 요소를 분리하거나 출력 형식을 변경할 때는 문자열과의 타입 변환 과정을 활용한다. 다음 두 함수는 날짜를 문자열로, 문자열을 날짜로 변환하며 포맷을 상세히 지정할 수 있다.

TO_CHAR(날짜, 포맷)

TO_DATE(문자열, 포맷)

포맷을 어떻게 지정하는가에 따라 추출하는 요소나 출력 형태가 달라진다. 날짜 요소를 지정하는 포맷은 다음과 같으며 대소문자는 구분하지 않는다.

포맷	설명	예
YY	두 자리 년도	21
YYYY	네 자리 년도	2021
RR	두 자리 년도. 50이상은 19xx	2021
CC	세기	21
YEAR	영문 년도	TWENTY TWENTY-ONE
MM	숫자 월	03
MONTH	영문 월	March
MON	영문 약어 월	Mar, 3월
DD	숫자 일	14
DDD	1년 기준 일	1~366
DAY	요일	화요일, Friday
D	숫자 요일	1~7. 1이 일요일
DY	요일 약어	화, Tue
HH	12시간제 시간	1~12
HH24	24시간제 시간	0~23
MI	분	55
SS	초	45
FFn	밀리초. n은 소수점 자리수이며 1~9까지. 생략시 6	12.34
AM, PM	오전인지 오후인지	오전, 오후
W	한달 기준 주	1~5
WW	1년 기준 주	0~53
DL	년월일과 요일	2021년 3월 30일 화요일

대체로 상식과 일치하는 서식이지만 몇 가지는 주의가 필요하다. 분과 월의 앞 글자가 같다 보니 분은 MM이 아니라 MI로 되어 있다. 시간은 12시간제의 HH와 오전/오후인 AM을 함께 쓰든지 아니면 24시간제의 HH24를 사용한다. SYSDATE를 그냥 출력하면 버전이나 설정에 따라 출력 포맷이 달라지지만 TO_CHAR로 변환하여 출력하면 원하는 형태를 선택할 수 있다.

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') FROM dual; -- 2020/10/17 12:18:51

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd AM hh:mi:ss') FROM dual; -- 2020/10/17 오후 12:20:35

년월일 요소 사이에는 / 나 - 구분자를 넣고 시분초 요소 사이에는 :이나 .을 넣는 방식이 일반적이다. 서식이 아닌 임의의 문자를 삽입할 때는 큰 따옴표로 감싼다. 한글도 서식 사이에 넣을 수 있다.

SELECT TO_CHAR(SYSDATE, 'yyyy"년" mm"월" dd"일" hh24"시" mi"분" ss"초"') FROM dual;

-- 2020년 10월 17일 12시 24분 44초

TO_CHAR 함수의 포맷에 일부 요소의 서식만 지정하여 날짜의 특정 요소만 뽑아 개별적으로 다룰 수 있다. 예를 들어 서식에 'yyyy'라고만 적으면 년도만 뽑아낸다. 다음 쿼리는 각 직원의 입사년도를 구한다.

SELECT name, TO_CHAR(joindate, 'yyyy') FROM tStaff;

joindate 필드에는 년월일이 다 포함되어 있지만 TO_CHAR 함수로 년도만 뽑아 냈다. 이렇게 추출한 값은 근무 년수나 퇴직금 계산 등에 사용할 수 있으며 그룹핑도 가능하다. 다음 쿼리는 년도별 입사 사원수를 구한다.

SELECT TO_CHAR(joindate, 'yyyy'), count(*) FROM tStaff GROUP BY TO_CHAR(joindate, 'yyyy') ORDER BY TO_CHAR(joindate, 'yyyy');

TO_CHAR(joindate, 'yyyy') 계산식도 필드와 자격이 같아 그룹핑, 정렬, 출력 필드 지정에 모두 사용할 수 있다. 똑같은 방식으로 년도 뿐만 아니라 월, 일, 시, 분, 초도 다 분리할 수 있다.

SELECT TO_CHAR(joindate, 'yyyy') AS 년, TO_CHAR(joindate, 'mm') AS 월,

TO_CHAR(joindate, 'dd') AS 일 FROM tStaff;

년도별, 월별, 일별 통계를 구하는 경우는 흔하지만 그렇다고 해서 년, 월, 일 필드를 따로 정의할 필요는 없다. 오히려 입력할 때 나누어 넣기가 더 번거롭다. 평이한 날짜 타입으로 정의해도 원하는 요소를 얼마든지 손쉽게 추출할 수 있다.

문자열을 날짜로 변환할 때는 TO_DATE 함수를 사용한다. 임의의 날짜를 만들 때는 문자열 형태로 날짜 상수를 적고 TO_DATE로 변환한다. 다음 쿼리문은 3.1운동이 일어난 날짜를 구해 출력한다.

SELECT TO_DATE('1919/3/1', 'yyyy/mm/dd') FROM dual;

문자열로 '1919/3/1'이라고 표기했지만 TO_DATE 함수에 의해 날짜 형식으로 바뀐다. 이때 뒤쪽 서식으로 날짜의 포맷을 정확히 알려 주어야 한다. 'yyyy/mm/dd'는 년도 4자리, 월 2자리, 일 2자리이며 구분자는 /임을 명시하여 '1919/3/1'의 날짜 요소를 파악할 수 있는 정보를 제공한다. 서식과 문자열 포맷만 일치하면 어떤 형식이든 사용할 수 있다. 다음 두 형식 모두 가능하다.

SELECT TO_DATE('1919-3-1', 'yyyy-mm-dd') FROM dual;

SELECT TO_DATE('19190301', 'yyyymmdd') FROM dual;

구분자를 - 로 지정해도 되고 구분자 없이 자리수를 정확히 맞춰 주어도 잘 변환한다. 구분자 /나 -를 쓰거나 자리수가 정확하면 포맷을 생략해도 기가 막히게 알아 듣는다. 다음 세 문장 모두 잘 변환한다.

SELECT TO_DATE('1919/3/1') FROM dual;

SELECT TO_DATE('1919-3-1') FROM dual;

SELECT TO_DATE('19190301') FROM dual;

그러나 TO_DATE('1919123') 식으로 자리수가 맞지 않으면 1월 23일인지 12월 3일인지 애매해서 에러이다. TO_DATE('1919/1/23')이나 TO_DATE('1919-12-3') 식으로 날짜 요소를 구분할 수 있어야 한다.

날짜를 출력만 한다면 굳이 TO_DATE로 바꿀 필요 없이 문자열을 그냥 출력하면 된다. 그러나 임의의 날짜를 연산에 사용하려면 반드시 날짜로 바꿔야 한다. 예를 들어 3.1운동이 일어난지 며칠이나 지났는지 알고 싶다고 하자. 오늘 날짜에서 3.1운동이 일어난 날짜를 빼면 에러이다.

SELECT sysdate - '1919/3/1' FROM dual; -- 에러

날짜에서 문자열을 뺄 수는 없다. 게다가 문자열 포맷만 보고 섣불리 날짜 타입으로 자동 변환하는 것도 위험하다. 그래서 문자열을 날짜로 명시적으로 변환한 후 빼야 한다.

SELECT sysdate - TO_DATE('1919/3/1') FROM dual;

양변을 다 날짜 타입으로 일치시키면 연산하는데 아무 문제가 없다. 거의 4만일이 다 되어 가고 있다. 소수점 이하가 번잡스럽다면 TRUNC 함수로 소수점 이하를 잘라 버리면 된다.

임의의 날짜를 연산하여 보기 좋게 출력하려면 양쪽 변환이 모두 필요하다. 예를 들어 2023년 3월 8일 태어난 아기의 백일잔치 날짜를 구하려면 다음과 같이 한다.

SELECT TO_CHAR(TO_DATE('2023/3/8', 'yyyy/mm/dd') + 99, 'yyyy"년" mm"월" dd"일"') FROM dual;

TO_DATE 함수로 생일을 날짜 형식으로 바꾼다. 이 날짜에 99를 더하면 태어난지 100일째이다. 태어난 날부터 1일로 치며 100일 잔치는 100일 후가 아닌 100일째에 하는 것으므로 100이 아닌 99를 더한다. 이렇게 구한 100일 잔치날을 원하는 형식으로 포맷팅하기 위해 TO_CHAR 함수로 다시 문자열로 변환하였다.

연습 문제

8.년도별 입사 인원을 구하는 쿼리에 TO_CHAR(joindate, 'yyyy') 계산식이 세 번이나 나온다. 인라인뷰를 사용하여 이 식을 한 번만 사용하도록 재작성하라.

9.tOrder에서 7일 이내에 발생한 주문 목록을 구하라. 일주일 이내에만 반품을 받아준다면 구입 날짜와 오늘 날짜를 계산하여 주문 경과일을 구해야 한다.

3.SQL Server의 날짜 함수

SQL Server의 날짜 함수도 형식만 다를 뿐 오라클과 기능상 유사하다. 약간만 응용하면 유사한 함수를 찾아 쓸 수 있다.

함수	설명
GETDATE()	현재 날짜와 시간을 구한다.
GETUTCDATE()	현재 날짜와 시간을 국제 표준시로 구한다. 우리나라 시간보다 9시간 느리다.
DATEPART(요소, 날짜)	날짜에서 요소를 분리한다.
DATENAME(요소, 날짜)	날짜에서 요소를 문자열로 분리한다.
YEAR(날짜)	날짜값에서 연도만 분리한다. YEAR(GETDATE())는 현재 년도만 출력한다.
MONTH(날짜)	날짜값에서 달만 분리한다.
DAY(날짜)	날짜값에서 날짜만 분리한다.
DATEADD(요소,값,날짜)	날짜에서 특정 요소를 더한다.
DATEDIFF(요소,날1,날2)	두 날짜 사이의 차이를 요소 단위로 구한다.

현재 날짜를 구하는 함수는 GETDATE이다. 함수이므로 인수가 없어도 반드시 괄호를 붙여야 한다. 현재 시간을 삽입하거나 필드의 디폴트값으로 지정할 때 이 함수 호출문을 작성한다.

SELECT GETDATE();

날짜에서 년, 월, 일을 분리할 때는 YEAR, MONTH, DAY 함수를 사용한다. tStaff의 입사일을 년, 월, 일로 분리해서 출력하면 하나의 필드가 결과셋에 세 개의 정수로 나타난다.

SELECT name, YEAR(joindate) AS 년, MONTH(joindate) AS 월, DAY(joindate) AS 일 FROM tStaff;

분리된 각 날짜 요소는 정수형이므로 수치 연산에 사용할 수 있으며 GROUP BY의 기준 필드로 쓸 수 있다. 다음 쿼리는 년도별 입사 사원수를 조사한다.

SELECT YEAR(joindate), count(*) FROM tStaff GROUP BY YEAR(joindate)

ORDER BY YEAR(joindate);

시분초나 요일, 주를 분리하는 함수는 따로 제공하지 않으며 DATENAME과 DATEPART 함수를 사용한다. 이 두 함수는 첫 번째 인수로 분리할 요소를 지정한다. 같은 요소에 대해 여러 가지 표현이 있는데 편한대로 사용하면 된다. 즉 년도를 알고 싶을 때 yyyy라고 써도 되고 year라고 써도 된다.

부분	설명
year, yy, yyyy	년도
quarter, qq, q	분기
month, mm, m	월
dayofyear, dy, y	1년중의 일
day, dd, d	일
week, wk, ww	주
weekday, dw	요일
hour, hh	시
minute, mi, n	분
second, ss, s	초
millisecond, ms	1/1000

DATENAME은 요소를 문자열 형태로 리턴하며 DATEPART는 숫자 형태로 리턴한다는 점이 다르다. 출력에는 DATENAME이 간편하고 연산에는 DATEPART가 편리하다. 다음 두 명령은 오늘 날짜에서 요일만 분리하여 출력한다.

SELECT DATEPART(dw, GETDATE());

SELECT DATENAME(dw, GETDATE());

DATENAME은 '화요일'을 리턴하는데 비해 DATEPART는 3을 리턴한다. 년, 월, 일을 분리하는 YEAR, MONTH, DAY 함수는 DATEPART 함수의 축약형이다.

날짜끼리 연산할 때는 DATEADD와 DATEDIFF 함수를 사용한다. DATEADD(day, 12, GETDATE())는 오늘부터 12일 후를 리턴한다. 다음 문장은 입사일로부터 며칠이나 근무중인지 조사한다.

SELECT name, DATEDIFF(day,joindate,GETDATE()) FROM tStaff;

joindate와 오늘의 차이를 날짜 단위로 구한다. day를 hour나 second로 변경하면 시간 단위나 초단위로 계산한다. 다음 명령은 3.1 운동 후 며칠이 지났는지 계산한다.

SELECT DATEDIFF(day, '1919/3/1', GETDATE());

CAST('1919/3/1' AS DATETIME)로 형 변환을 해야 하는 것이 원칙이지만 구분자가 있거나 자리수가 정확히 맞으면 자동으로 변환한다.

날짜의 형식을 문자열로 변환할 때는 타입 변환 함수인 CONVERT를 사용한다. 세 번째 스타일 인수로 각 국가별 날짜 형식을 지정한다. 100 미만의 스타일은 년도를 두 자리로 표기하고 100 이상의 스타일은 네 자리로 표기한다. 수십가지 스타일 중 주로 많이 쓰는 것만 보이면 다음과 같다.

yy(두자리)	yyyy(네자리)	방식	설명
	0, 100	디폴트	디폴트가 적용된다. mon dd yyyy hh:mm AM/PM
1	101	미국	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	영국/프랑스	dd/mm/yy
11	111	한국, 일본	yy/mm/dd
12	112	ISO	yymmdd

우리 나라는 주로 11번이나 111번을 사용하며 미국이나 유럽 스타일도 정의되어 있다. 동양은 년월일순, 미국은 월일년순, 유럽은 일월년순으로 문화마다 날짜 표기법이 다양하고 구분자도 다르다.

SELECT CONVERT(VARCHAR(20), GETDATE(),0); -- 06 29 2021 10:43PM

SELECT CONVERT(VARCHAR(20), GETDATE(),11); -- 21/06/29

SELECT CONVERT(VARCHAR(20), GETDATE(),111); -- 2021/06/29

SELECT CONVERT(VARCHAR(20), GETDATE(),101); -- 06/29/2021

SELECT CONVERT(VARCHAR(20), GETDATE(),103); -- 29/06/2021

문화별 날짜 형식을 쉽게 선택할 수 있는 이점이 있지만 이 방식은 SQL Server만 지원하며 표준과는 거리가 멀다. 구형 코드를 분석할 때를 위해 알아 두어야 하지만 앞으로는 쓰지 않는 것이 바람직하다. 이 방법이 아니더라도 날짜 요소를 분리하여 조립하면 원하는 형식으로 얼마든지 변환할 수 있다.

4.MariaDB의 날짜 함수

MariaDB의 날짜 관련 함수도 SQL Server나 오라클과는 상이하다.

함수	설명
CURDATE	현재 날짜를 조사한다.
CURTIME	현재 시간을 조사한다.
NOW, SYSDATE	현재 날짜와 시간을 조사한다.
DATE_FORMAT	날짜를 원하는 형식으로 포맷팅한다.
STR_TO_DATE	문자열을 날짜 타입으로 변환한다.
UNIX_TIMESTAMP	1970.1.1 자정 이후 경과 초. 에폭타임
FROM_UNIXTIME	엑폭타임을 날짜 포맷으로 변환
DATE_ADD	날짜 더하기
DATE_SUB	날짜 빼기
DATEDIFF	날짜 차이 구하기

현재 날짜와 시간은 다음 쿼리문으로 간단히 구한다. 날짜만, 시간만 따로 구할 수도 있다. MariaDB도 오라클의 SYSDATE를 지원하지만 NOW는 쿼리가 끝난 시점인데 비해 SYSDATE는 쿼리 시작 시간이라는 미세한 차이가 있다.

SELECT NOW();

DATE_FORMAT 메서드는 날짜의 포맷을 변환하거나 요소를 분리한다. 포맷을 지정하는 서식은 대소문자를 구분한다. 포맷 문자는 다르지만 오라클과 종류는 거의 비슷하다.

포맷	설명	예
%y	두 자리 년도	21
%Y	네 자리 년도	2021
%m	두자리 숫자 월.	03
%c	숫자 월.	3
%M	영문 월	March
%d	두자리 숫자 일.	01, 02
%e	숫자 일.	1, 2
%j	1년 기준 일	1~366
%w	숫자 요일	0~6. 0이 일요일
%W	요일	화요일, Friday
%a	요일 약어	화, Tue
%H	두 자리 24시간제 시간.	00~23
%h	두 자리 12시간제 시간	01~12
%k	24시간제 시간.	0~23
%l(소문자 엘)	12시간제 시간	1~12
%i	분	55
%S, %s	초	45
%f	초 이하 소수점 여섯재짜리까지	001234
%p	오전인지 오후인지	오전, 오후, AM, PM
%T	'%H:%i:%S'	15:23:34
%r	'%I:%i:%S %p'	3:23:34 PM
%u	주 번호	0~53. 첫날이 일요일
%U	주 번호	0~53. 첫날이 월요일
%%	% 문자	%

현재 날짜와 시간을 출력하기 가장 무난한 포맷으로 출력하려면 다음과 같이 한다.

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

다음 쿼리문은 연도만 추출하여 년도별로 입사한 사원의 수를 조사한다.

SELECT DATE_FORMAT(joindate, '%Y'), count(*) FROM tStaff

GROUP BY DATE_FORMAT(joindate, '%Y') ORDER BY DATE_FORMAT(joindate, '%Y');

날짜에서 특정 요소의 값을 더하거나 뺄 때는 DATE_ADD, DATE_SUB를 사용하며 요소는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND가 있다. (날짜, INTERVAL 값 요소) 형식으로 더하거나 뺄 값을 지정한다.

SELECT DATE_ADD(NOW(), INTERVAL 12 DAY); -- 12일 후

SELECT DATE_ADD(NOW(), INTERVAL 5 HOUR); -- 5시간 후

SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE); -- 30분 전

날짜간의 간격은 DATEDIFF로 구하며 일 단위를 리턴한다. 다음 쿼리는 입사한 후 근무한 일수를 계산한다.

SELECT name, DATEDIFF(NOW(), joindate) FROM tStaff;

이상으로 세 DBMS의 날짜, 시간 함수를 정리해 봤는데 함수 이름이 약간씩 다를 뿐 기능 목록은 거의 비슷하다. 결국 이쪽 DB에서 가능한 방법은 저쪽 DB에서도 가능하다는 얘기다. 주로 사용하는 DBMS의 함수 외에는 외워서 쓰는 것이 거의 불가능해 필요할 때마다 레퍼런스를 찾아 가며 써야 한다.