46강 SQL 초석 다지기 : PostgreSQL Timestamp Data Type

46강 SQL 초석 다지기 : PostgreSQL Timestamp Data Type   

PostgreSQL

SQL 초석 다지기 46강 시작합니다.

 

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

 

이번 시간에는

▶ PostgreSQL Timestamp Data Type

에 대해서 알아보는 시간입니다.

 

이번 강의에서는 타임스탬프 및 타임스탬프를 포함한 PostgreSQL 타임스탬프 데이터 유형에 대해 알아봅니다. 또한 타임스탬프 데이터를 보다 효과적으로 처리하기 위해 몇 가지 편리한 기능을 사용하는 방법도 배울 수 있습니다.

---

Introduction to PostgreSQL timestamp   

PostgreSQL은 타임스탬프를 처리하기 위한 두 가지 시간 데이터 유형을 제공합니다.

● timestamp: timezone 이 없는 타임스탬프입니다.
● timestamptz:  timezone 이 있는 타임스탬프입니다.
타임스탬프 데이터 유형을 사용하면 날짜와 시간을 모두 저장할 수 있습니다. 그러나 표준 시간대 데이터는 없습니다. 데이터베이스 서버의 표준 시간대를 변경할 때 데이터베이스에 저장된 타임스탬프 값은 자동으로 변경되지 않습니다.

timesmptz 데이터 유형은 timezone 이 있는 타임스탬프입니다. timesmptz 데이터 유형은 timezone 인식 날짜 및 시간 데이터 유형입니다.

PostgreSQL은 timestamptz를 UTC 값으로 저장합니다. 

협정 세계시(UTC) UTC는 그리니치 평균시(GMT)로 불리기도 하는데, UTC와 GMT는 초의 소숫점 단위에서만 차이가 나기 때문에 일상에서는 혼용되어 사용한다. 기술적인 표기에서는 UTC가 사용된다.

●  타임스탬프 열에 값을 삽입하면 PostgreSQL은 timesmptz 값을 UTC 값으로 변환하고 UTC 값을 테이블에 저장합니다.
● 데이터베이스에서 timesmptz를 쿼리 할 때 PostgreSQL은 UTC 값을 데이터베이스 서버, 사용자 또는 현재 데이터베이스 연결에 의해 설정된 표준시 값으로 다시 변환합니다.

 

timestamp와 timestamptz는 모두 다음 쿼리에 표시된 것처럼 타임스탬프 값을 저장하기 위해 8바이트를 사용합니다.

SELECT
      typname,
      typlen
FROM
      pg_type
WHERE
      typname ~ '^timestamp';   

pg_type 카탈로그 pg_type 은 데이터 유형에 대한 정보를 저장합니다. 기본 유형과 열거형(스칼라 유형)은 CREATE TYPE 으로 생성 되고 도메인은 CREATE DOMAIN 으로 생성 됩니다. 테이블의 행 구조를 나타내기 위해 데이터베이스의 각 테이블에 대해 복합 유형이 자동으로 생성됩니다. CREATE TYPE AS 를 사용하여 복합 유형을 생성하는 것도 가능합니다 .

typname ~ '^timestamp' POSIX Regular Expressions 표현 ~  :  비교 ^  :  뒤에 따라오는 문자로 시작하느냐 typname 이 timestamp로 시작하느냐  -> typname LIKE  'timestamp%' 와 같은 표현

timesmptz 값은 UTC 값으로 저장됩니다. PostgreSQL은 timestamptz 값을 가진 표준 시간대 데이터를 저장하지 않습니다.

표준시로 저장하고 timezone으로 현지시간을 계산

---

PostgreSQL timestamp example   

PostgreSQL이 timestamp와 timestamptz를 처리하는 방법을 더 잘 이해하기 위해 timestamp와 timestamptz를 사용하는 예를 살펴보겠습니다.

먼저 열을 모두  timestamp와 timestamptz로 구성된 테이블을 만듭니다.

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);   

 

