[MySQL] 날짜/시간 다루기 (1)

1. 현재 날짜/시간 출력하기

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

SELECT CURDATE();
-- OUTPUT: '2022-05-01'

SELECT CURDATE() + 0;
-- OUTPUT: 20220501

NOW()

- 현재 날짜와 시간을 출력하기 (쿼리 명령문이 실행을 시작하는 시간을 나타낸다.)

- 출력 형식: 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss

 

SYSDATE()

- 현재 날짜와 시간을 출력하기 (쿼리 명령문 안에서 해당 함수가 실행을 시작하는 시간을 나타낸다)

- 출력 형식: 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss

 

CURDATE()

- 현재 날짜만 출력하기

- 출력 형식: 'YYYY-MM-DD' or YYYYMMDD

 

 

 

2. 날짜/시간 부분만 추출하기

SELECT DATE("2017-06-30 01:02:03");
-- OUTPUT: 2017-06-30

SELECT MONTH("2017-06-30");
-- OUTPUT: 6

SELECT DAYOFMONTH("2017-06-30");
-- OUTPUT: 30

SELECT DAYOFYEAR("2017-06-30");
-- OUTPUT: 181

DATE(expr)

- date나 datetime expression 에서 'date'(연·월·일)만 추출하여 DATE 타입으로 반환한다.

 

YEAR(date)

- date에서 '연도' 부분만 추출하기 (1000 - 9999)

 

MONTH(date)

- date에서 '월' 부분만 추출하기 (0 - 12)

- '월' 부분이 0인 date는 0으로 반환한다. ('0000-00-00' or '2008-00-00' ⇒ 0)

 

DAYOFMONTH(date) = DAY(date)

- date의 '일' 부분이 해당 월에서 몇 일째인지 추출하기 (0 - 31)

- '일' 부분이 0인 date는 0으로 반환한다. ('0000-00-00' or '2008-12-00' ⇒ 0)

 

DAYOFYEAR(date)

- date의 '일' 부분이 해당 연도에서 몇 일째인지 추출하기 (1 - 366)

 

 

SELECT DAYNAME("2022-07-01");
-- OUTPUT: Friday

SELECT DAYOFWEEK("2022-07-01");
-- OUTPUT: 6

DAYNAME(date)

- 해당 날짜의 요일 이름만 추출하기

 

DAYOFWEEK(date)

- 해당 날짜의 요일 인덱스만 추출하기

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Returns NULL if date is NULL.

These index values correspond to the ODBC standard.

요일	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
인덱스	1	2	3	4	5	6	7

 

WEEKDAY(date)

- 해당 날짜의 요일 인덱스만 추출하기

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Returns NULL if date is NULL.

요일	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
인덱스	0	1	2	3	4	5	6

 

 

SELECT HOUR("2017-06-15 15:20:09");
-- OUTPUT: 15

SELECT HOUR("272:59:59");
-- OUTPUT: 272

TIME(expr)

- time나 datetime expression 에서 'time' 부분만 추출해서 문자열(string) 타입으로 반환하기.

 

HOUR(time)

- time에서 '시' 부분만 추출하기

- time-of-day 의 값인 경우, 반환하는 값의 범위는 0 - 23 이다.

- 그러나 time 값의 범위는 실제로 훨씬 크므로 HOUR는 23보다 큰 값을 반환할 수 있다.

 

MINUTE(time)

- time에서 '분' 부분만 추출하기 (0 - 59)

 

SECOND(time)

- time에서 '초' 부분만 추출하기 (0 - 59)

 

 

 

 

SELECT EXTRACT(YEAR FROM '2019-07-02');
-- OUTPUT: 2019

SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
-- OUTPUT: 201907

SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
-- OUTPUT: 20102

SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');
-- OUTPUT: 123

EXTRACT(unit  FROM date)

- 날짜나 시간 값에서 '연도, 월, 일, 시, 분, 초' 와 같이 특정 요소만 추출하는 함수

- integer(정수형)를 반환한다.

- 날짜에서 특정 요소(연도, 월, 일 등)를 추출하는 표준 SQL 방식이다.

- unit에 들어갈 수 있는 표현들은 아래 테이블을 참고하자.

 

출처: https://dev.mysql.com/doc/refman/8.4/en/expressions.html#temporal-intervals

 

 

 

3. 날짜/시간을 지정한 형식대로 출력하기 (DATE_FORMAT)

DATE_FORMAT(date, format)

: 날짜값을 입력하면, 그 날짜를 지정된 형식(format)으로 변환하여 문자열 타입으로 반환한다. (만약 date 또는 format 중 하나라도 NULL이면, 이 함수는 NULL을 반환한다.)

SELECT DATE_FORMAT("2017-06-15", "%Y");
-- OUTPUT: 2017

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %r');
-- OUTPUT: 2022-05-04 08:41:45 AM

 

※ 날짜/시간 형식

%Y	연도(숫자 - 4자리)	%T	hh:mm:ss
%y	연도(숫자 - 2자리)	%r	hh:mm:ss AM/PM
%m	월(숫자 - 2자리)	%H	시간 (00 to 23) → 2자리
%c	월(숫자 - 원래대로)	%k	시간 (0 to 23) → 원래대로
%d	일(숫자 - 2자리)	%h or %I(대문자 아이)	시간 (00 to 12) → 2자리
%e	일(숫자 - 원래대로)	%l(소문자 엘)	시간 (1 to 12) → 원래대로
%M	월(영문 - 길게)	%s or %S	분
%b	월(영문 - 짧게)	%s or %S	초
%W	요일(영문 - 길게)	%p	AM, PM
%a	요일(영문 - 짧게)

 

MySQL Documentation

👉 https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_date-format

 

 

 

 

4. 날짜/시간이 문자열 타입으로 저장되어 있는 경우, 형변환하기

-- (1) 문자열과 포맷을 맞췄을 경우
SELECT STR_TO_DATE('20220504090000', '%Y%m%d%H%i%s') -- OUTPUT: 2022-05-04 09:00:00

SELECT STR_TO_DATE('20220504', '%Y%m%d') -- OUTPUT: 2022-05-04

SELECT STR_TO_DATE('090000', '%H%i%s') -- OUTPUT: 09:00:00

SELECT STR_TO_DATE('May 4, 2022', '%M %e,%Y') -- OUTPUT: 2022-05-04

SELECT STR_TO_DATE('04,5,2022', '%d,%c,%Y') -- OUTPUT: 2022-05-04

-- (2) 문자열과 포맷이 다를 경우
SELECT STR_TO_DATE('04,5,2022', '%d%m%Y') -- OUTPUT: NULL

STR_TO_DATE(str, format)

- 문자열을 DATE or TIME or DATETIME 데이터 타입으로 변환하여 출력한다.

- 문자열과 포맷이 다를 경우 NULL 을 반환하기 때문에, 문자열과 포맷을 꼭 동일하게 맞춰 주어야 한다.

 

 

 

 

[참고] 12.7 Date and Time Functions

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions