48강 SQL 초석 다지기 : PostgreSQL TIME Data Type

48강 SQL 초석 다지기 : PostgreSQL TIME Data Type   

PostgreSQL

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

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

이번 시간에는

▶ PostgreSQL TIME Data Type

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

 

요약: 이번 강의에서는 PostgreSQL TIME 데이터 유형을 소개하고 시간 값을 처리하는 몇 가지 유용한 기능을 보여 줍니다.

---

Introduction to PostgreSQL TIME data type   

PostgreSQL은 시간 값을 저장할 수 있는 TIME 데이터 유형을 제공합니다.

다음 문은 TIME 데이터 유형을 사용하여 열을 선언하는 방법을 보여줍니다.

column_name TIME(precision); 

시간 값은 최대 6자리의 정밀도를 가질 수 있습니다. 정밀도는 두 번째 필드에 배치된 분수 자릿수를 지정합니다.

TIME 데이터 유형에는 8바이트가 필요하며 허용되는 범위는 00:00:00 ~ 24:00:00입니다. 다음은 TIME 값의 일반적인 형식을 보여줍니다.

HH:MI   
HH:MI:SS
HHMISS 

For example:

01:02
01:02:03
010203 

 

Precision을 사용하려면 다음 형식을 사용할 수 있습니다.

MI:SS.pppppp    
HH:MI:SS.pppppp
HHMISS.pppppp 

이 형식에서 p는 정밀도이다. 예:

04:59.999999
04:05:06.777777
040506.777777 

 

PostgreSQL은 실제로 SQL 호환, ISO 8601 등을 포함한 거의 모든 합리적인 TIME 형식을 허용한다.

 

ANSI SQL DBMS(Oracle, My-SQL, DB2 등등)들에서 각기 다른 SQL를 사용하므로, 미국 표준 협회(American National Standards Institute)에서 이를 표준화하여 표준 SQL문을 정립 시켜 놓은 것이다. 모든 DBMS는 ANSI SQL을 지원한다. 모든 DBMS의 SQL을 ANSI SQL이 수용하지는 않는다.

ISO8601  날짜와 시간과 관련된 데이터 교환을 다루는 국제 표준 : https://ko.wikipedia.org/wiki/ISO_8601 참조

---

PostgreSQL TIME example   

우리는 종종 사건이나 이동의 시간과 같이 하루의 시간만 저장하는 열에 TIME 데이터 유형을 사용한다. 다음 예를 생각해 보십시오.

먼저 다음 CREATE TABLE 문을 사용하여 shifts라는 새 테이블을 만듭니다.

CREATE TABLE shifts (
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_at TIME NOT NULL,
    end_at TIME NOT NULL
);     

 

둘째, 일부 행을 shifts 테이블에 삽입합니다.

INSERT INTO shifts(shift_name, start_at, end_at)
VALUES('Morning', '08:00:00', '12:00:00'),
      ('Afternoon', '13:00:00', '17:00:00'),
      ('Night', '18:00:00', '22:00:00'); 

 

셋째, 시프트 테이블에서 데이터를 쿼리 합니다.

SELECT * FROM shifts; 

---

PostgreSQL TIME with time zone type   

TIME 데이터 유형 외에도 PostgreSQL은 TIME with time zone을 사용하여 하루 중 시간대를 저장하고 조작할 수 있는 시간대 데이터 유형과 함께 시간대를 제공합니다.

다음 문은 TIME with time zone 데이터 유형이 TIME인 열을 선언하는 방법을 보여줍니다.

column TIME with time zone 

TIME with time zone 데이터 유형의 저장 크기는 12바이트이며 00:00:00+1459 ~ 24:00:00-1459 범위의 표준시 값을 저장할 수 있습니다.

다음은 TIME with time zone 값을 사용한 TIME을 보여줍니다.

04:05:06 PST    
04:05:06.789-8    

 

TIME에 TIMEZONE을 사용할 일이 있을까 싶지만, 이런 것도 있어요 정도로 알고 지나가자.

   

TIMEZONE을 표기하는 3가지 방법 (중요)  

1. FULLNAME :  Asia/Seoul

2. 약어             :  KST, PST

3. POSIX-Style의 Timezone 표기법 : UTC+9

 