그런 다음 데이터베이스 서버의 표준 시간대를 America/Los_Angeles로 설정합니다.

(한국은 'Asia/Seoul'입니다.)

SET timezone = 'America/Los_Angeles';   

 

참고로 SHOW TIMEZONE 명령을 사용하여 현재 시간대를 확인할 수 있습니다.

SHOW TIMEZONE;   

그런 다음 timstamp_demo table에 새 행을 삽입합니다.

INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');   

 

그런 다음  timestamp와 timestamptz 열에서 데이터를 쿼리 합니다.

SELECT 
   ts, tstz
FROM 
   timestamp_demo;  

 

여기서  잠깐  이때 Dbeaver를 사용중이였는데 같은 시간이 나와야 하는데.... 아니다. Dbeaver 설정문제인거 같아 찾아보았으나 찾지못했다. HeidiSQL로 툴을 바꾸었다. 잘된다. Dbeaver 설정문제는 다시 찾아봐야겠다.

쿼리는 삽입된 값과 동일한 타임스탬프 값을 반환합니다.
마지막으로 현재 세션의 시간대를 America/New_York로 변경하고 데이터를 다시 쿼리 합니다.

SET timezone = 'America/New_York';

SELECT 
   ts, tstz
FROM 
   timestamp_demo;    

timestamp 열의 값은 변경되지 않는 반면, timestamptz 열의 값은 'America/New_York'의 새 표준시로 조정됩니다.

마지막으로 현재 세션의 시간대를  'Asia/Seoul'로 변경하고 데이터를 다시 쿼리 합니다.

SET timezone = 'Asia/Seoul';   

SELECT 
   ts, tstz
FROM 
   timestamp_demo;   

timestamp 열의 값은 변경되지 않는 반면, timestamptz 열의 값은 'Asia/Seoul'의 새 표준시로 조정됩니다.

일반적으로 timesmptz 데이터 유형을 사용하여 timestamp 데이터를 저장하는 것이 좋습니다.

---

PostgreSQL timestamp functions   

타임스탬프 데이터를 효과적으로 처리하려면 PostgreSQL은 다음과 같은 편리한 기능을 제공합니다.

 

Getting the current time   

현재 타임스탬프를 가져오려면 다음과 같이 NOW() 함수를 사용합니다.

SELECT NOW();   

또는 CURRENT_TIME 스탬프를 사용할 수 있습니다.

SELECT CURRENT_TIMESTAMP;   

 

CURRENT_TIMESTAMP 및 CURRENT_TIME은 모두 시간대와 함께 현재 시간을 반환합니다.

시간을 문자열 형식으로 가져오려면 timeofday() 함수를 사용합니다.

SELECT TIMEOFDAY();  

사용할 일은 별로 없을듯하다.

 

Convert between timezones   

타임스탬프를 다른 표준 시간대로 변환하려면 표준 시간대(Zone, timestamp) 기능을 사용합니다.

SHOW TIMEZONE;   

현재 시간대는 'Asia/Seoul'입니다.

2016-06-01 00:00을 America/New_York 표준시로 변환하려면 다음과 같이 timezone() 기능을 사용합니다.

SELECT timezone('America/New_York','2016-06-01 00:00');  

 

타임스탬프를  timezone() 함수에 문자열로 전달하는 것에 유의하십시오. PostgreSQL는 타임스탬프를 암시적으로 timestamptz로 그립니다. 타임스탬프 값은 다음과 같이 timesmptz 데이터 유형에 명시적으로 캐스팅하는 것이 좋습니다.

SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);   

 

---

이번 강의에서는 PostgreSQL timestamp 데이터 유형을 소개하고 timestamp 값을 조작하는 데 유용한 기능을 사용하는 방법을 배웠습니다.

 

차후 DATE, TIME, TIMESTAMP 함수에 대해서는 다시 정리할 계획입니다.

 

수고하셨습니다.

---

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.