[PostgreSQL] 기본 데이터 타입 정리 - Temporal types

---

• Character types / why should we use char instead of varchar?
• Numeric types
• Boolean types
• Temporal types
• UUID for storing Universally Unique Identifiers
• Array for storing array strings, numbers, etc...
• JSON stores JSON data
• hstore stores key-value pair
• User defined data

---

Temporal types - time

8 bytes, 00:00:00~24:00:00 범위로 들어가게된다.

 

기본적인 포멧

column_name TIME;

HH:MM   
HH:MM:SS
HHMMSS

01:02
01:02:03
010203

column_name TIME(precision);

MM:SS.pppppp    
HH:MM:SS.pppppp
HHMMSS.pppppp

04:59.999999
04:05:06.777777
040506.777777​

 

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


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;

select * from shifts;

 

time with time zone 타입도 있다!

12 bytes, 기본적인 시간 포멧 뒤에 타임존이 들어가게 된다.

column TIME with time zone


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

 

현재시간 ( 타임존도 기본적으로 나온다.)

SELECT CURRENT_TIME;

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

 

precision 자릿수를 조정할 수 있다. 따로 precision 수치를 넣어주지 않으면, 최대 precision만큼 나오게된다.

SELECT CURRENT_TIME(5);

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

 

local time을 가져올 수 있다.

SELECT LOCALTIME;

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

 

역시 precision fuction을 적용할 수 있다.

SELECT LOCALTIME(0);

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

 

다음 form 을 사용해서 타임존을 전환할 수 있다.

[TIME with time zone] AT TIME ZONE time_zone // difault form

 

local time 을 'UTC-7' 로 바꾸고 싶을때.

SELECT LOCALTIME AT TIME ZONE 'UTC-7';

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

 

다음 form을 사용하여 특정 value를 추출할 수 있다.

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;

위 쿼리의 결과

 

+, -, * 과 같은 연산자들을 사용하여 산술연산도 가능하다

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

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

 

interval (뒤에 기술) 을 사용하여 연산도 가능하다

SELECT LOCALTIME + interval '2 hours' AS result;

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

 

---

Temporal types - date

 

4byte, 4713 BC ~ 5874897 AD 의 범위를 갖는다.

 

기본적인 format 은 yyyy-mm-dd e.g., 2020-08-17 이다.

 

date 테이블을 생성한 후, default current date 를 사용하면 current date가 들어간다.

 

기본적인 현재시간, 날짜 등은 db 서버 기준이다.

 

예)

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
	document_id serial PRIMARY KEY,
	header_text VARCHAR (255) NOT NULL,
	posting_date DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO documents (header_text)
VALUES('Billing to customer XYZ');


//
SELECT * FROM documents;


//
document_id |       header_text       | posting_date
-------------+-------------------------+--------------
           1 | Billing to customer XYZ | 2020-08-17
(1 row)

 

아래 쿼리문은 current date에 관한 쿼리문이다.

SELECT NOW()::date;

SELECT CURRENT_DATE; 

 

하지만 위 쿼리문은 'yyyy-mm-dd' format만 뱉기때문에, to_char() fuction을 사용하여 format을 조정해줄 수 있다.

 

첫번째 parameter는 now() :: date 와 같이 어떤 value를 가져올것인지,

두번째 parameter는 'dd/mm/yyyy'와 같이 output format을 입력해주면 된다.

SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');

//
to_char
------------
 23/06/2016
(1 row)


SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');

//
to_char
--------------
 Jun 23, 2016
(1 row)

 

설명을 위해 새로운 테이블을 하나 만들어보겠다.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	first_name VARCHAR (255),
	last_name VARCHAR (355),
	birth_date DATE NOT NULL,
	hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES ('Shannon','Freeman','1980-01-01','2005-01-01'),
	   ('Sheila','Wells','1978-02-05','2003-01-01'),
	   ('Ethel','Webb','1975-01-01','2001-01-01');

 

연산자를 사용하면 날짜간의 연산이 가능하다!

SELECT
	first_name,
	last_name,
	now() - hire_date as diff
FROM
	employees;
    
