요즘들어 PostgreSQL을 사용하는 프로젝트들이 많은데, 적어놓지 않으면 까먹을 것이 분명해서 조금이라도 오래 기억하고, 추후 필요해질 경우 곧바로 활용할 수 있도록 저장을 해놓고자 한다.
우선 다음 구조의 간단한 테이블이 있다고 가정한다.
간단하게 name, reg_date로 이루어진 테이블이다.
해당 테이블에는 아래와 같은 데이터가 입력되어있다. (참고로 오늘 날짜는 2023-05-29이다.)
SELECT에서 월, 연, 일, 요일 등 특정 시간 정보만 뽑아내기
가장 먼저 저장할 쿼리는 reg_date (timestamp)에서 연, 월, 일, 요일, 시, 분, 초 만 추출하는 쿼리이다.
요일을 제외한 모든 쿼리는 date_part 함수를 사용하면 된다.
# 연도 추출 date_part('year', 컬럼명) # 월 추출 date_part('month', 컬럼명) # 일 추출 date_part('day', 컬럼명) # 요일 추출 (1-월요일 / 7-일요일) EXTRACT(ISODOW FROM 컬럼명) # 시 추출 date_part('hour', 컬럼명) # 분 추출 date_part('minute', 컬럼명) # 초 추출 date_part('second', 컬럼명)
위 함수의 경우 timestamp 컬럼에는 잘 사용된다. 하지만 상황에 따라 timestamp가 아닌 varchar로 칼럼이 생성되어 있다면 위 함수는 사용되지 않는다.
# 테스트 용으로 테이블 생성 CREATE TABLE test_tbl_two ( name VARCHAR(255) PRIMARY KEY, reg_date VARCHAR(30) NOT NULL DEFAULT now());
# test_tbl의 데이터를 test_tbl_two에 삽입 INSERT INTO test_tbl_two SELECT * FROM test_tbl;
# date_part, EXTRACT 실행 결과 확인
이럴 때는 CAST 명령어를 사용하면 VARCHAR가 TIMESTAMP로 변환되므로, CAST 명령어를 한 번 통하면 사용할 수 있다.
CAST(칼럼 AS 자료형)
CAST 함수는 굳이 yyyy-MM-dd hh:mm:ss.sss 가 아니더라도 yyyy-MM-dd 등 여러 포맷에서 지원된다.
WHERE 절에서 시간에 따라 조건문 제시하기
이제 SELECT에서 특정 시간 정보만 뽑아내는 것을 마쳤으므로,
WHERE절에서 시간에 따라 조건문을 제시하는 방법을 알아본다.
일단 특정 시간 정보 (월, 요일 등)은 위의 SELECT에서 보았던 date_part와 EXTRACT를 응용하면 된다.
# 2023년 가입자 # 월요일 가입자
그렇다면 어제 가입한 사람, 저번달에 가입한 사람, 작년에 가입한 사람은 어떻게 구분할까?
물론 now()와 함께 date_part를 사용하면 쉽게 사용할 수 있겠지만, 굳이 귀찮게 그럴 필요 없이 postgresql에서는 interval이라는 기능을 제공한다.
사용법은 아래와 같다.
# 어제부터 가입한 사용자 추출 now() - interval '1 day' # 저번달부터 가입한 사용자 추출 now() - interval '1 month' # 작년부터 가입한 사용자 추출 now() - interval '1 year' # 작년 오늘의 전달의 전날부터 가입한 사용자 추출 (조합)
다만 위의 경우 now()가 기준이므로 시간도 now를 기준으로 실행된다.
그러나 왠만해서는 이런 쿼리에서 "어제"란 전일 00:00:00를 기준으로 잡아야 하므로 now() 대신 CURRENT_DATE을 사용하면 된다.
# now() 사용 시 어제 > 24시간 이내 # CURRENT_DATE 사용 시 어제 > 전일 00:00:00 이후
