sql, date 타입 다루기

2022-01-29 • infoqoch •

•

sql의 데이터 타입

sql에는 다양한 데이터 타입이 있다.
(사족이지만) varchar와 char의 차이가 인상적이다. varchar는 초기값이 얼마로 정해졌든, 실제 레코드가 들어갈 때의 점유 공간은 실제 입력되는 값의 길이에 따른다. 하지만 char의 경우 레코드가 생성될 때 무조건 그것이 초기값만큼 점유한다.
문자열 이외에 int, date, datetime 등 다양한 데이터 타입이 있다.
더 나아가 인텔리제이는 IDE 차원에서의 보정 기능이 있다. boolean 등을 지원하고 0과 1을 true와 false로 변환해주는 등 편리한 기능을 제공한다.
이번에는 date 타입에 대하여 다루고자 한다.

datetime과 ddl

datetime, date, time을 사용할 수 있다.
문자열을 특별한 타입 변환 없이 삽입할 수 있다.
regDt와 modDt 칼럼의 경우 옵션을 활용하여 생성 시간과 갱신 시간을 자동으로 입력하도록 구현했다.

create table datetest (
	idx int unsigned primary key auto_increment ,
	name varchar(100),
	birthday date,
	birthtime time,
	regdt datetime not null default current_timestamp,
	moddt datetime null on update current_timestamp
) ;

insert into datetest(name, birthday) values ('kim', '2000-01-01');
insert into datetest(name, birthday, regdt) values ('lee', '2005-06-05', now());
insert into datetest(name, birthday, birthtime, regdt) values ('choi', '2001-01-01', '13:00:12', '2002-01-01 00:00:00');

update datetest set birthday = '2000-01-02' where name = 'kim';

select * from datetest;

datetime과 timestamp

datetime과 비교할 때, timestamp는 에포크이며 timezone이 0000 이다.

create table datetest2 (
	dt datetime not null default now(),
	ts timestamp not null default now()
);

insert into datetest2 (dt, ts) values ('2000-01-01 00:00:00', '2000-01-01 00:00:00');

select * from datetest2;

만약 timezone을 변경할 경우 timestamp의 값이 변경됨을 확인할 수 있다.

SET time_zone = '+01:00';
select * from datetest2;

무엇을 사용해야 할까?

2000년에 밀레니엄 버그에 대한 괴담이 존재했다. 89년, 96년으로 사용한 시스템이 (20)00년이 되면 다 붕괴된다는 괴담이다.
실제로 발생 가능한 문제였고, 이를 해결하기 위하여 정부와 기업은 천문학적인 금액을 사용했다.
에포크로 인한 38년 문제가 발생할 예정이다. 새롭게 구축하는 시스템에서 굳이 에포크를 사용할 이유가 없다고 생각한다.
타임존이 필요한 경우 컨버팅 가능하다 : SELECT CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00');

현재시간을 다루는 함수 : now(), sysdate(), CURRENT_TIMESTAMP

now()와 CURRENT_TIMESTAMP 두 개는 동일하다.
now()와 sysdate()는 다음과 같은 차이를 가진다.
- now()는 쿼리 전체가 실행된 순간이다. (NOW() returns a constant time that indicates the time at which the statement began to execute.)
- sysdate()는 해당 함수가 실행된 순간이다. (returns the time at which it executes.)
- now()는 언제나 동일한 값을 반환한다. 성능 또한 함수를 재실행하는 sysdate()보다 좋다. 그러므로 now()가 권장된다.
확실하진 않지만 관행적으로 쿼리에는 now()를 쓰고 DDL에는 CURRENT_TIMESTAMP을 쓰는 것 같다.

시간을 다루는 다양한 방법

아래와 같이 다양한 방식으로 시간을 표현하고 조작할 수 있다.

select 
	timestampdiff(day, '2021-01-01', '2021-02-05')
	, timestampdiff(month, '2021-01-01', '2021-02-05')
	, date_format(now(), '%y-%m-%d')
	, month(now()) 
	, year(now())
	, hour(now())
	, year('2020-01-01')
	, year('2020-1-1')
	, now()
	, sysdate();

같은 날에 주문하였는가?

https://leetcode.com/problems/immediate-food-delivery-i/
고객이 주문을 할 때 선호하는 발송일을 선택할 수 있다.
고객이 주문한 날이 선호하는 날인 경우를 비율로 추출한다.

Input: 
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 5           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-11                  |
| 4           | 3           | 2019-08-24 | 2019-08-26                  |
| 5           | 4           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
+-------------+-------------+------------+-----------------------------+
Output: 
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
Explanation: The orders with delivery id 2 and 3 are immediate while the others are scheduled.

해소

주문한 날과 발송한 날을 비교한다. 그 날의 차이가 0 인 값을 추출한다.

select round(tb1.cnt/tb2.cnt, 4) * 100 immediate_percentage
from (
    select count(1) cnt
    from delivery 
    where timestampdiff(day, order_date, customer_pref_delivery_date) = 0
) tb1
join (
    select count(1) cnt
    from delivery 
) tb2

참고 : https://database.guide/sysdate-vs-now-in-mysql-whats-the-difference