[PostgreSQL] 시간 데이터타입 쿼리/함수

● POSTGRESQL에서의 시간 타입은 타 DBMS와 다른 부분이 있어 시간/날짜 데이터타입에 대한 쿼리 및 함수를 따로 정리하여 포스팅합니다.

 

-- 날짜 시간 타입으로 캐스팅 

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')

 

-- 문자열 데이터타입 -> 날짜형으로 변경

to_date( text, format );
SELECT to_date( '20200401', 'YYYYMMDD' );

 

예시)

UPDATE [table_name] SET [column_name] = to_date( concat(year, '-', month, '-01'), 'YYYY-MM-DD');

      - concat 은 , (comma)로 구분된 각 문자열을 합쳐주는 역할을 합니다.

 

[Template Patterns for Data/Time Formatting]

Patterns	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)