SQL 기본 3. 시간 정보와 추출(Date, Timestamp, Time, Interval)

SQL에서는 크게 네 가지의 시간과 관련한 타입이 존재한다.

(정확히는 현재 필자가 공부하고 있는 PostgreSQL을 기준으로)

 

1. Date : '일자'의 의미로 년,월,일의 정보만 가진다.

타입은 'YYYY-MM-DD'의 방식

문자열을 Date 형식으로 반환해주는 to_date

to_date('2022-12-20','yyyy-mm-dd')

상기 '2022-12-20'는 문자열 정보이다. 날짜에 대한 정보는 하나도 가지고 있기 않기에 이를 to_date 함수를 이용하여 Date 타입으로 반환시켜주어야 올바른 사용이 가능하다.

 

반대로 Date 타입을 문자열로 변환하고 싶을 때는 to_char 함수를 사용한다.

to_char(saldate, 'yyyy-mm-dd') -- saldate의 타입은 Date인 상황

 

2. Timestamp : 기본적인 '일자'에 시간 정보까지 포함되어있음

타입은 'YYYY-MM-DD HH24:MI:SS'

문자열을 Timestamp 형식으로 반환해주는 to_timestamp

to_timestamp('2022-12-20','yyyy-mm-dd')

상기 코드를 실행시킨다면 시분초가 모두 포함되지만 0으로 초기화된 '2022-12-20 00:00:00.000 +0900'이 반환되는 것을 알 수 있다.

 

이를 시/분/초까지 포함시켜 표현하고 싶다면 아래와 같은 코드를 이용하여 반환하도록 한다.

to_timestamp('2022-12-20 19:47:12', 'yyyy-mm-dd hh24:mi:ss')

상기 코드를 실행시킨다면 시분초가 모두 포함된 '2022-12-20 19:47:12.000 +0900' 가 반환되는 것을 알 수 있다.

 

3. Time : 오직 시간 정보만을 가짐

타입은 'HH24:MI:SS'

 

4. Interval : 기간을 의미. 전체적으로 시작과 끝까지 몇 일/몇 시간 등이 걸렸는지에 대한 정보가 필요할 때 사용

기본적으로 Date 타입에 덧셈/뺄셈 연산을 하게 되면 해당하는 일자를 더하거나 빼서 출력된다.

select to_date('2022-12-20', 'yyyy-mm-dd') + 2 -- 2022-12-22 출력

 

하지만 Timestamp 타입에 숫자의 연산이 들어가면 오류가 발생

select to_timestamp('2022-12-20 20:35:44', 'yyyy-mm-dd hh24:mi:ss') + 7
-- Error

 

이를 수행하기 위해서는 interval 키워드가 필요하다

select to_timestamp('2022-12-20 20:35:44', 'yyyy-mm-dd hh24:mi:ss') + interval '7 hour'
-- 7시간 뒤를 출력함

 

만약 interval을 구하기 위해 date 간 뺄셈을 진행하게 되면 정수형이 반환된다.(날짜만을 알려주기에 정수형.)

- Date 타입간 덧셈은 허용하지 않는다!

만약 interval을 구하기 위해 Timestamp 간 뺄셈을 진행하게 되면 interval 타입이 반환된다.

 

interval을 년,월,일로 표기하는 방법(justify_interval과 age의 차이)

interval을 자동으로 년/월/일로 표기하기 위해 justify_interval과 age 함수가 존재한다.

이 둘의 차이로써 age는 계산 중 현재의 시간을 사용한다는 점과 월을 제대로 연산한다는 점,

justify_interval은 한 달을 30일로 두고 계산을 진행한다는 점에서 그 차이가 있다.

 

아래는 편리하게 Date, Timestamp, Text 간 형변환하는 꿀팁(PostgreSQL에서 제공하는 형변환의 또다른 방법)

 

1) Date를 Timestamp 형으로 변환하고싶을 때

select to_date('2022-12-20', 'yyyy-mm-dd')::timestamp;

 

2) Timestamp를 Text로 변환하고 싶을 때

select to_timestamp('2022-12-20', 'yyyy-mm-dd')::text;

 

3) Timestamp를 Date로 변환하고 싶을 때

select to_timestamp('2022-12-20 20:08:45', 'yyyy-mm-dd hh24:mi:ss')::date

 

