PostgreSQL 提供了 timestamp、timestamptz、date 和 time 等多種日期時間型別，其中 timestamptz 能夠根據時區進行適當的轉換，因此建議盡量使用。date_part() 函式可以提取日期時間的特定元件，方便進行資料分析。make_timestamptz() 函式則可以從分散的日期時間元件建立帶時區的時間戳記。current_timestamp 和 clock_timestamp() 函式分別用於取得事務開始時間和實際當前時間，兩者在大量資料插入時會有差異。時區處理是日期時間處理中相當重要的一環，PostgreSQL 的 timestamptz 型別和 AT TIME ZONE 子句簡化了時區轉換的操作。
日期與時間處理的高階技術
在處理日期與時間的資料時，PostgreSQL 提供了豐富的函式和型別來滿足不同的需求。從基本的日期時間型別到複雜的時間計算，PostgreSQL 的功能非常全面。
日期時間型別的深入理解
PostgreSQL 支援多種日期時間型別，包括 timestamp、timestamptz（即 timestamp with time zone）、date 和 time。其中，timestamptz 是最推薦使用的型別，因為它能夠根據時區進行適當的轉換。
使用 date_part() 函式提取日期時間元件
date_part() 函式允許你從日期時間值中提取特定的元件，如年、月、日、時、分、秒等。這對於需要對日期時間進行詳細分析的應用非常有用。
SELECT date_part('year', '2022-12-01 18:37:12 EST'::timestamptz);
內容解密：
date_part()函式的第一個引數指定了要提取的日期時間元件。- 第二個引數是日期時間值，需要被轉換為
timestamptz型別以確保時區正確。 '2022-12-01 18:37:12 EST'::timestamptz將字串轉換為帶時區的時間戳。
從日期時間元件建立日期時間值
在某些情況下，你可能會遇到分散的日期時間元件（如年、月、日等），需要將它們組合成一個完整的日期時間值。PostgreSQL 提供了 make_date()、make_time() 和 make_timestamptz() 函式來實作這一點。
使用 make_timestamptz() 建立帶時區的時間戳
SELECT make_timestamptz(2022, 2, 22, 18, 4, 30.3, 'Europe/Lisbon');
內容解密：
make_timestamptz()函式接受年、月、日、時、分、秒和時區作為引數。- 時區引數必須是有效的時區名稱，如
'Europe/Lisbon'。 - 傳回的值是帶時區的時間戳，會根據 PostgreSQL 的時區設定進行顯示。
取得當前日期和時間
在許多應用中，記錄當前日期和時間是非常重要的。PostgreSQL 提供了多個函式來取得當前日期和時間，包括 current_timestamp、localtimestamp、current_date 和 current_time。
使用 current_timestamp 和 clock_timestamp()
CREATE TABLE current_time_example (
time_id integer GENERATED ALWAYS AS IDENTITY,
current_timestamp_col timestamptz,
clock_timestamp_col timestamptz
);
INSERT INTO current_time_example (current_timestamp_col, clock_timestamp_col)
SELECT current_timestamp, clock_timestamp()
FROM generate_series(1,1000);
SELECT * FROM current_time_example;
內容解密：
current_timestamp傳回查詢開始時的時間戳。clock_timestamp()傳回實際的當前時間，會隨著查詢的執行而變化。- 在插入大量資料時，這兩個函式的行為差異會顯現出來。
時區處理的最佳實踐
處理時區是日期時間處理中的一個重要方面。PostgreSQL 的 timestamptz 型別使得時區處理變得更加簡單。
正確使用時區
SELECT '2022-01-01 12:00:00'::timestamptz AT TIME ZONE 'America/New_York';
內容解密：
- 使用
AT TIME ZONE子句可以將時間戳轉換為指定的時區。 'America/New_York'是一個有效的時區名稱。
管理時間戳記與時區
在處理資料時，時間戳記（timestamp）是非常重要的資訊。PostgreSQL提供了多種函式和資料型別來處理時間戳記，包括timestamptz，它能夠儲存包含時區資訊的時間戳記。
瞭解時間戳記函式
在PostgreSQL中，我們可以使用generate_series()函式來產生一系列的整數，並將其與clock_timestamp()函式結合使用，以展示時間戳記的插入過程。
CREATE TABLE timestamp_test (
current_timestamp_col timestamp with time zone,
clock_timestamp_col timestamp with time zone
);
INSERT INTO timestamp_test (current_timestamp_col, clock_timestamp_col)
SELECT current_timestamp, clock_timestamp()
FROM generate_series(1, 1000);
SELECT * FROM timestamp_test;
內容解密：
generate_series(1, 1000)產生1到1000的一系列整數。current_timestamp記錄事務開始的時間，因此所有行的current_timestamp_col值相同。clock_timestamp()記錄每一行的插入時間，因此clock_timestamp_col的值會隨著每一行插入而變化。
處理時區
在處理時間戳記時，瞭解時區是非常重要的。資料集可能不包含時區資訊，但如果知道所有事件發生在同一個地點，就可以將會話時區設定為該地點的時區，並將日期時間載入timestamptz欄位中。
查詢目前時區設定
要查詢目前的時區設定，可以使用SHOW timezone;或SELECT current_setting('timezone');。
SHOW timezone;
SELECT current_setting('timezone');
內容解密：
SHOW timezone;顯示目前的時區設定。current_setting('timezone')同樣顯示目前的時區設定，並且可以作為其他函式的輸入。
顯示時區名稱和縮寫
可以使用以下查詢來顯示所有的時區名稱、縮寫和UTC偏移量。
SELECT * FROM pg_timezone_abbrevs ORDER BY abbrev;
SELECT * FROM pg_timezone_names ORDER BY name;
內容解密：
pg_timezone_abbrevs檢視表包含時區縮寫和UTC偏移量。pg_timezone_names檢視表包含時區名稱、縮寫、UTC偏移量和是否正在實施夏令時的資訊。
設定時區
可以在會話層級使用SET TIME ZONE命令來設定時區。
SET TIME ZONE 'US/Pacific';
CREATE TABLE time_zone_test (
test_date timestamptz
);
INSERT INTO time_zone_test VALUES ('2023-01-01 4:00');
SELECT test_date FROM time_zone_test;
SET TIME ZONE 'US/Eastern';
SELECT test_date FROM time_zone_test;
SELECT test_date AT TIME ZONE 'Asia/Seoul' FROM time_zone_test;
內容解密：
SET TIME ZONE 'US/Pacific';將會話時區設定為太平洋時區。- 插入的時間戳記沒有指定時區，但由於欄位是
timestamptz型別，因此會被轉換為太平洋時區的時間。 - 當再次查詢該值並將會話時區改為東部時區時，時間戳記會根據新的時區進行調整。
- 使用
AT TIME ZONE語法可以將時間戳記轉換為特定的時區。
日期與時間處理：PostgreSQL 中的時區與計算
在處理日期與時間資料時，時區的正確處理至關重要。PostgreSQL 提供了多種資料型別來處理日期與時間，包括 timestamp 和 timestamptz，後者能夠根據時區進行調整。
時區的運作機制
當使用 timestamptz 資料型別儲存時間資訊時，PostgreSQL 會將時間轉換為 UTC 格式儲存。顯示時間時，則根據當前的時區設定進行轉換。
-- 設定時區為 US/Pacific
SET timezone = 'US/Pacific';
-- 建立一個包含時區的時間戳記
SELECT '2023-01-01 04:00:00-08'::timestamptz;
-- 將時區變更為 US/Eastern 並重新查詢
SET timezone = 'US/Eastern';
SELECT '2023-01-01 04:00:00-08'::timestamptz;
內容解密：
- 時區設定：使用
SET timezone命令變更當前工作階段的時區設定。 - 時間戳記轉換：當查詢
timestamptz欄位時，PostgreSQL 會根據當前時區設定顯示相應的時間。 - 輸出變化：在不同時區下查詢同一個
timestamptz值，會得到不同的時間表示，但底層儲存的 UTC 值保持不變。
使用 AT TIME ZONE 進行時區轉換
除了變更工作階段的時區設定外，還可以使用 AT TIME ZONE 語法直接將時間轉換為指定時區。
-- 將時間轉換為 Asia/Seoul 時區
SELECT '2023-01-01 04:00:00-08'::timestamptz AT TIME ZONE 'Asia/Seoul';
內容解密：
AT TIME ZONE語法：允許在查詢中直接將時間轉換為指定的時區。- 輸出結果：傳回指定時區下的時間表示，而不改變底層儲存的 UTC 值。
- 資料型別變化：若原始值為
timestamptz，輸出為timestamp；若原始值為timestamp，輸出為timestamptz。
日期與時間的計算
PostgreSQL 支援對日期與時間進行基本的數學運算，如加減乘除。
-- 計算兩個日期之間的差異
SELECT '1929-09-30'::date - '1929-09-27'::date;
-- 在日期上加上時間間隔
SELECT '1929-09-30'::date + '5 years'::interval;
內容解密：
- 日期減法：傳回兩個日期之間的天數差異。
- 日期加法：在指定日期上加上一個時間間隔，傳回新的日期或時間戳記。
分析紐約市計程車資料
利用紐約市計程車資料，可以實際練習日期與時間函式的使用。下載 nyc_yellow_taxi_trips.csv 資料集並匯入 PostgreSQL 資料函式庫中。
-- 建立表格並匯入資料
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,
-- 其他欄位定義
);
COPY nyc_yellow_taxi_trips (
vendor_id,
tpep_pickup_datetime,
tpep_dropoff_datetime,
-- 其他欄位名稱
)
FROM 'C:\YourDirectory\nyc_yellow_taxi_trips.csv'
WITH (FORMAT CSV, HEADER);
-- 在 tpep_pickup_datetime 欄位上建立索引
CREATE INDEX tpep_pickup_idx ON nyc_yellow_taxi_trips (tpep_pickup_datetime);
內容解密：
- 表格建立：根據 CSV 檔案的欄位定義建立對應的表格結構。
- 資料匯入：使用
COPY命令將 CSV 資料匯入至資料函式庫表格中。 - 索引建立：在
tpep_pickup_datetime欄位上建立索引，以提升查詢效能。
透過這些範例，可以深入瞭解 PostgreSQL 中日期與時間資料型別的處理方式，以及如何利用相關函式進行實際資料分析。
利用時間戳記分析紐約市計程車資料
在分析紐約市計程車資料時，我們首先注意到tpep_pickup_datetime和tpep_dropoff_datetime兩個欄位包含了時區資訊。時區偏移量為-4，這對應於東部時間夏季時區（Eastern Daylight Time）。為了確保結果的一致性，建議使用以下SQL命令設定時區：
SET TIME ZONE 'US/Eastern';
一天中最繁忙的時段
為了找出計程車最繁忙的時段，我們可以使用date_part()函式對tpep_pickup_datetime進行分析。以下查詢用於計算每個小時的乘車次數：
SELECT
date_part('hour', tpep_pickup_datetime) AS trip_hour,
count(*)
FROM nyc_yellow_taxi_trips
GROUP BY trip_hour
ORDER BY trip_hour;
內容解密：
date_part('hour', tpep_pickup_datetime)用於提取tpep_pickup_datetime的時部分量，以便按小時分組乘車次數。count(*)用於計算每個小時的乘車次數。- 結果顯示，紐約市計程車在晚上6點到10點之間達到乘車高峰，這可能是由於通勤和夏季晚間活動所致。
將資料匯出至CSV檔案以進行視覺化
為了更好地理解資料的模式，我們可以使用Microsoft Excel等工具對資料進行視覺化。首先，將查詢結果匯出至CSV檔案：
COPY
(
SELECT
date_part('hour', tpep_pickup_datetime) AS trip_hour,
count(*)
FROM nyc_yellow_taxi_trips
GROUP BY trip_hour
ORDER BY trip_hour
)
TO 'C:\YourDirectory\hourly_taxi_pickups.csv'
WITH (FORMAT CSV, HEADER);
內容解密：
COPY命令用於將查詢結果匯出至指定的CSV檔案。WITH (FORMAT CSV, HEADER)指定輸出格式為CSV，並且包含欄位名稱作為標頭。
什麼時候行程時間最長？
為了找出行程時間最長的時段，我們可以計算每個小時的中位行程時間。使用percentile_cont()函式可以實作這一點：
SELECT
date_part('hour', tpep_pickup_datetime) AS trip_hour,
percentile_cont(.5)
WITHIN GROUP (ORDER BY tpep_dropoff_datetime - tpep_pickup_datetime)
AS median_trip_time
FROM nyc_yellow_taxi_trips
GROUP BY trip_hour
ORDER BY trip_hour;
內容解密：
percentile_cont(.5)用於計算中位數，即按行程時間排序後的中間值。WITHIN GROUP (ORDER BY tpep_dropoff_datetime - tpep_pickup_datetime)指定了計算中位數的依據，即行程時間。- 結果顯示，下午1點的行程時間中位數最長，為15分鐘。
分析Amtrak資料中的模式
Amtrak是美國全國性的鐵路服務，提供多種跨國列車行程。我們可以使用Amtrak網站的資料建立一個表格，記錄每次行程的各個路段資訊，包括出發和到達的時間以及時區。
計算火車行程的持續時間
首先，建立一個表格來記錄All American路線的六個路段：
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
);
內容解密：
trip_id是自動生成的唯一識別碼，用於區分不同的行程。segment記錄了行程的各個路段。departure和arrival分別記錄了每個路段的出發和到達時間，並包含了時區資訊。