//
first_name | last_name |           diff
------------+-----------+---------------------------
 Shannon    | Freeman   | 4191 days 08:25:30.634458
 Sheila     | Wells     | 4922 days 08:25:30.634458
 Ethel      | Webb      | 5652 days 08:25:30.634458
(3 rows)

 

age() function을 사용하여 나이를 계산할 수도 있다!

SELECT
	employee_id,
	first_name,
	last_name,
	AGE(birth_date)
FROM
	employees;
    
//
employee_id | first_name | last_name |           age
-------------+------------+-----------+-------------------------
           1 | Shannon    | Freeman   | 36 years 5 mons 22 days
           2 | Sheila     | Wells     | 38 years 4 mons 18 days
           3 | Ethel      | Webb      | 41 years 5 mons 22 days
(3 rows)

 

age() funcion을 사용하면 특정 날짜를 기준으로 나이를 구할수도 있다.

SELECT
	employee_id,
	first_name,
	last_name,
	age('2015-01-01',birth_date)
FROM
	employees;
    
//
employee_id | first_name | last_name |           age
-------------+------------+-----------+--------------------------
           1 | Shannon    | Freeman   | 35 years
           2 | Sheila     | Wells     | 36 years 10 mons 24 days
           3 | Ethel      | Webb      | 40 years
(3 rows)

 

extract() function을 사용하여 year, quarter, month, week, day from a date value 를 추출할 수 있다.

SELECT
	employee_id,
	first_name,
	last_name,
	EXTRACT (YEAR FROM birth_date) AS YEAR,
	EXTRACT (MONTH FROM birth_date) AS MONTH,
	EXTRACT (DAY FROM birth_date) AS DAY
FROM
	employees;
    
//
employee_id | first_name | last_name | year | month | day
-------------+------------+-----------+------+-------+-----
           1 | Shannon    | Freeman   | 1980 |     1 |   1
           2 | Sheila     | Wells     | 1978 |     2 |   5
           3 | Ethel      | Webb      | 1975 |     1 |   1
(3 rows)    

---

Temporal types - timestamp data types

 

timestamp - 날짜 + 시간                                8byte

 

timestampz - 날짜 + 시간 + 타임존 (UTC)       8byte

 

db 서버의 timezone을 바꾸어도 timestamp value 는 변경되지 않는다.

 

timestamp data types 예제

 

테이블 생성

ts - timestamp

tstz - timestampz

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);

 

타임존 설정

SET timezone = 'America/Los_Angeles';

SHOW TIMEZONE;

//
TimeZone
---------------------
 America/Los_Angeles
(1 row)

 

demo timestamp data 입력

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

SELECT 
   ts, tstz
FROM 
   timestamp_demo;
   
//
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)​

 

timezone 변경

SET timezone = 'America/New_York'; // LA -> NY

SELECT
	ts,
	tstz
FROM
	timestamp_demo;
    
//
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)

timestamp는 그대로지만, timestampz은 변경된 타임존에 맞게 변경된걸 볼 수 있다.

 

 

now() function 또는 current_timestamp 를 사용하면 현재 시간 data를 가져올 수 있다.

SELECT NOW();

//
            now
-------------------------------
 2016-06-22 20:44:52.134125-07
(1 row)


SELECT CURRENT_TIMESTAMP;

//

 

current_timestamp와 current_timed은 모두 타임존이 포함되지만, current_time 은 날짜정보가 없다.

SELECT CURRENT_TIME;

//
      timetz
--------------------
 20:49:04.566025-07
(1 row)

 

날짜를 문자열 방식으로 가져오기위해서는 timeofday() function을 사용한다.

SELECT TIMEOFDAY();

//
          timeofday
-------------------------------------
 Wed Jun 22 20:51:12.632420 2016 PDT

 

timezone(zone, timestamp) function 을 사용하면 특정 타임존에 대한 시간을 구할 수 있다.

SHOW TIMEZONE;

//현재 타임존 LA
      TimeZone
---------------------
 America/Los_Angeles
(1 row)

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

//NY 타임존 적용하면 
      timezone
---------------------
 2016-06-01 03:00:00
(1 row)

 

참고

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

//위처럼 쓰기보다는 ::timestamptz 라고 cast해주면 더 정확하게 값을 넘길 수 있다.
SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);