PostgreSQL의 timezone 테이블을 한번 조회해 보자. 

SELECT * FROM pg_timezone_names WHERE abbrev='KST'; 

풀네임, 약어, UTC_OFFSET이 나온다.

여기서 문제가 되는 것이 세 번째이다.

 

다음 SQL을 돌려보자.

SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Asia/Seoul'
UNION ALL
SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'UTC+9'
;   

보통 한국시간을 'UTC+9'로 표시하는데 헐 시간이 다르다.

POSIX-Style의 Timezone 표기법을 사용할 때 주의할 점이 있다.

한국시간이 UTC보다 9시간 빠르다고 'UTC+9'라고 표현하는데 

POSIX-Style표현법은 그게 아니다.

한국기준으로 UTC가 9시간 늦다. 즉, 'UTC-9'라고 표현한다.

이거 중요하다!!!.

 

SQL을 고쳐보자

SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'Asia/Seoul'
UNION ALL
SELECT TIMESTAMP '2020-08-06 12:00:00' AT TIME ZONE 'UTC-9'
;  

이제 맞게 나온다

---

Handling PostgreSQL TIME values   

 

Getting the current time

시간대를 사용하여 현재 시간을 가져오려면 다음과 같이 CURRENT_TIME 함수를 사용합니다.

SELECT CURRENT_TIME;

timetz
--------------------
 00:51:02.746572-08
(1 row)    

 

특정 정밀도로 현재 시간을 가져오려면 CURRENT_TIME(정밀) 함수를 사용합니다.

 SELECT CURRENT_TIME(5);

   current_time
-------------------
 00:52:12.19515-08
(1 row)   

 

정밀도를 지정하지 않고 CURRENT_TIME 함수는 사용 가능한 전체 정밀도의 시간 값을 반환합니다.

로컬 시간을 얻으려면 LOCALTIME 함수를 사용합니다.

SELECT LOCALTIME;

      localtime
-----------------
 00:52:40.227186
(1 row)   

 

마찬가지로 특정 정밀도로 로컬 시간을 얻으려면 LOCALTIME(정밀) 기능을 사용합니다.

 SELECT LOCALTIME(0);

 localtime
----------
 00:56:08
(1 row)     

---

Converting time to a different time zone   

 

시간을 다른 표준 시간대로 변환하려면 다음 양식을 사용합니다.

[TIME with time zone] AT TIME ZONE time_zone   

 

예를 들어 현지 시간을 표준 시간대 UTC-7의 시간으로 변환하려면 다음 문장을 사용합니다.

SELECT LOCALTIME AT TIME ZONE 'UTC-7';

      timezone
--------------------
 16:02:38.902271+07
(1 row)   

---

Extracting hours, minutes, seconds from a time value   

시간 값에서 시간, 분, 초를 추출하려면 다음과 같이 EXPROT 기능을 사용합니다.

EXTRACT(field FROM time_value);   

 

필드는 다음 예와 같이 시간, 분, 초, 밀리초가 될 수 있습니다.

SELECT
    LOCALTIME,
    EXTRACT (HOUR FROM LOCALTIME) as hour,
    EXTRACT (MINUTE FROM LOCALTIME) as minute, 
    EXTRACT (SECOND FROM LOCALTIME) as second,
    EXTRACT (milliseconds FROM LOCALTIME) as milliseconds;   

---

Arithmetic operations on time values   

PostgreSQL을 사용하면 시간 값과 시간 및 간격 값 사이에 +, -, *와 같은 산술 연산자를 적용할 수 있습니다.

다음 문은 두 시간 값 사이의 간격을 반환합니다.

SELECT time '10:00' - time '02:00' AS result;

  result
----------
 08:00:00
(1 row) 

 

다음 문장은 현지 시간에 2시간을 추가합니다.

 SELECT LOCALTIME + interval '2 hours' AS result;

    result
-----------------
 03:16:18.020418
(1 row) 

이 예에서는 시간 값과 구간 값의 합이 시간 값입니다.

---

이번 강의에서는 PostgreSQL TIME 데이터 유형과 시간 관련 기능을 사용하여 시간 값을 처리하는 방법에 대해 배웠습니다.

 

수고하셨습니다.

---

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