[PostgreSQL] TimeStamp, Date 데이터 타입 사용법

식빵·2022년 7월 17일

postgresql-memo

목록 보기

4/34

postgresql 에서 timestamp & date 를 다루는 방법을 기록, 공유하는 글입니다.
먼저 예제 테이블을 생성하는 것으로 시작하겠다.

🥝 Table 생성

create table emp (
	empno serial,
	ename varchar(10) null,
	hiredate date null,
	constraint emp_pk primary key (empno)
);

insert into emp (ename, hiredate) 
values ('이순재', to_date('1998-01-01', 'yyyy-mm-dd'));

insert into emp (ename, hiredate) 
values ('박나래', to_date('2000-04-12', 'yyyy-mm-dd'));

insert into emp (ename, hiredate) 
values ('이정재', to_date('2002-12-31', 'yyyy-mm-dd'));

select * from emp;

🕒 참고: timezone

일단 시작하기 앞서서 자신이 접속한 Database(또는 접속한 Session)의 timezone 을 확인합니다. 만약 timezone 이 Asia/Seoul 이 아니면 수정할 필요가 있습니다.

show timezone; -- 만약 UTC 라는 문구가 보이면, timezone 세팅이 안된 상태입니다!

-- UTC 라는 문구가 보이면 일단 아래쿼리를 돌려보세요.
-- 아마 현재 보고 계신 시간(한국 기준)에서 -9:00 시간의 차이가 보일 겁니다.
select now();

-- 그러면 timezone 세팅을 해보죠.
-- 방법1: 하나의 세션에 대해서만 적용하는 법
set timezone TO 'Asia/Seoul'; 

-- 방법2: 접속하려는 Database 의 default timezone 세팅법
--       세션 재접속해야 필요합니다.
-- ALTER DATABASE 데이터베이스_명 SET timezone TO 'Asia/Seoul';


-- 방법3: 하나의 계정에 대한 default timezone 세팅
-- ALTER USER 계정_명 SET timezone='Asia/Seoul' ;

-- (참고) 선택할 수 있는 timezone 은 아래 쿼리를 돌려서 확인할 수 있다.
SELECT * FROM pg_timezone_names;

-- 방법1, 방법2 무엇을 하든간에 이후에는 select now() 를 실행했을 때,
-- 여러분 컴퓨터의 시계에 표기된 시간과 쿼리 결과 시간과 동일한 시간이 보이면 제대로 timezone 이 세팅된 겁니다.

timezone 세팅 관련 참고(1)
timezone 세팅 관련 참고(2)

🥝 문자열 🠮 Date/Timestamp

select to_date('2001-01-02', 'yyyy-mm-dd');

select to_timestamp('2022-01-10', 'yyyy-mm-dd');

select to_timestamp('2022-05-11 16:30:11', 'yyyy-mm-dd hh24:mi:ss'); 

-- 참고로 Date 와 Timestamp 는 서로 변환이 가능하다!
-- select pg_typeof(~) 로 타입을 확인할 수 있다.
select to_date('1992-05-15', 'yyyy-mm-dd')::timestamp;
select to_timestamp('2011-12-31 20:52:55', 'yyyy-mm-dd hh24:mi:ss')::date;

혹시 to_timestamp 에 사용되는 문자열에 대해 더 알고 싶다면 이 링크를 따라가세요.

🥝 Date/Timestamp 🠮 문자열

with
temp_01 as (
	select *, to_char(hiredate, 'yyyy/mm/dd') as hiredate_string 
	from emp
)
select 
	to_char(hiredate, 'yyyy-mm-dd'),
	to_char(hiredate, 'yyyy/mm/dd hh24:mi:ss'),
--	to_char(hiredate, 'month dd yyyy'),
--	to_char(hiredate, 'Month dd yyyy'),
--	to_char(hiredate, 'MONTH dd yyyy'),
--	to_char(hiredate, 'yyyy month day'),
	to_char(hiredate, 'MONTH w d'),
	to_char(hiredate, 'MONTH Day'),
	to_char(hiredate, 'yyyy/mm/dd PM hh12:mi:ss') -- PM 이든, AM 이든 상관없다.
from temp_01
;

🥝 년/월/일/시/분/초 추출하기 (Extract)

select a.*,
	extract(year from hiredate) as year,
	extract(month from hiredate) as month,
	extract(day from hiredate) as day
from emp a;

select a.*,
	date_part('year', hiredate) as year,
	date_part('month', hiredate) as month,
	date_part('day', hiredate) as day
from emp a;


select
	date_part('year', '2013-02-12 15:20:31'::timestamp) as year,
	date_part('month', '2013-02-12 15:20:31'::timestamp) as month,
	date_part('day', '2013-02-12 15:20:31'::timestamp) as day,
	date_part('hour', '2013-02-12 15:20:31'::timestamp) as hour,
	date_part('minute', '2013-02-12 15:20:31'::timestamp) as minute,
	date_part('second', '2013-02-12 15:20:31'::timestamp) as second
;

🥝 연산자 사용

date, timestamp 에는 +, - 같은 연산자를 사용할 수 있다.
하지만 몇가지 주의할 점이 있다. 해당 주의점은 아래 쿼리와 함께 주석으로 남겼다.

select to_date('2022-01-01', 'yyyy-mm-dd') + 2;

select to_timestamp('2022-10-10 14:20:35', 'yyyy-mm-dd hh24:mi:ss');

-- ERROR! TIMEZONE + integer 는 안된다.
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') + 7; 

-- hours, hour 둘다 됨
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') 
			+ interval '2 hours'; -- '2 hours'::interval 도 가능.

-- minutess, minute 둘다 됨
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') 
			+ interval '2 minutes'; -- '2 minutes'::interval 도 가능.

