MySQL 시간관련데이터 처리

MySQL에도 시간관련데이터를 처리하기 위한 키워드들이 존재한다.

 

먼저 테이블을 만들고 데이터를 넣어보겠다.

 

people이란 테이블을 만들고 컬럼에 name , birthdate, birthtime, birthdt 를 만드는데, 

 

birthdate, birthtime, birthdt  에도 문자열 데이터를 받을 것이지만, 각각 date, time, datetime 으로 처리하였다.

 

이것은 시간관련 데이터를 쉽게 하기 위해서이다.

 

그리고 데이터를 넣을때는 날짜 시간 타입을 국제표준표기로 맞춰서 넣어야한다.

 

위의 테이블로 데이터 가공을 한다 하면,

-- 시간 관련으로 저장된 데이터 들을 다루는 함수()
 
 
day()  -- 날짜만 나옴
select name, day(birthdate)   
from people;
 
dayname()  -- 요일정보가 나옴
select name, dayname(birthdate) 
from people;
 
dayofweek()  -- 요일정보를 숫자로 받음. sunday가 1   saturday 7 
select name, dayofweek(birthdate) 
from people;
 
dayofyear()  -- 입력된 날짜는 1월1일을 1로 놓고, 며칠째 인지를 알려준다.(date 에 해줌)
select name, dayofyear(birthdate), birthdate
from people;
 
select name, dayofyear(birthtime), birthtime    -- 잘못된 사용.time에는 
from people;
 
-- month()  -- 해당 날짜의 월만 가져옴.  숫자로
select name, birthdt, month(birthdt)
from people;
 
-- monthname()    월을 문자로 가져옴
select name, birthdt, monthname(birthdt)
from people;
 
-- hour()  시간만 가져옴
select name, birthtime, hour(birthtime)
from people;
 
select name, birthdt, hour(birthdt)
from people;
 
-- minute  -- 분만 가져옴
select name, birthdt, minute(birthdt)
from people;
 
-- second 초만 가져옴
select name, birthdt, second(birthdt)
from people;
 
-- 함수 사용할때 레퍼런스 확인하여 사용!!   
-- https://www.tutorialspoint.com/mysql/mysql-date-time-functions.htm

상기와 같은 것들이 가능하다.   더 많은 함수가 있다. 레퍼런스를 통하여 알 수 있다.

 

그 중에서도 

 

date_format()  함수는 데이터의 형식을 바꾸는 함수이다.

 

기존의 표기는 국제 표준에 맞춰 2021-03-16 11:07:40     이런식으로 연, 월, 일, 시, 분, 초가 표기되었다.

 

이러한 표기법을 바꾼다.

 

select date_format( birthdt, '%m/%d/%Y')
from people;   

 

위는 birthdt 컬럼 데이터의 형식을 월/일/년   으로 바꿔준다.

*실행화면

기존의 데이터 형식이 바뀌었다.

 

date_format()을 이용하여 문자열로도 바꿀 수 있다.

 

select date_format( birthdt, '나는 %W 에 태어났다')  
from people;

 

실행화면은,

위와 같이 해당 날짜의 요일을 문자열로 나타내고 , 원하는 문자열이 되었다.

 

data_format() 에 사용되는 포맷형식도 레퍼런스를 참고하라.

 

그리고 시간 처리 관련 데이터로 작성했을시에는

 

날짜의 연산 또한 가능하다.

 

select birthdt, date_add(birthdt, interval 1 month)   -- 1개월을 더해서 나타낸다.
from people;
 
select birthdt, date_add(birthdt, interval 24 second)   -- 24초를 더해서 나타낸다.
from people;
 
select birthdt, date_add(birthdt, interval 100 day)   -- 100일을 더해서 나타낸다.
from people;
 
select birthdt, birthdt + interval 100 day  -- 100일을 더함. 위와 같다.
from people;
 
select birthdt, birthdt + interval 15 month + interval 10 day  -- 계속 붙일수 있다.
from people;

위와 같이 여러가지의 연산이 가능하다.

 

레퍼런스를 보면 더 정확하게 알 수 있다.

 

 

이번에는 실무에서 자주 쓰이는 timestamp 를 이용하여 본다.

 

먼저 테이블을 만들고 데이터를 추가한다.

create table comments3 ( content varchar(100), created_at timestamp default now(), 
                         changed_at timestamp default now() on update now() );
 
insert into comments3 (content)
       values ('야 진짜 재밌는 기사네');
       
insert into comments3 (content)
       values ('이 물건은 가격이 비싸네'), ('정말 재미없는 영화를 봤다.');
       
select *
from comments3;

comments3 라는 테이블을 만들고 content 컬럼을 만들었다. created_at , changed_at 컬럼도 만들었는데 그 두 컬럼에는 timestamp가 쓰였다. 

(timestamp값은 1970년01월01일 00:00:00 이후부터 현재까지 지난기간을 초를 환산하여 표현한 값이다.)

(위에서는 timestamp의 default 값을 조정하였다.)

 

먼저,  created_at 컬럼에 있는 timestamp default now()  는 . 데이터가 추가 될 시의 년월일시분초 데이터를 자동으로 created_at 컬럼에 넣겠다는 말이다.  이렇게되면 데이터 추가시 따로 created_at 컬럼의 데이터는 추가 하지 않아도 자동 생성된다.

changed_at 컬럼에도 timestamp default now() on update now()   라고 되어 있다. 

timestamp default now() 이 부분까지는 앞의 created_at과 같다. 이 컬럼 역시 데이터 생성시의 년월일시분초로 이루어진 데이터가 자동 생성되어 컬럼의 데이터로 들어가게 된다. 그렇지만 다른 점은

on update now() 이다.   컬럼의 데이터는 수정이 필요할시 수정을 하게 된다. 이때  

timestamp default now() on update now() 쓰였다면, 데이터가 들어왓을 시의 시간데이터가 수정했을 당시의 시간 데이터로 바뀌어 저장 된다.

 

화면으로 보면,    먼저 위의 실행 화면은,

이렇게 두개의 컬럼에 content 데이터가 추가 된 시간 정보가 데이터로 추가 되어 입력되었다.

 

여기서 데이터를 

 

update comments3 set content = '아 재미있다';      

 

로 업데이트하고 실행화면을 보면

 

아래와 같이 content 데이터가 바뀌고,

 

changed_at 컬럼의 데이터도 바뀐 것을 볼 수 있다.

 

이런 방법을 이용하여 timestamp 와 on update 는 많이 쓰이고 있다.