[PostgreSQL, Greenplum] TO_CHAR() 함수로 TIMESTAMP 데이터를 포맷을 가진 문자열로 바꾸기 (converting a TIMESTAMP data type to a formatted string)
Greenplum and PostgreSQL Database 2021. 11. 26. 16:46지난번 포스팅에서는 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 데이터 유형에 한정해서 설명을 하겠습니다.
직관적인 이해를 돕기위한 간단한 예제로서, 아래는 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/
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
