지난번 포스팅에서는 PostgreSQL, Greenplum에서 EXTRACT() 함수를 사용해서 TIMESTAMP, INTERVAL 데이터 유형으로 부터 날짜, 시간 정보를 가져오기(https://rfriend.tistory.com/708) 하는 방법을 소개하였습니다. 

 

이번 포스팅에서는 PostgreSQL, Greenplum 에서 TO_CHAR(expression, format) 함수를 사용하여 TIMESTAMP 데이터를 포맷을 가진 문자열로 바꾸기 (converting a TIMESTAMP data type to a formatted string) 하는 방법을 소개하겠습니다. 

 

TO_CHAR(expression, format) 함수에서 expression 매개변수에는 TIMESTAMP 데이터 유형 뿐만이 아니라 INTERVAL, Integer, Double Precision, Numeric 데이터 유형도 사용할 수 있습니다만, 이번 포스팅에서는 TIMESTAMP 데이터 유형에 한정해서 설명을 하겠습니다. 

 

PostgreSQL, Greenplum, TO_CHAR(expression, format) : converting TIMESTAMP data type to a string

 

 

직관적인 이해를 돕기위한 간단한 예제로서, 아래는 TO_CHAR(expression, format) 함수에서 expression 매개변수로 TIMESTAMP '2021-11-28 21:30:45' 를 입력해주고, format 매개변수로는 'Mon-DD-YY HH12:MI:SS a.m.' (==> 축약된 형태의 첫글자 대문자의 월 이름-날짜-연도는 뒤의 두개년도만, 시간(0~12시):분:초 소문자로 점 포함해서 a.m. 또는 p.m.) 를 설정했을 때 문자열로 변환된 예입니다. 

 

---------------------------------------------------------------------------------
-- The PostgreSQL TO_CHAR(expression, format) function
-- : converts a timestamp data type to a string.
-- [expression] a timestamp that is converted to a string according to a specific format.
--                     or an interval, an integer, a double precision, or a numeric value
-- [format] The format for the result string.
-- ref: https://www.postgresqltutorial.com/postgresql-to_char/
---------------------------------------------------------------------------------

-- converting the timestamp to a string.
SELECT 
	TO_CHAR(
		TIMESTAMP '2021-11-28 21:30:45', -- expression
		'Mon-DD-YY HH12:MI:SS a.m.' -- format
	) AS fomatted_dt;

--fomatted_dt            |
-------------------------+
--Nov-28-21 09:30:45 p.m.|


SELECT 
	TO_CHAR(
		TIMESTAMP '2021-11-28 21:30:45', -- expression
		'YYYY-MM-DD HH24:MI:SS' -- format
	) AS fomatted_dt2;

--fomatted_dt2       |
---------------------+
--2021-11-28 21:30:45|

 

 

 

위의 예처럼 TO_CHAR(expression, format) 의 format 매개변수에 원하는 포맷의 매개변수를 이어서 써주면 되는데요, 각 포맷 매개변수의 아웃풋을 좀더 비교하기 편리하도록 하나씩 떼어서 예를 들어보겠습니다. 

 

먼저 날짜 년(year), 월(month), 일(day) 에 대해서 TO_CHAR(TIMESTAMP '2021-11-28 21:30:45', format) 의 날짜 format 을 아래처럼 설정했을 때의 아웃풋을 비교해보겠습니다. 

 

 - format: 'YYYY-MM-DD' ==> 2021-11-28

 - format: 'dd/mm/yyyy' ==> 28/11/2021

 

-- converting DATE to format strings
SELECT 
	datum AS dt
	-- YYYY: year in 4 digits
	-- MM: month number from 01 to 12
	-- DD: Day of month (01-31)
	, TO_CHAR(datum, 'YYYY-MM-DD') AS date_yyyymmdd
	-- formatted strings as 'dd/mm/yyyy'
	, TO_CHAR(datum, 'dd/mm/yyyy') AS date_ddmmyyyy 
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;

--dt                     |date_yyyymmdd|date_ddmmyyyy|
-------------------------+-------------+-------------+
--2021-11-28 21:30:45.000|2021-11-28   |28/11/2021   |

 

 

 

TIMESTAMP 데이터 유형 '2021-11-28 21:30:45'에서 월(month)을 여러가지 포맷의 문자열로 변환해 보겠습니다. 

(전체 월 이름 vs. 축약형 월 이름, 전체 대문자 vs. 첫글자만 대문자 vs. 전체 소문자)

 

  - format: 'MONTH' ==> NOVEMBER

  - format: 'Month' ==> November

  - format: 'month' ==> november

  - format: 'MON' ==> NOV

  - format: 'Mon' ==> Nov

  - format: 'mon' ==> nov

 

-- converting Month to formatted strings
SELECT 
	datum AS dt
	-- MONTH: English month name in uppercase
	, TO_CHAR(datum, 'MONTH') AS month_upper
	-- Month: Full capitalized English month name
	, TO_CHAR(datum, 'Month') AS month_capital
	-- month: Full lowercase English month name
	, TO_CHAR(datum, 'month') AS month_lower
	-- MON: Abbreviated uppercase month name e.g., JAN, FEB, etc.
	, TO_CHAR(datum, 'MON') AS month_abbr_upper
	-- Mon: Abbreviated capitalized month name e.g, Jan, Feb,  etc.
	, TO_CHAR(datum, 'Mon') AS month_abbr_capital
	-- mon: Abbreviated lowercase month name e.g., jan, feb, etc.
	, TO_CHAR(datum, 'mon') AS month_abbr_lower
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;

--dt                     |month_upper|month_capital|month_lower|month_abbr_upper|month_abbr_capital|month_abbr
-------------------------+-----------+-------------+-----------+----------------+------------------+----------------+
--2021-11-28 21:30:45.000|NOVEMBER   |November     |november   |NOV             |Nov               |nov             |

 

 

 

TIMESTAMP 데이터 유형 '2021-11-28 21:30:45' 에 대해서 '주(week)' 의 format 을 다르게 설정해서 문자열로 변환해보겠습니다. 

 

 - format: 'W' (Week of month, 1~5) ==> 4

 - format: 'WW' (Week number of year, 1~53)==> 48

 - format: 'IW' (Week number of ISO 8601, 01~53) ==> 47

 

-- converting number of week to formatted strings
SELECT 
	datum AS datetime
	-- W:	Week of month (1-5) 
    -- (the first week starts on the first day of the month)
	, TO_CHAR(datum, 'W') AS week_of_month
	-- WW:	Week number of year (1-53) 
    -- (the first week starts on the first day of the year)
	, TO_CHAR(datum, 'WW') AS week_of_year
	-- IW:	Week number of ISO 8601 week-numbering year 
    -- (01-53; the first Thursday of the year is in week 1)
	, TO_CHAR(datum, 'IW') AS week_iso
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;

--datetime               |week_of_month|week_of_year|week_iso|
-------------------------+-------------+------------+--------+
--2021-11-28 21:30:45.000|4            |48          |47      |

 

 

 

TIMESTAMP 데이터 유형의 '2021-11-28 21:30:45' 의 '일(day)'에 대해서 format 을 달리하여 문자열로 변환해 보겠습니다. (전체 vs. 축약, 대문자 vs. 첫글자만 대문자 vs. 소문자)

 

 - format: 'DAY' ==> SUNDAY

 - format: 'Day' ==> Sunday

 - format: 'day' ==> sunday

 - format: 'DY' ==> SUN

 - format: 'Dy' ==> Sun

 - format: 'dy' ==> sun

 

-- converting Day to  formatted strings
SELECT 
	datum AS datetime
	-- DAY: Full uppercase day name
	, TO_CHAR(datum, 'DAY') AS day_upper
	-- Day: Full capitalized day name
	, TO_CHAR(datum, 'Day') AS day_capital
	-- day: Full lowercase day name
	, TO_CHAR(datum, 'day') AS day_lower
	--	DY: Abbreviated uppercase day name
	, TO_CHAR(datum, 'DY') AS day_abbr_upper
	-- Dy: 	Abbreviated capitalized day name
	, TO_CHAR(datum, 'Dy') AS day_abbr_capital
	-- dy:	Abbreviated lowercase day name
	, TO_CHAR(datum, 'dy') AS day_abbr_lower
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;

--datetime               |day_upper|day_capital|day_lower|day_abbr_upper|day_abbr_capital|day_abbr
-------------------------+---------+-----------+---------+--------------+----------------+--------------+
--2021-11-28 21:30:45.000|SUNDAY   |Sunday     |sunday   |SUN           |Sun             |sun           |

 

 

 

TIMESTAMP 데이터 유형의 '2021-11-28 21:30:45' 의 'TIME'에 대해서 format 을 달리하여 문자열로 변환해 보겠습니다. 'HH24'는 0~23시간, 'HH12'는 0~12시간의 포맷으로 시간(hour)을 문자열로 바꿔줍니다. 'HH12' 포맷을 사용할 경우 오전(a.m.)과 오후(p.m.)을 구분할 수 있도록 a.m. meridiem, p.m. meridiem 표기를 추가해줍니다. 

 

 - format: 'HH24:MI:SS' ==> 21:30:45

 - format: 'HH12:MI:SS' ==> 09:30:45

 - format: 'HH12:MI:SS AM' ==> 09:30:45 PM

 - format: 'HH12:MI:SS a.m.' ==> 09:30:45 p.m.

 

-- converting TIME to formatted strings
SELECT 
	datum AS dt
	-- HH24: Hour of Day (0~23)
	-- MI: Minute (0-59)
	-- SS: Second (0-59)
	, TO_CHAR(datum, 'HH24:MI:SS') AS time_h24ms
	-- HH12: Hour of Day (0,12)
	, TO_CHAR(datum, 'HH12:MI:SS') AS time_h12ms
	-- AM, am, PM or pm	Meridiem indicator (without periods, upper case)
	, TO_CHAR(datum, 'HH12:MI:SS AM') AS time_h12ms_ampm
	-- A.M., a.m., P.M. or p.m.	Meridiem indicator (with periods, lower case)
	, TO_CHAR(datum, 'HH12:MI:SS a.m.') AS time_h12ms_ampm_periods
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;

--dt                     |time_h24ms|time_h12ms|time_h12ms_ampm|time_h12ms_ampm_per
-------------------------+----------+----------+---------------+-----------------------+
--2021-11-28 21:30:45.000|21:30:45  |09:30:45  |09:30:45 PM    |09:30:45 p.m.          |

 

 

[ Reference ]

* PostgreSQL TO_CHAR() : https://www.postgresqltutorial.com/postgresql-to_char/

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!  :-)

728x90
반응형
Posted by Rfriend
,