[PostgreSQL] 날짜/시간 함수 정리

-- 날짜 시간 타입으로 캐스팅 
 
select '20190320'::date, '2019-01-02'::date
 
select date '2019-03-18'
 
select to_date('01022019','MMDDYYYY'), to_date('2018-12-25', 'YYYY-MM-DD')
 
select to_timestamp('2019-01-01 20:12:12', 'YYYY-MM-DD HH:MI:SS')
 
 
 
-- 현재시간 날짜 구하기 
 
select now(), current_date, current_time 
 
 
 
-- 시간 더하고 빼기 
 
select '2019-01-01'::date + interval '2 sec', '2019-01-01'::date - interval '2 sec'
 
select '2019-01-01'::date + interval '2 min', '2019-01-01'::date - interval '2 min'
 
select '2019-01-01'::date + interval '2 hour', '2019-01-01'::date - interval '2 hour'
 
select '2019-01-01'::date + interval '2 day', '2019-01-01'::date - interval '2 day'
 
select '2019-01-01'::date + interval '2 week', '2019-01-01'::date - interval '2 week'
 
select '2019-01-01'::date + interval '2 month', '2019-01-01'::date - interval '2 month'
 
select '2019-01-01'::date + interval '2 year', '2019-01-01'::date - interval '2 year'
 
select ('2019-01-01'::date + interval '2 day') + interval '2 hour'
 
select now() + (600 * interval '1 sec'), now() + interval '600 sec'
 
select now(), now() + interval '15h 2m 12s'
 
 
-- day of year 구하기 
 
select extract(doy from now())
 
 
-- 시간파트 구하기 
 
select to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
 
select to_char(now(), 'MONTH:Month:month:MON:Mon:mon:WW:W:D')
 

[날짜 / 시간 형식화를위한 템플릿 패턴]

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 or A.M. or PM or P.M.	meridian indicator (uppercase)
am or a.m. or pm or p.m.	meridian indicator (lowercase)
Y,YYY	year (4 and more digits) with comma
YYYY	year (4 and more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO year (4 and more digits)
IYY	last 3 digits of ISO year
IY	last 2 digits of ISO year
I	last digits of ISO year
BC or B.C. or AD or A.D.	era indicator (uppercase)
bc or b.c. or ad or a.d.	era indicator (lowercase)
MONTH	full uppercase month name (blank-padded to 9 chars)
Month	full mixed-case month name (blank-padded to 9 chars)
month	full lowercase month name (blank-padded to 9 chars)
MON	abbreviated uppercase month name (3 chars in English, localized lengths vary)
Mon	abbreviated mixed-case month name (3 chars in English, localized lengths vary)
mon	abbreviated lowercase month name (3 chars in English, localized lengths vary)
MM	month number (01-12)
DAY	full uppercase day name (blank-padded to 9 chars)
Day	full mixed-case day name (blank-padded to 9 chars)
day	full lowercase day name (blank-padded to 9 chars)
DY	abbreviated uppercase day name (3 chars in English, localized lengths vary)
Dy	abbreviated mixed-case day name (3 chars in English, localized lengths vary)
dy	abbreviated lowercase day name (3 chars in English, localized lengths vary)
DDD	day of year (001-366)
DD	day of month (01-31)
D	day of week (1-7; Sunday is 1)
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	ISO week number of year (The first Thursday of the new year is in week 1.)
CC	century (2 digits) (The twenty-first century starts on 2001-01-01.)
J	Julian Day (days since January 1, 4712 BC)
Q	quarter
RM	month in Roman numerals (I-XII; I=January) (uppercase)
rm	month in Roman numerals (i-xii; i=January) (lowercase)
TZ	time-zone name (uppercase)
tz	time-zone name (lowercase)

 

ㅁㄴㅇㅁㅇㅁㅇㅁㄴㅇ