[MySQL || MariaDB] 자주 쓰는 날짜 API와 날짜 실전 예제 총 정리

자주 쓰는 날짜 API

---

CURDATE() : 현재 날짜를 반환

---

SELECT CURDATE();

결과 : YYYY-MM-DD || YYYYMMDD(시간 반환X)

 

 

CURTIME() : 현재 시간을 반환

---

SELECT CURTIME();

결과 : HH:MM:SS || HHMMSS 

 

 

SYSDATE() || NOW() : 현재날짜 시간을 반환

---

SELECT SYSDATE();
SELECT NOW();

결과 : YYYY-MM-DD HH:MM:SS || YYYYMMDDHHMMSS

 

SYSDATE()와 NOW()는 엄연히 다르다. 절때 다르다. 당연히 다르다

SYSDATE() : 그 함수(쿼리포함) 실행되는 시점의 시각을 바로 리턴

NOW() : 쿼리가 끝난 시점의 시각 리턴

 

예를 들어 SELECT * FROM computer_value 를 조회하는데 데이터가 많아서 10분이상 혹은 1시간 10시간 걸렸다면

처음 조회한 첫 데이터의 SYSDATE()는 20시 30분이였다면

중간 조회된 데이터 SYSDATE()는 21시 20분

끝은 22시 30분 이런식..

 

거기에 FULL-TABLE-SCAN을 하기 때문에 성능상에도 굉장히 좋지 않다.

 

한마디로 NOW()를 쓰자.

 

 

UNIX_TIMESTAMP() : '1970-01-01 00:00:00' 부터의 UTC 시간을 초를 반환

---

SELECT UNIX_TIMESTAMP()

#값을 지정 할 수도 있다.
#그럼 이 시간 기준으로 반환된다.
SELECT UNIX_TIMESTAMP('2019-11-12 17:00');

결과 : 초를 반환하기 때문에 1573545907 이런식

 

 

FROM_UNIXTIME(unix_timestamp) : UTC 시간(timestamp)을 날짜 형식으로 변환한다.

---

SELECT FROM_UNIXTIME(1573545600);

결과 : 2019-11-12 17:00:00

 

 

DATE_FORMAT(date,format) : 날짜를 해당 형식의 문자열로 변환하여 반환.

---

대표적인 예시

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%H:%i:%s');

결과 : 20:23:00

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%y-%m-%d');

결과 : 19-11-12

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%y-%m-%d %H:%i:%s');

결과 : 19-11-12 20:23:00

FORMAT 양식은 너무 길어서 생략 합니다.

 

 

STR_TO_DATE(str, format) : 문자열 str와 형식 문자열 format을 입력받음

---

받은 str 문자열을 Date 형식으로 바꿔줍니다.

SELECT STR_TO_DATE('2019-11-12','%y-%m-%d')

결과 : 2019-11-12

SELECT STR_TO_DATE('August 12 2019', '%M %d %Y')

결과 : 2019-08-12

SELECT STR_TO_DATE('Nov 12 2019', '%M %d %Y')

결과 : 2019-11-12

 

주의 : str이랑 format 폼을 꼭 동일하게 맞춰야 합니다. 맞지 않을 경우 null 반환

 

 

DATE_SUB(date, INTERVAL) 빼기

---

현재 시간 - 1시간(DATETIME 값만 가능)

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

현재 시간 - 1일

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

현재 시간 - 1달

SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

 

 

DATE_ADD(date, INTERVAL) 더하기

---

현재 시간 + 1시간(DATETIME 값만 가능)

SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

현재 시간 + 1일

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

현재 시간 + 1달

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

현재 시간 - 1시간(DATETIME 값만 가능)

SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR);

현재 시간 - 1일

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

 

 

DATEDIFF(date1,date2) 두 날짜 차이 구하기

---

두 날짜의 일 차이를 가져오는 것입니다.

앞에 파라미터(date1)를 기준으로 합니다.

select datediff('2019-11-15','2019-11-10')

결과 : 5  (양수)

select datediff('2019-11-10','2019-11-15')

결과 : -5 (음수)

 

 

TIMESTAMPDIFF(unit(단위),date1,date2)

---

두 날짜의 시간 차이를 가져오는 것입니다.

이것 역시 앞에 파라미터(date1)를 기준으로 합니다.

select TIMESTAMPDIFF(SECOND,'2019-11-10 18:22:30','2019-11-10 18:22:50')

결과 : 20(초)

