Postgresql date_trunc 함수를 통한 날짜별 조회

Postgresql DBMS를 사용하고 있다.

 

id	content	created_at
0	abc	2023-07-25 07:27:17.407684+00
1	abc	2023-07-25 07:47:50.772492+00
2	ㄱㄴㄷ	2023-07-26 09:26:02.968429+00
3	ㄹㅁㅂ	2023-07-27 01:30:09.382792+00

다음과 같은 데이터가 존재하는 상황!

날짜별로 각 데이터가 몆 개 생성되었는지 조회해 달란 요구사항이 있었다.

 

일반적인 상황... 예를 들어 content별로 각 데이터가 몇 개 존재하는지 조회해야 했다면

SELECT content, count(*) FROM TABLE
GROUP BY content

group by를 통해 간단하게 조회할 수 있을 테다.

 

그런데 문제는? 위 테이블의 created_at 속성은 time stamp 타입이기 때문에...

SELECT created_at, count(*) FROM TABLE
GROUP BY created_at

위와 같이 작성할 경우, 모든 column이 조회 돼버리고 말 테다.

(동일한 time stamp가 존재할 순 없으므로)

 

따라서 time stamp에 특정 기준(년, 월, 일 등등)에 맞는 버림 연산을 수행해야 한다!

→ Postgresql에서는 date_trunc란 함수를 통해 가능!

 

date_trunc(text, timestamp)

다음과 같이 동작하며

첫 번째 인자에 버림을 수행할 날짜 text (year, month, day, hour... )

두 번째 인자에 timestamp 데이터를 넘겨주면 된다.

 

따라서 created_at 기준, 날짜별로 각 데이터가 몇 개 생성되었는지 조회하기 위해서는

SELECT date_trunc('day', created_at), count(*) FROM TABLE
GROUP BY date_trunc('day', created_at)

다음과 같이 작성해 줄 수 있겠다.

 

date_trunc	count
2023-07-25 00:00:00+00	2
2023-07-26 00:00:00+00	1
2023-07-27 00:00:00+00	1

원하는 대로, 날짜별로 각 데이터가 몇 개인지 반환된 모습!