[PostgreSQL] timestamp 다루기

출처 :

   https://ko.wikipedia.org/wiki/%ED%83%80%EC%9E%84%EC%8A%A4%ED%83%AC%ED%94%84

   데이터 분석을 위한 SQL 레시피

 

 타임스텀프란 특정한 시각을 나타내거나 기록하는 문자열입니다. 데이터베이스를 구축하고 서비스를 진행하는 과정에서 데이터가 생성되는 시간을 데이터와 함께 저장하여 활용하는 경우가 많습니다. 그러한 경우 타임스텀프의 요소, 연, 월, 일, 시간, 분 중 필요한 부분을 추출하여 가공해야하는 상황이 있습니다. 

 

 수집해야하는 문자열에 따른 수행 방법에 대해 적어보려고 한다.

현재 시간 수집

# 타임존이 적용된 타임스탬프 수집
SELECT
	CURRENT_DATE AS dt
    , CURRENT_TIMESTAMP AS stamp; 
    
# 타임존을 적용하지 않은 타임스탬프 수집
SELECT
	LOCALTIMESTAMP as stamp;

지정된 시간 수집

SELECT
	# 'CAST(value AS type)' 사용
	CAST('2022-01-11' AS date) AS dt
    CAST('2022-01-11 12:00:00' AS stamp
    
    # 'type value' 사용
    date '2022-01-11' AS dt
    ,timestamp '2022-01-11 12:00:00' AS timestamp
    
    # 'value::type' 사용
    '2022-01-11'::date AS dt
    , '2022-01-11'::timestamp

Timestamp에서 원하는 요소(년, 월, 일, 시간) 수집

SELECT
	stamp
    , EXTRACT(YEAR FROM stamp) AS year # '년' 수집
    , EXTRACT(MONTH FROM stamp) AS month # '월' 수집
    , EXTRACT(DAY FROM stamp) AS day # '일' 수집
    , EXTRACT(HOUR FROM stamp) AS hour '시간' 수집
FROM
	(SELECT CAST('2022-01-11 12:00:00' AS timestamp) AS stamp) AS t;

Timestamp을 문자열 인덱스를 이용하여 원하는 요소 수집

SELECT
	stamp
    # substring 함수 이용
    , substring(stamp, 1, 4) AS year # 1~4까지 문자열 수집
    , substring(stamp, 6, 2) AS month # 6~7까지 문자열 수집
    , substring(stamp, 9, 2) AS day # 9~10까지 문자열 수집
    , substring(stamp, 12, 2) AS hour # 12~13까지 문자열 수집
    , substring(stamp, 1 , 7) AS year_month # 1~7까지 문자열 수집
	# substr 함수 이용
    , substr(stamp, 1, 4) AS year # 1~4까지 문자열 수집
    , substr(stamp, 6, 2) AS month # 6~7까지 문자열 수집
    , substr(stamp, 9, 2) AS day # 9~10까지 문자열 수집
    , substr(stamp, 12, 2) AS hour # 12~13까지 문자열 수집
    , substr(stamp, 1 , 7) AS year_month # 1~7까지 문자열 수집
FROM
	(SELECT CAST('2022-01-11 12:00:00' AS text) AS stamp) AS t