SQL로 Date / Time 데이터 다루기 (기초)
SQL로 Date / Time 데이터 다루는 법을 알아본다.
데이터 타입
PostgreSQL
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ), ...
참고 : PostgreSQL - Date/Time Types
MySQL
DATE, DATETIME, TIMESTAMP(시스템 TIME ZONE 포함), ...
참고 : MySQL - Date/Time Types / MYSQL DATETIME VS TIMESTAMP 차이
MySQL의 TIMESTAMP와 PostgreSQL의 TIMESTAMPTZ 비교
MySQL은 ‘YYYY-MM-DD hh:mm:ss’ 데이터를 UTC로 저장 후 return 할 때 TIME ZONE에 따라 return하는 반면,
PostgreSQL은 ‘YYYY-MM-DD hh:mm:ss+09‘와 같은 형태로 직접 TIMEZONE 데이터를 INSERT할 수 있다.
유용한 함수
함수 | MySQL | PostgreSQL |
---|---|---|
오늘 | CURRENT_DATE 또는 CURDATE() | CURRENT_DATE |
현재 날짜+시간(타임존 O) | CURRENT_TIMESTAMP또는 NOW() | CURRENT_TIMESTAMP 또는 NOW() |
현재 날짜+시간(타임존 X) | CURRENT_TIMESTAMP | LOCALTIMESTAMP |
DATE FORMAT 변환
각각의 함수가 존재하며, FORMAT 형식이 다른 것을 주의해야 한다.
MySQL
SELECT DATE_FORMAT("2019-09-09 13:00:00", "%Y-%m-%d");
=> 2019-09-09
MySQL DATE_FORMAT() Function - w3schools
PostgreSQL
-- PostgreSQL에서는 문자열 형식의 날짜를 바로 DATE로 인식하지 못하기 때문에 TYPE을 지정해야 한다.
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로 모두 변환해야 한다. )
참고 : ‘YYYY-MM-DD HH:MM:SS’ 문자열을 DATE 타입 선언없이도 자동으로 인식한다.
-- 더하기
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