DBeaver)날짜 & 시간데이터 다루기

blog.csv

0.01MB

login_record.csv

0.03MB

오늘은 날짜, 시간 다루는 법을 알아봅시다. 

강의하단 csv 파일로 연습용 테이블 하나 생성해서 시작합시다. 

 

 

 

 

 

날짜 저장하려면 

 

MySQL의 경우 이런 것 중에 고르면 됩니다. 

 

DATE	YYYY-MM-DD 형식	9999년까지 저장가능
DATETIME	YYYY-MM-DD hh:mm:ss 형식	9999년까지 저장가능
TIMESTAMP	YYYY-MM-DD hh:mm:ss 형식	2038년까지 저장가능

 

TIMESTAMP는 현재 DB시간 기록용 말고는 잘 안씁니다.

테이블 만들 때 DATETIME(6) 이렇게 1~6 숫자를 넣으면 초단위 소수점 6자리까지 기록해줍니다. 

 

 

 

Postgres는 DATE, TIME, TIMESTAMP, TIMESTAMPTZ가 있고

YYYY-MM-DD hh:mm:ss 형식으로 저장해주는 TIMESTAMP를 주로 씁니다.

TIMESTAMPTZ 쓰면 시간 뒤에 timezone도 함께 저장해줌  

 

Oracle은 DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE 이거 3개 다 날짜 + 시간 저장해주는데 

TIMESTAMP는 날짜 + 시간(밀리초까지) 저장해줍니다.

TIMESTAMP WITH TIME ZONE 쓰면 날짜 + 시간(밀리초까지) + timezone도 함께 저장해줌

 

 

 

 

 

 

DATETIME 컬럼 가져오기

 

 

강의하단 csv 파일로 blog 테이블을 만들어봤습니다.

여기서 원하는 날짜를 가져오려면 그냥 문자숫자다루듯 필터링을 주면 됩니다. 

 

 

SELECT * FROM blog WHERE 발행일 > '2022-03-10 08:24:25'
SELECT * FROM blog WHERE 발행일 = '2022-03-10 08:24:25' 

등호 부등호 사용해서 원하는 DATETIME 자료를 가져올 수 있습니다. 

 

 

 

 

 

 

원하는 날짜의 모든 행 가져오기

 

Q. 2022년 3월 10일이 찍혀있는 모든 행을 가져오고 싶으면 어떻게하죠? 

당연히 WHERE 발행일 = '2022-03-10' 이런 자의적인 코딩하면 안되고 

 

SELECT * FROM blog 
WHERE 발행일 >= '2022-03-10 00:00:00' 
  AND 발행일 < '2022-03-11 00:00:00' 

똑똑한 놈들은 이렇게 조건식 쓰면 가능하다는걸 생각해냅니다. 

이러면 2022년 3월 10일 날짜를 가진 모든 행을 찾아줍니다.

 

(참고1) BETWEEN '2022-03-10 00:00:00' AND '2022-03-10 23:59:59' 이래도 되는데

요즘 MySQL에선 시간을 ms 단위까지 몰래 저장해두기 때문에 BETWEEN 쓰면 빵구날 수 있습니다. 

(참고2) 등호 말고 부등호는 발행일 < '2022-03-11' 이렇게 시간 빼먹어도 봐줍니다. 

 

 

 

 

Q. 2022년 3월 10일부터 현재 시간까지 발행된 글들만 출력하고 싶으면?

 

SELECT * FROM blog 
WHERE 발행일 > '2022-03-10 00:00:00' 

이렇게만 해도 될 것 같은데 

근데 DATETIME 컬럼엔 언제나 미래의 날짜시간도 저장할 수 있습니다. 

그래서 미래 시간이 기록되어있으면 그것도 잘 출력되는 불상사가 생길 수 있어서

2022년 3월 10일 ~ 현재시간이라고 정확히 조건을 주는게 좋습니다. 

 

 

 

