[PostgreSQL] PostgreSQL의 Date/Time 타입에 대해서 알아보자.

Date/Time 타입에 대해서 알아보자.

실제로 테이블에 데이터를 쌓을 때 CreatedAt, UpdatedAt, DeletedAt 등과 같이

시간 관련 타입을 사용하는 것은 아주 흔히 볼 수 있다.

 

 

아래 표와 같이 타입을 나눌 수 있다.

 

아래 예시를 통해서 Date/Time을 익혀보자.

NOW()의 기준은 2022년 06년 19일이다.

 

 

1. 시간값 획득

SELECT NOW();

Result

 

 

 

SELECT PG_TYPEOF(NOW());

Result

 

 

 

2. Current 키워드 사용

SELECT CURRENT_TIMESTAMP;

Result

 

 

 

SELECT PG_TYPEOF(CURRENT_TIMESTAMP);

Result

 

 

 

SELECT CURRENT_TIME;

Result

 

 

 

SELECT CURRENT_DATE;

Result

 

 

 

3. 타임 스탬프 <-> 문자열 사용

SELECT TO_CHAR(NOW(), 'YYYYMMDD');

Result

 

 

 

4. 타임 스탬프 <-> Date 변환

SELECT NOW()::DATE;

Result

 

 

 

SELECT (NOW()::DATE)::TIMESTAMP;

Result

 

 

 

SELECT NOW()::TIME;

Result

 

 

 

SELECT NOW(), NOW() - INTERVAL'30 MINUTE';

Result

 

 

 

SELECT NOW() - INTERVAL '10 DAYS';

Result

 

 

 

SELECT NOW() + INTERVAL '10 YEARS';

Result

 

 

 

5. 시간값 추출 (EXTRACT)

SELECT EXTRACT(DAY FROM NOW());

Result

 

 

 

SELECT EXTRACT(YEAR FROM NOW());

Result

 

 

 

6. Time Zone

SHOW TIMEZONE;

Result

 

 

 

많은 종류의 Time Zone이 있지만, 자주 쓰는 것을 나열해본다.

 

(1) UTC : 협정 세계시

(2) GMT : 그리니치 평균시 (UTC와 동일)

(3) KST : 한국 표준시 (UTC + 9)

(4) PST : 태평양 표준시 (UTC -8)

(5) PDT : 태평양 여름시간 (UTC - 9)

 

 

 

** STRING 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)
FF1	tenth of second (0–9)
FF2	hundredth of second (00–99)
FF3	millisecond (000–999)
FF4	tenth of a millisecond (0000–9999)
FF5	hundredth of a millisecond (00000–99999)
FF6	microsecond (000000–999999)
SSSS, SSSSS	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)

 

 

 

 

REFERENCES

PostgreSQL : Documentation: 13: PostgreSQL 13.7 Documentation

[PostgreSQL] 시간값 다루기