-- seconds, second 둘다 됨
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss')
			+ interval '2 seconds'; -- '2 seconds'::interval 도 가능.

-- 한방에 아래처럼 연산할 수도 있다!
select to_timestamp('2020-10-10 10:10:10', 'yyyy-mm-dd hh24:mi:ss') 
	+ interval '2 years'
	+ interval '2 months'
	+ interval '2 days'
	+ interval '2 hours'
	+ interval '2 minutes'
	+ interval '2 seconds'
	-- 모두 '2 시간표현'::interval 연산 가능!
;


-- 만약에 mybatis 같은 동적인 쿼리를 생성하는 프레임워크를 사용한다면 
-- 아래같이 쓰는걸 권장한다. 다만 이 방식은 복수형 표현이 아닌 
-- 단수형 표현만 사용해야 한다는 점을 주의하자.

-- years X , year O
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') 
			+ interval '2' year;



-- months X , month O
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') 
			+ interval '2' month; 

-- days X , day O
select to_timestamp('2022-10-10 15:15:15', 'yyyy-mm-dd hh24:mi:ss') 
			+ interval '2' day;

-- 마찬가지로 이 방식도 한방에 여러 연산이 가능하다!
select to_timestamp('2020-10-10 10:10:10', 'yyyy-mm-dd hh24:mi:ss') 
	+ interval '1' year
	+ interval '2' month
	+ interval '2' day
	+ interval '2' hour
	+ interval '2' minute
	+ interval '2' second;
    
-- 위의 연산은 '1', '2' 처럼 문자열 형태로 값을 줘야만 가능하다.
-- 문자열이 아니라 숫자를 interval 로 주고 싶다면 어떻게 할까?
-- 방법(1):
select to_timestamp('2020-10-10 10:10:10', 'yyyy-mm-dd hh24:mi:ss') 
	+ (20 || ' minutes')::interval;

-- 방법(2):
select to_timestamp('2020-10-10 10:10:10', 'yyyy-mm-dd hh24:mi:ss') 
 	+ (20 * interval '1 minute')

🥝 연산자 사용 주의사항

-- 연산이 되기는 된다.
select to_date('2001-01-01', 'yyyy-mm-dd') + interval '2 days';

-- 하지만 연산 결과는 Date가 아닌 Timestamp 가 된다.
select pg_typeof(to_date('2001-01-01', 'yyyy-mm-dd') + interval '2 days');


-- 서로 다른 Date 를 빼면 integer 타입으로 차이나는 일수가 출력된다.
select to_date('2022-01-03', 'yyyy-mm-dd') 
			- to_date('2022-01-03', 'yyyy-mm-dd');
            
select pg_typeof(
	to_date('2022-01-03', 'yyyy-mm-dd') - to_date('2022-01-04', 'yyyy-mm-dd')
);

-- Date 와 달리 Timestamp 는 서로 빼면 interval 이 나온다!
select to_timestamp('2023-02-23 13:11:12', 'yyyy-mm-dd hh24:mi:ss')
- to_timestamp('2022-01-20 13:15:50', 'yyyy-mm-dd hh24:mi:ss');

-- 출력결과: 398 days 23:55:22  ==> dbeaver 기준으로 interval 은 이런식으로 출력된다.

-- 타입 interval을 확인!
select pg_typeof(to_timestamp('2022-01-21 13:11:12', 'yyyy-mm-dd hh24:mi:ss')
- to_timestamp('2022-01-20 13:10:12', 'yyyy-mm-dd hh24:mi:ss'));



-- 덧셈은 지원하지 않는다!!  -- ERROR 발생!
select to_date('2022-01-03', 'yyyy-mm-dd') +  to_date('2022-01-01', 'yyyy-mm-dd');

🥝 interval에서 year/month/day 를 빼오기

-- interval 에서 총 일수를 구하려면 아래처럼 하면 된다.
select to_timestamp('2023-02-23 13:11:12', 'yyyy-mm-dd hh24:mi:ss')
- to_timestamp('2022-01-20 13:15:50', 'yyyy-mm-dd hh24:mi:ss');


-- 하지만 위처럼 일수로만 뽑는 게 아니라 ?년, ?개월, ?일 로 뽑아내고 싶다면
-- justify_interval 을 사용해서 year, month, day 를 추출해야 한다.
-- 아래처럼 쓸 수 있다.
with 
tt as (
select empno, ename, hiredate, now(), current_date
	, date_trunc('second', now()) as now_trunc
	, now() - hiredate as "일한 기간"
from emp
)
select *
	, date_part('year', "일한 기간")
	, justify_interval("일한 기간")
	, date_part('year', justify_interval("일한 기간"))||'년 '
    	||date_part('month', justify_interval("일한 기간"))||'월' as 근속년월
from tt;

🥝 date_trunc 로 시간 잘라내기

select date_trunc('day', '2022-10-22 15:32:56'::timestamp);
-- 출력: 2022-10-22 00:00:00.000


select date_trunc('day', to_date('2022-10-10', 'yyyy-mm-dd')); 
-- 출력 : 2022-10-10 00:00:00.000 +0900 
-- timestamp 형으로 타입 변형이 일어난다!
-- 다시 date 로 변경하고 싶으면 아래처럼 한다.
-- select date_trunc('day', to_date('2022-10-10', 'yyyy-mm-dd'))::date;

select date_trunc('month', '2022-04-11'::date)::date;

-- 달의 마지막 날짜 구하기
select (date_trunc('month', current_date) 
	+ interval '1 month' - interval '1 day')::date;

-- 시간, 분, 초 모두 잘라내기 가능!
select date_trunc('hour', now());