---

interval data type

interval 을 사용하면 년, 월, 일, 시, 분, 초 등등을 조정할 수 있다.

 

16byte 공간과 -178,000,000년 ~ 178,000,000년 을 범위로 갖는다.

// sign @ 는 생략가능하다
// (P) -> precision
// interval data type

@ interval [ fields ] [ (p) ]

 

fields 에 들어갈 수 있는 문자

SELECT
	now(),
	now() - INTERVAL '1 year 3 hours 20 minutes' 
             AS "3 hours 20 minutes ago of last year";

결과

interval input format

 

postgres_verbose format

INTERVAL '1 year 2 months 3 days';
INTERVAL '2 weeks ago';

quantity unit [quantity unit...] [direction]

quantity : 숫자 ( +, - 도 가능)

 

unit : bc, ad / 세기 / n십년대 / 년도 / 월 / 주 / 일 / 시 / 분 / 초 / 밀리초 / 마이크로초  (y, m, d... 과 같은 축약형 또는 months, days...와 같은 복수형 모두 가능)

 

direction : ago 또는 공백

 

ISO 8601 interval format

 

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

interval value는 무조건 앞에 P 가 오고, time-of-day unit 앞에는 무조건 T 가 온다.

 

iso 8601 에서 사용되는 축약어

abbreviation	description
Y	Years
M	Months (in the Date part)
W	Weeks
D	Days
H	Hours
M	Minutes (in the Time part)
S	Seconds

T 이전에 오는 M 은 months를 나타내고 T 다음에 오는 M은 minutes를 나타낸다.

 

the interval of 6 years 5 months 4 days 3 hours 2 minutes 1 second 을 ISO 8601 로 나타내면?

P6Y5M4DT3H2M1S

 

ISO 8601 을 나타낼 수 있는 다른 형식

P [ years-months-days ] [ T hours:minutes:seconds ]

역시 P 다음에 날짜가 오고 T 다음에 시간이 온다.

 

the interval of 6 years 5 months 4 days 3 hours 2 minutes 1 second 을 위 형식으로 나타내면?

P0006-05-04T03:02:01

 

set intervalstyle 을 통해 여러 스타일로 6 years 5 months 4 days 3 hours 2 minutes 1 second 을 나타낼 수 있다.

SET intervalstyle = 'sql_standard';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'postgres';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'postgres_verbose';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'iso_8601';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

 

interval operators

 

interval values에 연산자 (+, -, *, etc.,) 를 사용할 수 있다

SELECT
INTERVAL '2h 50m' + INTERVAL '10m'; -- 03:00:00

SELECT
INTERVAL '2h 50m' - INTERVAL '50m'; -- 02:00:00

SELECT
600 * INTERVAL '1 minute'; -- 10:00:00

 

to_char() function 을 사용하면 interval value를 문자열로 변환할 수 있다.

TO_CHAR(interval,format)

 

예)

SELECT
    TO_CHAR(
        INTERVAL '17h 20m 05s',
        'HH24:MI:SS'
    );
    
//
to_char
----------
 17:20:05
(1 row)    

 

extract() function을 사용하면 특정 value를 추출할 수 있다.

SELECT
    EXTRACT (
        MINUTE
        FROM
            INTERVAL '5 hours 21 minutes'
    );
    
//
date_part
-----------
        21
(1 row)

 

select justifydays(interval ' ' );

→ 30일당 1달로 바꾸어준다

 

select justifyhours(interval ' ');

→ 24시간당 하루로 바꾸어준다.

 

select justify_interval(interval ' 1 year - 1 hour ');

→ 11 mons 29 days 23:00:00

 

이런식으로도 된다.

SELECT
    justify_days(INTERVAL '30 days'),
    justify_hours(INTERVAL '24 hours');
		justify_interval(interval '1 year -1 hour');

//output
justify_days | justify_hours | justify_insterval
--------------+--------------------------------------
 1 mon        | 1 day        | 11 mons 29 days 23:00:00
(1 row)

SELECT
    justify_interval(interval '1 year -1 hour');
    
//
justify_interval
--------------------------
 11 mons 29 days 23:00:00
(1 row)