Practical (실용) SQL�12장: 날짜와 시간을 사용한 작업
대전대학교 • 데이터베이스보안 • 13주차
Aaron Snowberger
pp. 237-256
날짜와 시간을 사용한 작업
12
Working with Dates and Times
pp. 237-256
12-1 날짜 및 시간에 대한 데이터 타입과 함수 이해하기
날짜와 시간으로 채워진 열은 이벤트가 발생한 시기나 소요된 시간을 나타낼 수 있으며 이는 흥미로운 질문으로 이어질 수 있습니다. 타임라인의 순간에는 어떤 패턴이 존재하나요? 가장 짧은 이벤트는 무엇이고 가장 긴 이벤트는 무엇인가요? 특정 활동과 그 활동이 발생한 시간 또는 계절 사이에는 어떤 관계가 있나요?
12-1 날짜 및 시간에 대한 데이터 타입과 함수 이해하기
여기에서는 날짜 및 시간과 관련된 네 가지 데이터 타입에 대해 복습해 보겠습니다.
12-2 날짜와 시간 조작하기
SQL 함수를 사용하여 날짜 및 시간에 대한 계산을 수행하거나 구성 요소를 추출할 수 있습니다. 예를 들어 타임스탬프에서 요일을 검색하거나 날짜에서 월만 추출할 수 있습니다.
12-2-1 타임스탬프 값의 구성 요소 추출하기
월 , 연도, 또는 분 단위로 결과를 집계할 때 분석을 위해 날짜 또는 시간 값 중 하나만 필요한 것은 드문 일이 아닙니다.
PostgreSQL 의 date_part() 함수를 사용하여 이러한 구성 요소를 추출할 수 있습니다. 형식은 다음과 같습니다.
데이터베이스는 PostgreSQL
시간대 설정을 반영하도록 값을 반환하므로 출력이 다를 수 있습니다.
SELECT
date_part('year', '2022-12-01 18:37:12 EST'::timestamptz) AS year,
date_part('month', '2022-12-01 18:37:12 EST'::timestamptz) AS month,
date_part('day', '2022-12-01 18:37:12 EST'::timestamptz) AS day,
date_part('hour', '2022-12-01 18:37:12 EST'::timestamptz) AS hour,
date_part('minute', '2022-12-01 18:37:12 EST'::timestamptz) AS minute,
date_part('seconds', '2022-12-01 18:37:12 EST'::timestamptz) AS seconds,
date_part('timezone_hour', '2022-12-01 18:37:12 EST'::timestamptz) AS tz,
date_part('week', '2022-12-01 18:37:12 EST'::timestamptz) AS week,
date_part('quarter', '2022-12-01 18:37:12 EST'::timestamptz) AS quarter,
date_part('epoch', '2022-12-01 18:37:12 EST'::timestamptz) AS epoch;
12-2-1 타임스탬프 값의 구성 요소 추출하기
12-2-1 타임스탬프 값의 구성 요소 추출하기
PostgreSQL은 date_part() 함수와 동일한 방식으로 datetimes를 구문 분석하는 표준 SQL extract() 함수도 지원하지만 두 가지 이유로 date_part()를 사용했습니다. 첫째, 이름 자체만으로도 역할을 상기시킵니다. 둘째, extract()는 데이터베이스 관리자에서 널리 지원되지 않습니다.
12-2-2 E|임스템프 구성 요소에서 날짜시간 값 만들기
연도, 월 일이 별도의 열에 존재하는 데이터셋을 발견하는 것은 드문 일이 아니며 이러한 구성 요소에서 datetime 값을 생성할 수 있습니다. 날짜에 대해 계산을 수행하려면 이러한 부분을 하나의 열로 올바르게 결합하고 형식을 지정하는 것이 좋습니다.
이 세 가지 함수에는 integer 타입의 변수를 입 력으로 사용하지만 두 가지 예외가 있습니다. 초는 소수로 된 초 단위를 제공할 수 있기 때문에 double precision 타입의 숫자로 지정하고 시간대는 시간대 이름을 지정하는 text 타입의 문자열로 지정해야 합니다.
12-2-3 현재 날짜 및 시간 검색하기
행을 업데이트할 때 쿼리의 일부로 현재 날짜 또는 시간을 기록해야 하는 경우에 표준 SQL도 이에 대한 함수를 제공합니다.
이러한 함수들은 쿼리(또는 10장에서 다룬 트랜잭션 아래 그룹화된 쿼리 모음)가 시작될 때 시간을 기록하기 때문에 쿼리 실행에 걸리는 시간과 관계없이 쿼리를 실행하는 내내 동일한 시간을 제공합니다.
12-2-3 현재 날짜 및 시간 검색하기
만약 쿼리 실행 중에 시계가 변경되는 방식을 날짜와 시간에 반영하고 싶다면 PostgreSQL 전용 clock_timestamp() 함수를 사용하여 시간 경과에 따라 기록할 수 있습니다. 이렇게 하면 10만 개의 행을 업데이트하고 매번 타임스탬프를 삽입할 때 각 행은 쿼리 시작 시간이 아닌 행이 업데이트된 시간을 가져옵니다. clock_timestamp()는 대용량 쿼리를 느리게 만들고 시스템 제한이 적용될 수 있다는 점을 주의하세요.
쿼리를 실행하면 최종 SELECT 문의 결과는 current_timestamp_col 의 시간은 모든 행에 대해 동일하지만 clock_timestamp_col 의 시간은 삽입된 각 행에 따라 증가한다는 것을 보여야 합니다.
12-3 시간대 다루기
타임스탬프를 기록해 두면 데이터를 기록한 위치 정보와 결합해 유용한 정보를 찾을 수 있습니다. 그러나 데이터셋의 datetime 열에 표준 시간대 데이터가 없는 경우도 있습니다. 그렇다고 데이터 분석 측면에서 항상 트랜잭션을 차단하는 것도 아닙니다. 모든 이벤트가 동일한 위치에서 발생했다.
하지만 데이터를 가져올 때 세션 시간대를 설정하고 datetimes를 timestamptz 열에 로드하길 권합니다. 이렇게 하면 나중에 데이터를 잘못 해석하는 일을 방지할 수 있습니다.
12-3-1 시간대 설정 찾기
코드 12-4처럼 SHOW 명령에 timezone 키워드를 사용하거나 current_setting() 함수에 timezone 인수를 사용하면 현재 시간대 설정을확인할수 있습니다.
두 코드 중 하나를 실행하면 운영체제 및 현재 지역에 설정된 시간대 설정이 표시됩니다. 한국에 있는 독자 분들이 코드 12-4를 pgAdmin에 입력하고 실행하면 Asia/Seoul 이 반환됩니다.
12-3-1 시간대 설정 찾기
두 문장 모두 동일한 정보를 제공하지만 make_timestamptz()와 같은 다른 함수에 대한 입력으로는 current_setting( ）을 사용하는 게 좋습니다.
또한 코드 12- 5 의 두 명령을 사용하여 모든 시간대 이름, 약어 , 해당 UTC 오프셋을 나열할 수 있습니다.
WHERE 절을 사용하여 이러한 SELECT 문 중 하나를 쉽게 필터링하여 특정 위치 이름이나 시간대를 조회할수있습니다.
12-3-2 시간대 설정하기
PostgreSQL을 설치할 때 서버의 기본 시간대는 PostgreSQL이 시작할 때마다 읽은 수십 개의 값을 포함하는 파일인 postgresql.conf에서 매개 변수로 설정되었습니다.
그리고 때로는 PostgreSQL을 설치한 방식에 따라 다릅니다. postgresql.conf를 영구적으로 변경하려면 파일을 편집하고 서버를 다시 시작해야 합니다.
그 대신 , 서버에 연결되어 있는 동안 지속되는 세션별로 시간대를 설정하는 방법을 살펴보겠습니다. 이 솔루션은 쿼리에서 특정 테이블을 보거나 타임스탬프를 처리하는 방법을 지정하려는 경우에 편리합니다.
12-4 날짜 및 시간을 활용하여 계산하기
숫자를 다룰 때와 같은 방식으로 datetime 및 interval 타입에 대해 간단한 산술을 수행할 수 있습니다. 더하기, 빼기. 곱하기, 나누기는 모두 PostgreSQL에서 수학 연산자 +, -, *, I를 사용하여 수행할 수 였습니다.
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
뉴욕시 택시 및 리무진 위원회는 월별 노란색 택시 운행과 기타 렌트 차량에 대한 데이터를 발표합니다. 이 방대하고 풍부한 데이터셋에 날짜 함수를 실용적으로 사용해 보겠습니다.
책의 실습 자료로 포함되어 었는 nyc_yellow_taxi_trips.csv 파일에는 2016년 6월 1 일 하루 동안의 노란색 택시 운행 기록이 들어 있습니다.
CREATE TABLE nyc_yellow_taxi_trips (
trip_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
vendor_id text NOT NULL,
tpep_pickup_datetime timestamptz NOT NULL,
tpep_dropoff_datetime timestamptz NOT NULL,
passenger_count integer NOT NULL,
trip_distance numeric(8,2) NOT NULL,
pickup_longitude numeric(18,15) NOT NULL,
pickup_latitude numeric(18,15) NOT NULL,
rate_code_id text NOT NULL,
store_and_fwd_flag text NOT NULL,
dropoff_longitude numeric(18,15) NOT NULL,
dropoff_latitude numeric(18,15) NOT NULL,
payment_type text NOT NULL,
fare_amount numeric(9,2) NOT NULL,
extra numeric(9,2) NOT NULL,
mta_tax numeric(5,2) NOT NULL,
tip_amount numeric(9,2) NOT NULL,
tolls_amount numeric(9,2) NOT NULL,
improvement_surcharge numeric(9,2) NOT NULL,
total_amount numeric(9,2) NOT NULL
);
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
가져오기가 완료되면 2016년
6월 1 일에 노란색 택시를 탄 기록으로 368,774개의 행이 있어야 합니다.
COPY nyc_yellow_taxi_trips (
vendor_id,
tpep_pickup_datetime,
tpep_dropoff_datetime,
passenger_count,
trip_distance,
pickup_longitude,
pickup_latitude,
rate_code_id,
store_and_fwd_flag,
dropoff_longitude,
dropoff_latitude,
payment_type,
fare_amount,
extra,
mta_tax,
tip_amount,
tolls_amount,
improvement_surcharge,
total_amount
)
FROM 'C:\YourDirectory\nyc_yellow_taxi_trips.csv'
WITH (FORMAT CSV, HEADER);
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
두 타임스탬프 열의 값에는 시간대가 포함됩니다. CSV 파일의 모든 행에서 타임스탬프에 포함된 시간대는 -4로 표시되며, 이는 뉴욕시와 미국 동부 해안의 나머지 지역에서 일광 절약 시간을 준수하는 동부 시간대의 서머 타임 UTC 오프셋입니다. 여러분이 지금 해당 지역에 있지 않거나 PostgreSQL 서버가 동부 시간에 있지 않은 경우 다음 코드로 시간대를 설정해 결과가 제 것과 일치하게 해보세요.
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
하루 중 가장 바쁜 시간대
숫지를 훑어보면 2016 년 6월 1 일 오후 6시에서 10 시 사이에 뉴욕시 택시의 승객이 가장 많았다는 걸 알 수 있습니다. 퇴근 시간과 여름 저녁에 이루어지는 과도한 활동을 반영한 결과일 수도 있겠군요. 그러나 전체 패턴을 보려면 데이터를 시각화하는 것이 가장 좋습니다.
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
엑셀에서 시각화하기 위해 CSV로 내보내기
Microsoft 엑셀과 같은 도구를 사용하여 데이터를 차트로 작성하면 패턴을 더 쉽게 이해할 수 있기 때문에 쿼리 결과를 CSV 파일로 내보내 차트를작성하는 경우가많습니다.
12-4-1 뉴욕시 택시 데이터에서 패턴 찾기
택시 승차후 이동 시간은 언제 가장 긴가요?
또 다른 흥미로운 질문을 살펴보겠습니다. 택시를 타고 오래 달리는 시간대는 언제일까요? 답을 찾는 한 가지 방법으로는 각 시간의 중간 이동 시간을 계산하는 것입니다. 중앙값은 정렬된 값 세트의 중간값입니다. 평균과는 다르게 세트 안에 있는 매우 작거나 매우 큰 값이 결과를 왜곡하지 않기 때문에 비교를 할 때는 중앙값이 평균보다 더 정확합니다.
12-4-2 Amtrak 데이터에서 패턴 찾기
미국의 철도 서비스인 Amtrak은 미국 전역에 걸쳐 여러 패키지 여행을 제공합니다. Amtrak 웹 사이트(http://www.amtrak.com/)의 데이터를 사용하여 여행의 각 구간에 대한 정보를 보여 주는 테이블을 작성합니다.
12-4-2 Amtrak 데이터에서 패턴 찾기
기차 이동 시간 계산하기
각 타임스탬프 입력은 출발 또는 도착하는 도시의 시간대를 반영합니다. 도시의 시간대를 지정하면 여행 기간을 정확하게 계산하고 시간대 변경을 반영할 수 있습니다. 타임스탬프는 중부 표준 시간을 참조로 사용해 각 컴퓨터의 기본 시간대에 관계없이 동일한 데이터를 보여 줍니다.
CREATE TABLE train_rides (
trip_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
segment text NOT NULL,
departure timestamptz NOT NULL,
arrival timestamptz NOT NULL
);
INSERT INTO train_rides (segment, departure, arrival)
VALUES
('Chicago to New York', '2020-11-13 21:30 CST', '2020-11-14 18:23 EST'),
('New York to New Orleans', '2020-11-15 14:15 EST', '2020-11-16 19:32 CST'),
('New Orleans to Los Angeles', '2020-11-17 13:45 CST', '2020-11-18 9:00 PST'),
('Los Angeles to San Francisco', '2020-11-19 10:10 PST', '2020-11-19 21:24 PST'),
('San Francisco to Denver', '2020-11-20 9:10 PST', '2020-11-21 18:38 MST'),
('Denver to Chicago', '2020-11-22 19:10 MST', '2020-11-23 14:50 CST');
SET TIME ZONE 'US/Central';
SELECT * FROM train_rides;
12-4-2 Amtrak 데이터에서 패턴 찾기
기차 이동 시간 계산하기
이제 기차가 정차하는 각 구간을 만들었으므로 코드 12-12를 사용하여 각 구간의 기간을 계산합니다.
계산을 보기 전에 departure 열 주위의 추가 코드를 확인해 보세요. 타임스탬프의 여러 구성 요소의 형식을 지정하는 PostgreSQL 전용 형식 지정 함수가 있습니다.
12-4-2 Amtrak 데이터에서 패턴 찾기
누적 이동 시간 계산하기
각 계산에서 PostgreSQL은 시간대의 변경을 고려하므로 뺄셈을 할 때 실수로 시간을 더하거나 잃지 않습니다. timestamp without time zone 데이터 타입을 사용하면 구간이 여러 시간대에 걸쳐 있을 때 잘못된 여행 기간으로 끝납니다.
살펴보니 샌프란시스코에서 덴버로 가는 것은 미국 기차 여행 중 가장 긴 구간입니다. 그러나 전체 이동 시간은 얼마나 걸릴까요?
12-4-2 Amtrak 데이터에서 패턴 찾기
누적 이동 시간 계산하기
PostgreSQL은 간격의 일 day 부분에 대한 합계와 시간 부분에 대한 합계를 생성합니다. 이는 이 구문을 사용하여 데이터베이스 intervals를 합산하는 것의 단점 중 하나입니다.
코드 12-14는 제한을 우회하기 위해 justify_interval() 함수로 누적 기간에 대한 윈도우 함수를 래핑합니다.
justify_interval() 함수는 24시간은 일로, 30 일은 월로 변환하도록 간격 계산의 출력을 표준화합니다.
과제1 연습 문제
뉴욕시 택시 데이터에서 승차 및 하차 타임스탬프를 사용하여 각 주행 시간을 계산합니다. 가장 긴 운행에서 가장 짧은 운행 순으로 쿼리 결과를 정렬합니다. 뉴욕시 공무원에게 물어보고 싶은 최장 또는 최단 운행에 대해 뭔가 알아차린 게 있나요?
과제2 연습 문제
AT TIME ZONE 키워드를 사용해 2100 년 1 월 1 일의 뉴욕에 도착하는 순간 런던, 요하네스버그, 모스크바, 멜버른의 날짜와 시간을 표시하는 쿼리를 작성합니다. 시간대 이름은 코드 12―5를 참고하세요.
과제3 연습 문제
보너스 과제는 11 장의 통계 함수를 사용하는 문제입니다 운행 시간과 승객에게 부과된 총 금액을 나타내는 뉴욕시 택시 데이터의 total_amount 열을 사용하여 상관계수와 r―제곱 값을 계산해 보세요.
trip_distance 열과 total_amount 열에 대해서도 동일하게 수행하고. 쿼리를 3시간 이하의 운행으로 제한하세요.
Thanks!
Do you have any questions?�
Please keep this slide for attribution