SELECT * FROM blog 
WHERE 발행일 > '2022-03-10 00:00:00' 
  AND 발행일 <= now() 

현재시간이 필요하면 now() 쓰면 현재 날짜/시간을 그 자리에 남겨줍니다.

그럼 위 코드는 무슨뜻이게요

안알랴줌 

[collapse]

 

- now(6) 이렇게 쓰면 현재 날짜/시간을 초단위 소수점 6자리까지 알려줍니다.

- now()와 비슷한 curdate()도 있는데 이건 시간없이 현재 날짜만 알려줍니다.

 

 

 

SELECT * FROM blog WHERE date(발행일) = '2022-03-10' 

간혹 date() 쓰는 사람들이 있는데

date() 안에 데이터를 넣으면 DATETIME -> DATE 형식으로 바꿔줍니다.

그래서 검색시 그거 써도 되는데 index를 못쓰는 방법이라 느릴 수 있습니다. 

 

 

 

 

 

 

날짜 포맷 마음대로 바꾸기

 

년-월-일 순으로 출력해주는게 싫으면 다른걸로 맘대로 바꿀 수 있습니다. 

 

SELECT date_format(now(), '%Y년 %d일이고 %m월인데요')

date_format() 안에 날짜, 아무문자 차례로 집어넣으면 출력형식을 맘대로 바꿔줍니다. 

아무문자의 안엔 %Y, %m, %d 맘대로 넣을 수 있는데 각각 그 자리에 년, 월, 일이 들어옵니다.  

%H, %i, %s로 시, 분, 초도 넣을 수 있고 요일도 출력가능합니다.

자세한건 필요할 때 date_format 함수 검색해서 씁시다. 

 

 

 

 

 

 

새로운 행에 날짜 insert 하려면

 

INSERT INTO 테이블명 VALUES('2030-01-01 12:00:00'); 

대부분의 상황에선 날짜는 문자와 똑같이 취급하면 잘됩니다. 

 

 

 

 

 

오늘의 숙제 :

 

강의하단 login_record.csv 파일을 가져와서 진행해봅시다. 

어떤 사이트 유저의 이름, 이메일, 9월부터 11월까지의 마지막 접속시간이 저장된 테이블입니다. 

 

Q1. 2022년 11월의 월간 액티브 유저 (MAU) 수를 구해보십시오. 

월간 액티브 유저는 특정 월에 최소 1회 접속한 유저의 수를 의미합니다. 

답으로 163명이 나오면 잘한 것임 

 

어떻게 했냐면

 

SELECT count(*) FROM login_record 
WHERE last_login >= '2022-11-01 00:00:00' 

이라고 코드를 짜보았습니다. 

 

[collapse]

 

 

Q2. 날짜시간을 조작하고 싶습니다.

현재 테이블의 모든 날짜시간에 1년을 더해서 출력하고 싶으면 어떻게할까요? 

날짜를 더해주는 함수가 있는데 당연히 안배운건 검색해봐야합니다. 

 

찾아봅시다

 

SELECT last_login, DATE_ADD(last_login, INTERVAL 1 YEAR) FROM login_record 

DATE_ADD() 써도 되고 last_login + INTERVAL 1 YEAR 해도 1년을 더해준다는군요. 

 

[collapse]

 

 

Q3. 9월의 짝수일에 해당하는 행만 출력해보고 싶은데 어떻게할까요? 

9월12일, 9월14일 이런 날짜를 가진 행들만 출력되면 됩니다. 

 

모르는건 찾아봐야

 

datetime 자료에서 월, 일만 뽑고 싶다면 

MONTH() DAY() 이런거에 넣어주면 된다는군요.

그래서 넣어서 조건식을 작성해봤더니 잘됩니다.

 

SELECT *  FROM login_record 
WHERE MONTH(last_login) = 9 AND DAY(last_login) % 2 = 0