R, Python 분석과 프로그래밍의 친구 (by R Friend)

시계열 데이터를 다루다 보면 년, 분기, 월, 일, 시간, 분, 초 등의 날짜와 시간에 관한 정보를 추출해야 할 일이 있습니다. 

이번 포스팅에서는 Greenplum, PostgreSQL 에서 EXTRACT() 함수로 TIMESTAMP, INTERVAL 데이터 유형에서 날짜, 시간 정보를 추출(retrive)하는 방법을 소개하겠습니다. 

(1) EXTRACT() 함수로 TIMESTAMP 데이터 유형에서 날짜, 시간 정보 가져오기

(2) EXTRACT() 함수로 INTERVAL 데이터 유형에서 날짜, 시간 정보 가져오기 

(1) EXTRACT() 함수로 TIMESTAMP 데이터 유형에서 날짜, 시간 정보 가져오기

먼저 PostgreSQL TIMESTAMP 데이터 유형에서 년(year), 분기(quarter), 월(month), 주(week), 일(day), 시간(hour), 분(minute), 초(second), 밀리초(millisecond), 마이크로초(microsecond) 정보를 EXTRACT(field from source) 함수를 사용해서 가져와보겠습니다. 

------------------------------------------------------
-- PostgreSQL EXTRACT function
-- retriveing a field such as a year, month, and day from a date/time value
-- Syntax: EXTRACT(field FROM source)
-- The field argument specifies which field to extract from the date/time value
-- The source is a value of type 'TIMESTAP' or 'INTERVAL'
-- The 'EXTRACT()' function returns a double precision value. 
-- ref: https://www.postgresqltutorial.com/postgresql-extract/
------------------------------------------------------

SELECT 
	datum AS datetime
	, EXTRACT(YEAR FROM datum)::int AS year 
	, EXTRACT(QUARTER FROM datum)::int AS quarter
	, EXTRACT(MONTH FROM datum)::int AS month 
	, EXTRACT(WEEK FROM datum)::int AS week
	, EXTRACT(DAY FROM datum)::Int AS day 
	, EXTRACT(HOUR FROM datum)::int AS hour 
	, EXTRACT(MINUTE FROM datum)::Int AS minute 
	, EXTRACT(SECOND FROM datum)::Int AS second 
	--, EXTRACT(MILLISECONDS FROM datum)::int AS millisecond -- =45*1000
	--, EXTRACT(MICROSECONDS FROM datum)::Int AS microsecond -- = 45*1000000
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;
    
--datetime               |year|quarter|month|week|day|hour|minute|second|
-------------------------+----+-------+-----+----+---+----+------+------+
--2021-11-28 21:30:45.000|2021|      4|   11|  47| 28|  21|    30|    45|

PostgreSQL의 TIMESTAMP 데이터 유형에서

  - DOY (Day Of Year) : 1년 중 몇 번째 날인지에 대한 값으로, 1일~365일 범위의 값을 가짐.

  - DOW (Day Of Week): 1주일 중 몇 번째 날인지에 대한 값으로, 일요일이 '0', 토요일이 '6'의 값을 가짐.

  - ISODOW (Day Of Week based on ISO 8601) : ISO 8601 표준을 따라서 1주일 중 몇 번째 날인지에 대한 값으로, 월요일이 '1', 일요일이 '7'의 값을 가짐. 

-- extracting the day of year, week from TIMESTAMP type
SELECT 
	datum AS datetime
    -- the DAY OF YEAR that ranges FROM 1 TO 365
	, EXTRACT(DOY FROM datum)::int AS doy 
    
    -- the DAY OF week Sunday(0) TO Saturday(6)
	, EXTRACT(DOW FROM datum)::int AS dow 
    
    -- DAY OF Week based ON ISO 8601 Monday(1) to Sunday(7)
	, EXTRACT(ISODOW FROM datum)::int AS isodow 
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;


--datetime               |doy|dow|isodow|
-------------------------+---+---+------+
--2021-11-28 21:30:45.000|332|  0|     7|

(2) EXTRACT() 함수로 INTERVAL 데이터 유형에서 날짜, 시간 정보 가져오기

위의 (1)번에서는 TIMESTAMP 데이터 유형에 대해서 날짜, 시간 정보를 가져왔다면, 이번에는 INTERVAL 데이터 유형에 대해서 날짜, 시간 정보를 추출해보겠습니다.

INTERVAL 데이터 유형이 익숙하지 않은 분들을 위해서, 아래에 INTERVAL 데이터 유형을 사용해서 기준일로부터 INTERVAL 기간 만큼을 뺀 날짜/시간을 계산해 보았습니다.  ('2021-11-28 21:30:45' 에서 '2년 3개월 5일 2시간 10분 5초' 이전은 날짜/시간은?)

-- The INTERVAL data type allows you to store and manipulate a period of time 
-- in years, months, days, hours, minutes, seconds, etc. 
-- (Syntax) @ INTERVAL [ fields ] [ (p) ]
SELECT 
	TIMESTAMP '2021-11-28 21:30:45' AS datetme
	, TIMESTAMP '2021-11-28 21:30:45'  
	  - INTERVAL '2 years 3 months 5 days 2 hours 10 minutes 5 seconds' 
    AS intv_dt;


--datetme                |intv_dt                |
-------------------------+-----------------------+
--2021-11-28 21:30:45.000|2019-08-23 19:20:40.000|

그럼, INTERVAL 데이터 유형에서 EXTRACT(field FROM source) 함수를 사용해서 년(year), 분기(quarter), 월(month), 일(day), 시간(hour), 분(minute), 초(second) 정보를 가져와 보겠습니다. 

단, 위의 (1)번에서 TIMESTAMP 데이터 유형에 대해서는 가능했던 DOW, DOY, ISODOW, ISOYEAR, WEEK 등의 정보에 대해서는 INTERVAL 데이터 유형에 대해서는 EXTRACT() 함수가 지원하지 않습니다. 

-- extracting the month, day, hour  from an interval. 
-- DOW, DOY, ISODOW, ISOYEAR, WEEK are not supported for INTERVAL
	
SELECT 
	intv AS INTERVAL
	, EXTRACT(YEAR FROM intv)::int AS intv_year
	, EXTRACT(QUARTER FROM intv)::int AS intv_quarter
	, EXTRACT(MONTH FROM intv)::int AS intv_month
	, EXTRACT(DAY FROM intv)::int AS intv_day 
	, EXTRACT (HOUR FROM intv)::int AS intv_hour 
	, EXTRACT(MINUTES FROM intv)::int AS intv_min
	, extract(SECONDS FROM intv)::int AS intv_sec
FROM (
	SELECT INTERVAL '2 years 3 months 5 days 2 hours 10 minutes 5 seconds' 
    	AS intv
	) ts;


--interval                      |intv_year|intv_quarter|intv_month|intv_day|intv_hour|intv_min|intv_sec|
--------------------------------+---------+------------+----------+--------+---------+--------+--------+
--2 years 3 mons 5 days 02:10:05|        2|           2|         3|       5|        2|      10|       5|

[ Reference ]

* PostgreSQL EXTRACT() function
  : https://www.postgresqltutorial.com/postgresql-extract/

다음번 포스팅에서는 PostgreSQL TO_CHAR() 함수를 사용해서 TIMESTAMP 데이터 유형을 포맷을 가진 문자열로 변환 (converting TIMESTAMP data type to a formatted string) 하는 방법을 소개하겠습니다. (https://rfriend.tistory.com/709)

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

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