select TIMESTAMPDIFF(SECOND,'2019-11-10 18:22:50','2019-11-10 18:22:30')

결과 : -20(초)

select TIMESTAMPDIFF(MINUTE,'2019-11-10 18:28:55','2019-11-10 18:40:20')

결과 : 11(분) 

주의 : 왜 12가 아니고 11일까? 바로 초가 모자라서 그렇습니다. 28분 55초-40분 20초 그래서 11분으로 인식됩니다. 조심

 

 

TIMESTAMPDIFF, DATE_SUB, DATE_ADD에 쓸 수 있는 포맷

---

SECOND

MINUTE

HOUR

DAY

MONTH

YEAR

 

 

문법

---

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE computerValue(

id int,
cpu_value int,
memory_value int,
graphics_card_value int,
time_value datetime

)

날짜 데이터는 많이 들어가 있어야 쿼리 할 때 이해가 더 쉬우니 데모데이터를 넣어봅시다.

DEMO CSV 만들기

https://www.mockaroo.com/

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

설정

설정을 끝낸 뒤 Download Data 누르시면 됩니다.

 

만들어진 CSV 파일 MariaDB에 import 하기

LOAD DATA LOCAL INFILE 'C:/Users/사용자/Desktop/MOCK_DATA.csv'(csv 파일 경로)
REPLACE
INTO TABLE `localtest(DB 이름)`.`computervalue(TABLE 이름)`
COLUMNS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
#CSV 컬럼들
(@id, @name, @cpu_value, @memory_value,@graphics_card_value,@time_value) 
#테이블컬럼 = CSV 컬럼 
SET `id` = @id, `name` = @name, `cpu_value` = @cpu_value, `memory_value` = @memory_value,`graphics_card_value` = @graphics_card_value,`time_value` = @time_value; 

 

주의 - file path 부분

---

Windows : C:/Users/   "/"(슬래쉬)를 사용

Linux : \temp\home\  "\"(역슬래쉬) 사용

---

 

실전 예제

 

DATE 값 기준 최근 동향 구하기

---

최근 3일 컴퓨터의 CPU or MEMORY 70% 사용량을 구하기

SELECT DISTINCT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 DAY) 
AND (cpu_value >= 70 OR memory_value >= 70);

최근 n일은 DATE_ADD(CURRENT_DATE(), INTERVAL -3 DAY) INTERVAL 뒤에 정수값만 바꿔주시면 됩니다.

 

DATETIME 값 기준 최근 동향 구하기

---

최근 3일 컴퓨터의 CPU or MEMORY 70% 사용량을 구하기

SELECT DISTINCT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(NOW(), INTERVAL -3 DAY) 
AND (cpu_value >= 70 OR memory_value >= 70);

이럴 경우 DATETIME인 경우는 그 최근 n일 사이에 70%가 넘은 데이터가 여러 개라면 이름과 아이디가 똑같은 데이터가 여러 개 나오겠죠? 테이블에 표출하고 싶은데 말이죠

 

2(컴퓨터ID) 사용량 사용량 2019-11-12 19:05

2(컴퓨터ID) 사용량 사용량 2019-11-12 21:35 처럼요.

 

DATETIME 값 기준 최근 동향 구하기

---

최근 3일 1컴퓨터당 1개의 데이터만 사용량이 제일 높았던 것 구하기

SELECT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(NOW(), INTERVAL -1 DAY) and (cpu_value > 70 or memory_value > 70)
GROUP BY name,id

 

실시간 사용량 TOP5 구하기

SELECT *
FROM computervalue
where `time_value` IN (SELECT MAX(time_value) FROM computervalue) 
order by cpu_value desc, memory_value desc,graphics_card_value desc limit 5;

CPU -> MEMORY -> GRAPHIC 기준입니다.

우선순위를 바꾸시려면 order by 컬럼위치를 바꾸시면 됩니다.

 

DATE : 2019-11-10 처럼 날짜만 나오는 데이터.

DATETIME : 2019-11-10 15:35처럼 날짜+시간이 같이 나오는 데이터

 

 

추가 실전 예제는 제가 프로젝트를 진행하면서 남들도 어렵다고 생각하겠다 OR 제가 삽질했던거 OR 자주 쓰는데 계속 까먹는 것 위주로 올립니다

이 글은 계속 업데이트 될 예정입니다.

 

예제 파일 제공

---

[MariaDB] date API+date query.sql

0.00MB