PostgreSQL | 날짜/시간 함수 정리 (+ DataLemur 문제풀이)

mySQL 과 postgreSQL RDB는 날짜/시간 처리에 사용하는 함수가 다르다. 코테 준비를 하면서 다양한 환경에서 쿼리를 작성 할수 있어야하기 때문에, 주로 사용하는 날짜/시간 함수를 정리했다.

---

➡️ PostgreSQL 날짜 타입 

데이터 타입	설명	예제
DATE	년-월-일	'2004-10-19'  or '10/19/2004'
TIMESTAMP	년-월-일-시-분-초	'2004-10-19 10:23:54' or '10/19/2004 10:23:54'
** INTERVAL ' A'	시간 간격	INTERVAL '1 day' ,interval '2 hours', interval '1 second' 등

• postgreSQL 은 mysql 의 DATETIME 유형은 없음 

➡️ DATE / TIMESTAMP 타입 연산하기

-- 1. date & 정수 연산  : date +- integer  → date
SELECT date '2001-09-28' + 7
--  출력 : 2001-10-05

-- 2.date & Inteval 연산: date +- interval→timestamp
select date '2001-09-28' + interval '1 hour'
-- 출력 : 2001-09-28 01:00:00

-- 3. ** date & date 연산: date - date → integer 
select date date '2001-10-01' - date '2001-09-28'
-- 출력 : 3 (경과 일수 구하기)

-- 1. timestamp 연산  : timestamp +- interval  → timestamp
SELECT timestamp '2001-09-28 01:00' + interval '23 hours'
--  출력 : 2001-09-29 00:00:00

-- 2. **timestamp 간격을 interval 로 반환 : timestamp - timestamp → interval 
select timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'
-- 출력 : 63 days 15:00:00 (경과 일/시 구해짐)

➡️ DATE/TIMESTAMP 타입 경과 기간 정수로 반환하기 

• 날짜 사이의 간격/ 경과 일수 정수로 반환하려면
  • date 타입 이라면 단순연산: date - date → integer 
  • timestamp 타입 이라면 연산 & 추가 함수 사용: timestamp - timestamp → interval 이므로  

•  timestamp 타입 간의 기간을 정수로 반환하는 법 

-- 1. EXTRACT 함수 활용
-- post_date = timestamp type
SELECT EXTRACT(DAY FROM MAX(post_date)- MIN(post_date))

-- 2. 타입캐스트 로 DATE로 변환 후 연산 
SELCET MAX(post_date)::DATE-MIN(post_date)::DATE

➡️ TYPE CAST (::) 개념  

• 특정 데이터 타입으로 변환할때 사용 

  데이터타입	설명	예제
  DATE	날짜 형식 (YYYY-MM-DD)	SELECT '2025-03-07'::DATE;
  TIME	시간 형식 (HH:MI:SS)	SELECT '12:30:45'::TIME;
  TIMESTAMP	날짜 + 시간	SELECT '2025-03-07 12:30:45'::TIMESTAMP;
  TIMESTAMPTZ	날짜 + 시간 + 타임존	SELECT '2025-03-07 12:30:45+09'::TIMESTAMPTZ;
  INTERVAL	시간 차이 (ex. '1 day', '2 hours')	SELECT '3 days'::INTERVAL;

➡️ 날짜 관련 함수(EXTRACT / DATE_PART / DATE_TRUC)

•  날짜 컬럼의 특정 부분 추출하기
  • EXTRACT (단위 FROM 날짜컬럼) 
  • DATE_PART ( '단위', 컬럼)
  • 두 함수의 차이 : EXTRACT 는 정수로 반환 , DATE_PART 는 소수 포함 반환

-- 1.날짜 컬럼 특정부분 추출 : EXTRACT(단위 FROM 컬럼)
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
결과:2

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 결과:16
 
-- 1-1. INTERVAL 추출시 아래와 같음 
SELECT EXTRACT(MONTH FROM INTERVAL '2년 13개월');
결과:1
 
 -- 2.날짜 컬럼 특정부분 추출 : DATE_PART('단위',컬럼)
 SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 결과:16
 
 -- 3. 설정한 단위까지 자르기 : date_truc('단위',컬럼) 
date_trunc('hour', timestamp '2001-02-16 20:38:40')
결과 : 2001-02-16 20:00:00

➡️ 날짜에서 특정 값 추출하기 (DATE_TRUC / TO_CHAR)

EX) timestamp = '2022-03-05' 에서 년-월만 추출해서 필터링 하고 싶다면

• DATE_TRUC
  • DATE_TRUNC('month', timestamp) -> timestamp 로 반환됨 (YYYY-MM-DD HH:MM:SS)
  • DATE_TRUNC('month', timestamp) -> 2022-03-01 00:00:00

• TO_CHAR 
  •  TO_CHAR( timestamp , 'YYYY-MM' ) -> STRING 으로 반환 (YYYY-MM)
  • TO_CHAR( timestamp , 'YYYY-MM' )  -> 2022-03

📌 MySQL과 PostgreSQL 비교

	MYSQL	POSTGRESQL
포맷 함수	DATE_FORMAT()	TO_CHAR()
사용 예제	SELECT DATE_FORMAT(NOW(), '%Y-%m');	SELECT TO_CHAR(NOW(), 'YYYY-MM');
포맷 문자열	%Y-%m	YYYY-MM
출력 결과 (예시)	2024-03	2024-03

 

☑️ Average Post Hiatus (Part 1)

[문제]

---

[문제 풀이]

내 코드

SELECT user_id
      ,(MAX(post_date)::DATE-MIN(post_date)::DATE) AS days_between
FROM posts
WHERE EXTRACT(YEAR FROM post_date) = 2021
GROUP BY user_id
HAVING MAX(post_date) <> MIN(post_date)

 

 

이슈 및 해결 과정

• 문제 상황
  • postgreSQL timestamp 날짜 연산시 `json` 형태로 반환함 (mysql에서는 동일 타입으로 반환) 

SELECT user_id
      ,MAX(post_date)-MIN(post_date)AS days_between
FROM posts
WHERE EXTRACT(YEAR FROM post_date) = 2021
GROUP BY user_id
HAVING MAX(post_date) <> MIN(post_date)

• 이슈 해결 
  • 방법1) DATE 로 타입 변환 후 연산 : `MAX(post_date)::DATE-MIN(post_date)::DATE`
  • 방법2 ) 연산후 DAY 추출 : `DATE_PART( 'DAY',MAX(post_date)-MIN(post_date))`

한줄 포인트

• DATE_FORMAT 유사한 함수는 TO_CHAR 
• DATE_ADD /DATE_SUB 와 유사한건, 연산자 & INTERVAL '단위'
• 날짜에서 특정값만 추출할때는
  • EXTRACT ( 단위 FROM 컬럼)
  • DATE_PART ( '단위', 컬럼)

 

9.9. Date/Time Functions and Operators

Average Post Hiatus | Facebook SQL Interview Question