상기 표현처럼 ::date/timestamp/text를 이용하여 유연한 형변환이 가능하다

 

5. extract와 date_part를 이용한 시간 정보 추출

1) extract 키워드 사용

select a.*
	,extract(year from saldate) as year
	,extract(month from saldate) as month
	,extract(day from saldate) as day
from a_table a;

date_part에 비해서 문법이 유연한 느낌을 가진다.(SQL 적으로는 함수의 흐름과는 동떨어진 느낌을 가짐)

 

2) date_part 키워드 사용

select a.*
	,date_part('year', saldate) as year
	,date_part('month', saldate) as month
	,date_part('day', saldate) as day
from a_table a;

둘 중에 무엇을 사용해도 무방하나, 문법적인 느낌을 살리기 위해서는 date_part를 사용하는 것을 권장하고 있음

키워드에는 year, month, day(s), hour(s), minute(s), second(s)가 들어갈 수 있음

 

6. 시간 절삭을 해주는 date_trunc()

원래 trunc라는 함수도 존재한다 : 소수점을 절삭해주는 함수

select trunc(12.2121, 2); -- 결과로 12.21이 출력

 

date_trunc() : 인자로 들어온 날을 기준으로 주어진 날짜를 절삭

date라고 적혀있어 Date 타입만 사용할 수 있는 것처럼 보이지만, timestamp 타입에서도 사용할 수 있다.

select date_trunc('day', '2022-12-20');
-- Date 타입을 인자로 받아도 반환은 Timestamp

 

사용할 수 있는 인자로는 'day','month','week','year' 등을 사용할 수 있으며, 절삭을 시행할 경우 절삭되는 값의 모든 값은 첫 번째 값으로 세팅된다.

select date_trunc('month', '2022-12-21'::date)::date;
-- 2022-12-01이 출력

select date_trunc('year', '2022-12-21'::date)::date;
-- 2022-01-01이 출력

 

 

여기서 date_trunc의 특징 한 가지!

💡 Date 타입을 인자로 받은 date_trunc여도 반환값은 무조건 Timestamp로 반환된다.

 

만약 인자로 Week를 받았을 경우, 해당 날짜의 첫번째 날(월요일)에 해당하는 날짜를 반환한다.

select date_trunc('week', '2022-12-21'::date)::date;
-- 2022-12-19이 출력. 해당 주의 월요일은 12월 19일이므로

 

이를 응용하여 해당 주의 마지막 날(일요일)에 해당한다면 interval을 같이 사용하면 된다.

select date_trunc('week', '2022-12-21'::date) + interval '6 days'::date;
-- 2022-12-25이 출력.

 

date_trunc는 group by와 같이 특정 일/월/년을 묶고 싶을 때 주로 사용하는 함수이다.

 

 

아래는 기타 날짜, 시간을 표기하는 포맷팅 패턴

포맷팅 패턴	내용
hh24	하루 중 시간(24시각 표기법)
hh12	하루 중 시간(12시각 표기법)
mi	분(00-59)
ss	초(00-59)
yyyy	년도
mm	월(1-12)
dd	일(월중 해당일)
month/MONTH	월 이름/대문자 출력
day	요일 이름
w	월의 해당 주(1-5)
ww	1년의 해당 주(1-52)
d	요일. 일요일은 1, 토요일은 7로 표기
am/pm	am/pm의 표기(하나만 써도 작동)
tz	시간대

 

---

 

모든 시간 반환 함수는 어떠한 방식으로 저장이 되어있는지 포맷팅을 추가적으로 해주어야 한다.

해당 형식으로 반환하라는 소리가 아니다!

-- Error code
to_date('2022-12-20, 'yyyymmdd') -- 해당 형태로 반환하라는 소리가 아님. Error 출력

 

추가적으로 postgreSQL 내 타입을 알고 싶을 경우에는 pg_typeof함수를 사용한다.

select pg_typeof(to_date('2022-12-20', 'yyyy-mm-dd');
-- 상기 코드 실행 시 date가 출력되는 것을 확인할 수 있다.

 

now() 함수를 사용하면 현재의 시간이 Timestamp 형태로 반환,

current_timestamp와 같다고 할 수 있다.

이외에도 현재 시간을 Date 형태로 반환하는 current_date와 시간만을 반환하는 current_time이 있다.

select now(), current_timestamp, current_date, current_time;