[PostgreSQL] 날짜/시간 관련 함수 1

# 현재 날짜 조회하기

SELECT NOW(), CURRENT_DATE, CURRENT_TIME ,CURRENT_TIMESTAMP

 

# TIMESTAMP에서 추출하기(EXTRACT, date_part)

## EXTRACT

SELECT
	NOW()
	,EXTRACT(YEAR FROM NOW())	AS YEAR
    ,EXTRACT(MONTH FROM NOW())	AS MONTH
    ,EXTRACT(DAY FROM NOW())	AS DAY
    ,EXTRACT(HOUR FROM NOW())	AS HOUR
    ,EXTRACT(MINUTE FROM NOW())	AS MINUTE
    ,EXTRACT(SECOND FROM NOW())	AS SECOND

## date_part

date_part('field', source)

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

Valid values for field	Description
century	The Century
day	For timestamp values, the day (of the month) field (1–31) for interval values, the number of days
decade	For timestamp values, the day (of the month) field (1–31)  for interval values, the number of days
epoch	For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC for interval values, the total number of seconds in the interval
dow	The day of the week as Sunday (0) to Saturday (6)
doy	The day of the year (1–365/366)
hour	The hour field (0–23)
isodow	The day of the week as Monday (1) to Sunday (7)
isoyear	The ISO 8601 week-numbering year that the date falls in (not applicable to intervals)
julian	The Julian Date corresponding to the date or timestamp (not applicable to intervals)
microseconds	The seconds field, including fractional parts, multiplied by 1 000 000 note that this includes full seconds
millennium	The millennium
milliseconds	The seconds field, including fractional parts, multiplied by 1000 Note that this includes full seconds.
minute	The minutes field (0–59)
month	For timestamp values, the number of the month within the year (1–12) for interval values, the number of months, modulo 12 (0–11)
quarter	The quarter of the year (1–4) that the date is in
second	The seconds field, including any fractional seconds
timezone	The seconds field, including any fractional seconds
timezone_hour	The hour component of the time zone offset
timezone_minute	The minute component of the time zone offset
week	The number of the ISO 8601 week-numbering week of the year.
year	The year field

## date_trunc

date_trunc(field, source [, time_zone ])

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

Valid values for field	description
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

 

# 날짜/시간 Formatting

SELECT 
	TO_CHAR(NOW(), 'YY/MM/DD')
	, TO_CHAR(NOW(), 'MONTH:Month:month:MON:Mon:mon')

## Template Patterns for Date/Time Formatting

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 mor digits) with comma
YYYY	year (4 and mor digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digits of year
IYYY	ISO year (4 and mor 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)
Mon	Abbreviated mixed-case month name (3 chars)
mon	Abbreviated lowercase month name (3 chars)
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)
Dy	Abbreviated mixed-case day name (3 chars)
dy	Abbreviated lowercase day name (3 chars)
DDD	Day of year (001-366)
DD	Day of month (01-31) for TIMESTAMP (For INTERVAL, DD is day of year (001-366) because day of month is undefined.)
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)
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)

 

	FUNCTION	RESULT
현재 날짜	CURRENT_DATE	2022-09-14
현재 시간	CURRENT_TIME	13:23:25
현재 날짜/시간	CURRENT_TIMESTAMP	2022-09-14 13:23:25
현재 날짜/시간	NOW()	2022-09-14 13:23:25
날짜/시간 추출	EXTRACT( field FROM source)

 

공식사이트 참고 

https://postgresql.kr/docs/13/functions-formatting.html

https://www.postgresql.org/docs/current/functions-datetime.html

9.8. Data Type Formatting Functions