SQL로 Date / Time 데이터 다루기 (기초)
SQL로 Date / Time 데이터 다루는 법을 알아본다.
데이터 타입
PostgreSQL
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ), ...
참고 : PostgreSQL - Date/Time Types
MySQL
DATE, DATETIME, TIMESTAMP, ...
MySQL에서는 TIMESTAMP가 TIME ZONE이 포함된 시간을 의미하며,
‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC 까지 가능한 범위가 존재한다.
참고 : MySQL - Date/Time Types / MYSQL DATETIME VS TIMESTAMP 차이
MySQL의 TIMESTAMP와 PostgreSQL의 TIMESTAMPTZ 비교
입력된 TIMESTAMP 데이터를 UTC로 저장 후 TIME ZONE에 따라 변환하는 방식은 같다.
MySQL은 현재 설정된 TIMEZONE을 기준으로 데이터를 INSERT해야 한다.
‘Asia/Seoul’이라면 한국 시간에 맞는 ‘YYYY-mm-dd hh:mm:ss’ 형태로 넣어야 한다.
세션마다 TIMEZONE을 설정하고 그에 맞는 시간으로 데이터를 넣으면 GLOBAL TIMEZONE에 맞게 변환된다.
단, PostgreSQL은 ‘YYYY-MM-DD HH:MI:SS+09‘와 같은 형태로 직접 TIMEZONE이 포함된 데이터를 INSERT할 수 있다.
TIMEZONE에 관계없는 데이터를 사용하려면 MySQL - DATETIME, PostgreSQL - TIMESTAMP 타입을 사용한다.
TIMEZONE 설정
MySQL
-- 현재 타임존 확인
SELECT @@global.time_zone, @@session.time_zone;
-- 타임존 변경
SET TIME_ZONE = 'Asia/Seoul';
SET GLOBAL TIME_ZONE = 'Asia/Seoul'; -- root 권한 유저의 경우,
영구적으로 적용하려면 my.conf에서 수정
PostgreSQL
영구적으로 적용하려면 postgresql.conf에서 수정
-- 현재 타임존 확인
SHOW TIMEZONE;
-- GMT는 UTC와 같다.
-- 타임존 변경 (현재 세션에서만 적용)
SET TIME ZONE 'Asia/Seoul';
참고 : PostgreSQL Set Time Zone - by ObjectRocket
현재 시간 표시
| 함수 | MySQL | PostgreSQL |
|---|---|---|
| 오늘 | CURRENT_DATE 또는 CURDATE() | CURRENT_DATE |
| 현재 날짜+시간 ( 타임존 O ) | CURRENT_TIMESTAMP 또는 NOW() | LOCALTIMESTAMP 또는 [CURRENT_TIMESTAMP / NOW()]::TIMESTAMP |
| 현재 날짜+시간 ( UTC ) | UTC_TIMESTAMP | CURRENT_TIMESTAMP 또는 NOW() |
PostgreSQL에서는 ::TIMESTAMP 없이 CURRENT_TIMESTAMP나 NOW()를 출력하면 UTC로 출력된다.
그러나, 내부적으로는 LOCALTIMESTAMP와 같다고 인식한다.
0. 입출력 형식
둘 다 ‘YYYY-MM-DD HH:MM:SS’ 과 ‘YYYY-MM-DDTHH:MM:SS’ 형식을 지원한다.
MySQL
‘YYYY-MM-DD HH:MM:SS’ 문자열을 DATE 타입 선언없이도 자동으로 인식한다. TIMESTAMP 타입으로 따로 명시하는 방법은 없다.
SELECT DATE "2019-09-09";
-- TIMESTAMP라고 표현하지만(SQL 표준 방식 적용) DATETIME 타입이다.
SELECT TIMESTAMP "2019-09-09 13:00:00";
PostgreSQL
뒤에 ::을 붙이는 방식으로도 타입 표현이 가능하다. (주로 사용되는 패턴)
SELECT "2019-09-09"::DATE;
-- 단위 변환 가능
SELECT "2019-09-09T13:00:00"::DATE;
SELECT '2019-09-09 13:00:00'::TIMESTAMP;
SELECT '2019-09-09 13:00:00'::TIMESTAMPTZ;
DATE FORMAT으로 변환 ( STRING => DATE )
문자열이 갖고 있는 표현방식을 입력해준다. 각각의 함수가 존재하며, FORMAT 형식이 다른 것을 주의해야 한다.
MySQL
SELECT STR_TO_DATE("2020/02/06", "%Y/%m/%d");
=> 2020-02-06
PostgreSQL
SELECT TO_DATE('2020/02/06', 'YYYY/MM/DD');
=> 2020-02-06T00:00:00.000Z
SELECT to_date('10 Feb 2020', 'DD Mon YYYY');
=> 2020-02-10T00:00:00.000Z
DATE 표현 방식 변환 ( DATE => STRING )
MySQL
% + 글자 형식으로 사용
SELECT DATE_FORMAT("2019-09-09 13:00:00", "%Y-%m-%d");
=> 2019-09-09
MySQL DATE_FORMAT() Function - w3schools
PostgreSQL
SELECT to_char('2019-09-09T13:00:00'::timestamp, 'YYYY-MM-DD');
=> 2019-09-09
Data Type Formatting - PostgreSQL
1. DATE / TIME 데이터 연산
날짜 단위에는 INTERVAL을 명시한다.
PostgreSQL
일반적인 연산은 +, - 를 통해 계산한다.
-- 더하기
SELECT DATE '2019-01-01' + INTERVAL '6 month'; -- 날짜 단위에 ' '가 필요
-- 빼기
SELECT TIMESTAMP '2019-01-01 18:00' - INTERVAL '2 hour';
MySQL
MySQL은 함수를 사용해서 계산해야 한다. +, -를 사용할 경우, 잘못된 결과가 나올 수 있기 때문이다.
( +,-로 정확한 계산을 하려면 UNIX_TIMESTAMP로 모두 변환해야 한다. )
-- 더하기
SELECT DATE_ADD('2019-01-01', INTERVAL 6 month); -- 날짜 단위에 ' '가 없음
--SELECT '2019-01-01' + INTERVAL 6 month; 도 가능
-- 빼기
SELECT DATE_SUB('2019-01-01 18:00', INTERVAL 2 hour);
날짜 차이 계산
PostgreSQL
-- 날짜 차이
SELECT CURRENT_DATE - DATE '2019-01-01';
-- 특정 단위 차이
SELECT date_part('day', CURRENT_TIMESTAMP - '2019-01-01 13:00'::TIMESTAMP);
MySQL
-- 날짜 차이 ( 1 - 2)
SELECT DATEDIFF(CURDATE(), DATE('2019-01-01'));
-- 특정 단위 차이 ( 2 - 1 )
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP('2019-01-01 13:00'), CURRENT_TIMESTAMP);
2. 특정 단위(일, 월, …) 추출 또는 통합
참고: week을 계산하는 방식이 달라 주의가 필요하다.
-- PostgreSQL
SELECT EXTRACT(WEEK FROM '2019-07-23'::date);
-- result : 30
-- MySQL
SELECT EXTRACT(WEEK FROM '2019-07-23');
-- result : 29
단순 숫자로 추출하는 법
공통
SELECT EXTRACT(MONTH FROM '2019-01-01 18:00');
-- result : 1
PostgreSQL
EXTRACT는 호환을 위해 가능하고 실제로는 date_part를 통해 실행된다.
SELECT date_part('month', TIMESTAMP '2019-01-01 18:00');
TIMESTAMP 타입을 유지하는 법
PostgreSQL
DATE_TRUNC를 활용할 수 있다.
SELECT DATE_TRUNC('week', '2019-07-23'::date);
-- result : 2019-07-22
MySQL
STR_TO_DATE 함수를 활용하여 구할 수 있다.
-- 주의 : week에 1을 더해준다.
SELECT STR_TO_DATE(CONCAT('2019',EXTRACT (week FROM '2019-07-23')+1,'Monday'), '%x %v %W');
-- result : 2019-07-22