소개
이 랩에서는 PostgreSQL 의 강력한 날짜 및 시간 연산을 탐색합니다. 시간대 정보가 포함된 타임스탬프를 다루는 방법을 배우게 되며, 이는 다양한 지리적 위치의 데이터를 처리하는 애플리케이션에 필수적인 기술입니다.
먼저 시간대 인식을 보장하기 위해 TIMESTAMPTZ 데이터 타입을 사용하여 이벤트 데이터를 저장하도록 설계된 테이블을 생성합니다. 그런 다음 다른 시간대에서 발생하는 이벤트에 대한 데이터를 삽입하고 PostgreSQL 이 이 데이터를 어떻게 정규화하는지 배우게 됩니다. 마지막으로 EXTRACT, AT TIME ZONE 절과 같은 함수를 사용하고 INTERVAL을 사용하여 시간 차이를 계산하여 이 시간 데이터를 쿼리하고 조작하는 연습을 하게 됩니다.
시간 기반 이벤트를 위한 테이블 생성
이 단계에서는 PostgreSQL 데이터베이스에 연결하고 이벤트 정보를 저장할 테이블을 생성합니다. 이 테이블은 다양한 시간대의 타임스탬프를 정확하게 저장하는 데 필수적인 TIMESTAMPTZ 데이터 타입을 사용합니다.
TIMESTAMPTZ 이해하기
PostgreSQL 의 TIMESTAMPTZ (timestamp with time zone) 데이터 타입은 전 세계의 날짜와 시간을 처리하도록 설계되었습니다. 특정 시간대를 가진 값을 삽입하면 PostgreSQL 은 저장 시 해당 값을 협정 세계시 (UTC) 로 변환합니다. 이러한 표준화 덕분에 원래 시간대에 관계없이 시간을 쉽게 비교하고 계산할 수 있습니다.
1 단계: PostgreSQL 에 연결
먼저 터미널을 엽니다. psql 대화형 쉘을 사용하여 PostgreSQL 데이터베이스에 연결합니다. 이후의 모든 SQL 명령은 이 쉘에서 실행합니다.
sudo -u postgres psql
이제 postgres=#와 같이 보이는 PostgreSQL 프롬프트가 표시됩니다.
2 단계: events 테이블 생성
이제 다음 SQL 명령을 실행하여 events 테이블을 생성합니다. 이 테이블은 각 이벤트에 대한 ID, 이름 및 시간을 저장합니다.
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(255),
event_time TIMESTAMPTZ
);
이 명령은 세 개의 열을 가진 events라는 테이블을 생성합니다.
id: 자동 증가하는 기본 키입니다.event_name: 이벤트 이름을 위한 텍스트 필드입니다.event_time: 시간대 지원이 포함된 타임스탬프입니다.
3 단계: 테이블 생성 확인
테이블이 올바르게 생성되었는지 확인하려면 \d 명령으로 구조를 검사할 수 있습니다.
\d events
events 테이블의 스키마가 표시되어 열과 데이터 타입을 확인할 수 있습니다.
Table "public.events"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
event_name | character varying(255) | | |
event_time | timestamp with time zone | | |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
시간 기반 데이터를 저장하기 위한 테이블 설정을 성공적으로 완료했습니다.
시간대 정보와 함께 데이터 삽입
이 단계에서는 events 테이블에 레코드를 삽입합니다. 각 레코드는 특정 시간대와 연결된 타임스탬프를 가지며, PostgreSQL 이 다양한 지리적 위치의 데이터를 어떻게 처리하는지 보여줍니다.
1 단계: 이벤트 데이터 삽입
psql 쉘에서 다음 INSERT 문을 실행하여 테이블에 세 개의 이벤트를 추가합니다. 각 event_time에 시간대 식별자가 포함되어 있음을 확인하세요.
로스앤젤레스 (UTC-8) 에서 열리는 컨퍼런스 콜 이벤트 삽입:
INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');
런던 (UTC+0) 에서 열리는 프로젝트 회의 삽입:
INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');
로스앤젤레스에서 열리는 후속 회의 삽입:
INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');
2 단계: 데이터 삽입 확인
PostgreSQL 이 이 데이터를 어떻게 저장했는지 확인하기 위해 events 테이블을 쿼리합니다.
SELECT * FROM events;
다음과 같은 출력이 표시됩니다.
id | event_name | event_time
----+-------------------+------------------------
1 | Conference Call | 2024-01-20 18:00:00+00
2 | Project Meeting | 2024-01-20 18:00:00+00
3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)
모든 event_time 값이 UTC( +00 오프셋으로 표시됨) 로 표시되는 것을 확인하세요. 예를 들어, 로스앤젤레스 (UTC-8) 의 10:00:00은 UTC 18:00:00으로 변환되었습니다. 이러한 내부 변환 덕분에 일관된 시간 기반 비교가 가능합니다.
타임스탬프 조회 및 형식 지정
이제 UTC 로 저장된 데이터를 원하는 시간대로 조회하고 표시할 수 있습니다. 이 단계에서는 EXTRACT 함수와 AT TIME ZONE 절을 사용하여 타임스탬프를 조작하고 형식화합니다.
1 단계: EXTRACT를 사용하여 타임스탬프의 일부 추출
EXTRACT 함수를 사용하면 타임스탬프에서 특정 구성 요소 (예: 연도, 월, 시간) 를 추출할 수 있습니다. 각 이벤트의 연도를 가져와 보겠습니다.
SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;
이 쿼리는 저장된 UTC 타임스탬프에서 연도를 추출합니다.
event_name | event_year
-------------------+------------
Conference Call | 2024
Project Meeting | 2024
Follow-up Meeting | 2024
(3 rows)
2 단계: AT TIME ZONE을 사용하여 타임스탬프 변환
저장된 UTC 시간을 특정 로컬 시간대로 보려면 AT TIME ZONE 절을 사용합니다. 모든 이벤트 시간을 America/Los_Angeles 시간대로 다시 변환해 보겠습니다.
SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;
이 쿼리는 UTC event_time을 가져와 로스앤젤레스 시간으로 해당 로컬 시간을 표시합니다.
event_name | los_angeles_time
-------------------+---------------------
Conference Call | 2024-01-20 10:00:00
Project Meeting | 2024-01-20 10:00:00
Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)
보시다시피, UTC 18:00:00에 발생한 'Project Meeting'은 로스앤젤레스 시간으로 10:00:00으로 올바르게 표시됩니다.
3 단계: EXTRACT와 AT TIME ZONE 결합
이러한 기능을 결합하여 더 강력한 쿼리를 만들 수 있습니다. 예를 들어, Europe/London 시간대로 각 이벤트가 발생한 시간을 찾으려면 다음과 같이 할 수 있습니다.
SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;
이 쿼리는 먼저 시간을 런던 시간대로 변환한 다음 시간을 추출합니다.
event_name | event_hour_london
-------------------+-------------------
Conference Call | 18
Project Meeting | 18
Follow-up Meeting | 20
(3 rows)
시간 차이 계산
일반적인 요구 사항 중 하나는 두 이벤트 간의 기간을 계산하는 것입니다. PostgreSQL 에서는 한 타임스탬프에서 다른 타임스탬프를 빼면 시간의 기간을 나타내는 INTERVAL 데이터 타입이 결과로 나옵니다.
1 단계: 두 이벤트 간의 차이 계산
'Conference Call'과 'Follow-up Meeting' 사이의 경과 시간을 찾아보겠습니다. 이를 위해 events 테이블을 자기 자신과 조인할 수 있습니다.
SELECT
e1.event_name AS event1,
e2.event_name AS event2,
e2.event_time - e1.event_time AS time_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';
이 쿼리는 다음과 같이 작동합니다.
events테이블의 두 개의 가상 인스턴스e1과e2를 생성합니다.e1을 'Conference Call' 행으로만 필터링합니다.e2를 'Follow-up Meeting' 행으로만 필터링합니다.e1의event_time에서e2의event_time을 뺍니다.
결과는 INTERVAL 값입니다.
event1 | event2 | time_difference
-----------------+-------------------+-----------------
Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)
시간 차이는 1 일 2 시간입니다.
2 단계: INTERVAL에서 구성 요소 추출
INTERVAL에 EXTRACT를 사용하여 기간의 특정 부분 (예: 일수 또는 시간 수) 을 가져올 수도 있습니다. 이전 계산에서 일수만 추출해 보겠습니다.
SELECT
EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';
이렇게 하면 일수에 대한 숫자 값을 얻을 수 있습니다.
days_difference
-----------------
1
(1 row)
3 단계: PostgreSQL 종료
이 실습을 위한 연습을 완료했습니다. 이제 psql 쉘을 종료하고 메인 터미널로 돌아갈 수 있습니다.
\q
요약
이 실습에서는 PostgreSQL 의 필수적인 날짜 및 시간 연산을 살펴보았습니다. 다음을 배웠습니다.
- 시간대 인식 정보를 올바르게 저장하기 위해
TIMESTAMPTZ데이터 타입을 사용하여 테이블을 생성하는 방법. - 지정된 시간대로 타임스탬프를 삽입하고 PostgreSQL 이 저장 시 UTC 로 변환하는 방법을 관찰하는 방법.
EXTRACT함수를 사용하여 타임스탬프에서 연도 또는 시간과 같은 특정 구성 요소를 검색하는 방법.AT TIME ZONE절을 사용하여 저장된 UTC 시간을 원하는 로컬 시간대로 표시하는 방법.- 두 이벤트 간의 기간을 계산하고, 그 결과로
INTERVAL을 얻고, 해당 인터벌에서 구성 요소를 추출하는 방법.
이러한 기술은 여러 지역에 걸쳐 시간 데이터를 정확하게 관리하는 강력한 애플리케이션을 구축하는 데 기본이 됩니다.
