[PostgreSQL] Timestamp 시간 쿼리 다루기

실무 사건

저는 실무에서 postgresql 을 사용 중입니다.

업무 중에 시간에 따른 Log를 확인해야하는 일이 생겼습니다.

Database를 통해 Data를 확인해야하는 작업이었습니다.

이에 따라, 특정 시간에 해당하는 Data를 조회하는 Query를 생성했습니다.

 

이번 포스팅은 위 사건에서 사용한 timestamp 타입에 대해서 알아보려고 합니다.

 

TIMESTAMP

날짜 & 시간 값을 저장하는 데이터 타입, 8 byte 값입니다.
표현 범위는 BC.4713~AD.294276 년입니다.

 

데이터 확인을 위한 예시 데이터

우선, 예시 데이터를 위해 TABLE을 생성해줍니다.

CREATE TABLE data_log (
	code varchar(10),
	error boolean,
	req_timestamp timestamp
);

code : 거래 코드
error : 오류 여부
req_timestamp : 요청 시간

예시 데이터를 해당 TABLE에 생성해줍니다.

INSERT INTO DATA_LOG
VALUES
	('AA11', 'F', '2022-06-01 18:00:00'),
	('BB22', 'T', '2022-06-10 19:00:00'),
	('CC33', 'F', '2022-06-20 20:00:00');

생성된 데이터를 확인해줍니다.

SELECT * FROM DATA_LOG

 

 

위 데이터를 시간에 따른 Data를 확인할 수 있는 Query를 생성하여 출력된 결과를 확인해줍니다.

 

특정 시간 이전에 발생한 데이터 조회

SELECT *
FROM   data_log
WHERE  req_timestamp < To_timestamp('20220615 10:00:00', 'YYYYMMDD HH24:MI:SS')

 

SELECT *
FROM   data_log
WHERE  req_timestamp < To_timestamp('20220609', 'YYYYMMDD')

 

특정 시간 이후에 발생한 데이터 조회

SELECT *
FROM   data_log
WHERE  req_timestamp > To_timestamp('20220615 10:00:00', 'YYYYMMDD HH24:MI:SS')

SELECT *
FROM   data_log
WHERE  req_timestamp > To_timestamp('202205', 'YYYYMM')

 

특정 일에만 해당하는 데이터 조회

SELECT *
FROM   data_log
WHERE  req_timestamp :: DATE > To_timestamp('20220610', 'YYYYMMDD')

 

 

PostgreSQL에서 사용하는 TIMESTAMP 타입

Pattern	Description
HH	hour of day (01-12)
HH12	hour of day (01-12)
HH24	hour of day (00-23)
MI	minute (00-59)
SS	second (00-59)
MS	millisecond (000-999)
US	microsecond (000000-999999)
SSSS	seconds past midnight (0-86399)
AM, am, PM or pm	meridiem indicator (without periods)
A.M., a.m., P.M. or p.m.	meridiem indicator (with periods)
Y,YYY	year (4 or more digits) with comma
YYYY	year (4 or more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week-numbering year (4 or more digits)
IYY	last 3 digits of ISO 8601 week-numbering year
IY	last 2 digits of ISO 8601 week-numbering year
I	last digit of ISO 8601 week-numbering year
BC, bc, AD or ad	era indicator (without periods)
B.C., b.c., A.D. or a.d.	era indicator (with periods)
MONTH	full upper case month name (blank-padded to 9 chars)
Month	full capitalized month name (blank-padded to 9 chars)
month	full lower case month name (blank-padded to 9 chars)
MON	abbreviated upper case month name (3 chars in English, localized lengths vary)
Mon	abbreviated capitalized month name (3 chars in English, localized lengths vary)
mon	abbreviated lower case month name (3 chars in English, localized lengths vary)
MM	month number (01-12)
DAY	full upper case day name (blank-padded to 9 chars)
Day	full capitalized day name (blank-padded to 9 chars)
day	full lower case day name (blank-padded to 9 chars)
DY	abbreviated upper case day name (3 chars in English, localized lengths vary)
Dy	abbreviated capitalized day name (3 chars in English, localized lengths vary)
dy	abbreviated lower case day name (3 chars in English, localized lengths vary)
DDD	day of year (001-366)
IDDD	day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DD	day of month (01-31)
D	day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	week of month (1-5) (the first week starts on the first day of the month)
WW	week number of year (1-53) (the first week starts on the first day of the year)
IW	week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CC	century (2 digits) (the twenty-first century starts on 2001-01-01)
J	Julian Date (integer days since November 24, 4714 BC at local midnight; see Section B.7)
Q	quarter
RM	month in upper case Roman numerals (I-XII; I=January)
rm	month in lower case Roman numerals (i-xii; i=January)
TZ	upper case time-zone abbreviation (only supported in to_char)
tz	lower case time-zone abbreviation (only supported in to_char)
TZH	time-zone hours
TZM	time-zone minutes
OF	time-zone offset from UTC (only supported in to_char)

 

- 참조 -

http://www.gisdeveloper.co.kr/?p=11622

https://www.postgresql.org/docs/12/functions-formatting.html