時間資料的四捨五入與截斷： DATE_TRUNC()

DATE_TRUNC() 是個很強大的工具，可以讓你把時間值「截斷」到某個特定的時間單位。比如說，你可以把一個 timestamp 四捨五入到一天、一個月、一年、一個小時之類的開頭。這在你要分析一段期間的資料時（像是要把訂單依照天、月或年分組）特別有用。

你可以把日期和時間想像成一條很長的字串，裡面有小時、分鐘、秒。DATE_TRUNC() 這個 function 就是把這條字串「剪掉」多餘的部分，只留下你要的那一段。舉例來說：

• 你想把 2023-10-01 15:30:45 這個日期截到一天的開頭，結果會是 2023-10-01 00:00:00。
• 或者你只想留下這個小時的第一秒，就是 2023-10-01 15:00:00。

語法

DATE_TRUNC() 的語法長這樣：

DATE_TRUNC(field, source)

• field —— 你想「截斷」到哪個時間單位。像是 year、month、day、hour、minute。
• source —— 你要截斷的時間值。可以是 TIMESTAMP 欄位，也可以是其他 function 的結果，像 NOW()。

簡單的呼叫範例：

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- 結果：2023-10-01 00:00:00

支援的欄位

這裡有一些 DATE_TRUNC() 可以用的時間單位：

時間單位越小，截斷的結果就越精確。對了，週是從禮拜天開始的喔 :)

DATE_TRUNC() 的使用範例

截到一天的開頭。 這個例子我們把一個 timestamp 四捨五入到一天的開頭：

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- 結果：2023-10-01 00:00:00

截到一個月的開頭。 現在我們把日期截到一個月的開頭：

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- 結果：2023-10-01 00:00:00

截到一年的開頭。 來試試看把日期四捨五入到一年的開頭：

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- 結果：2023-01-01 00:00:00

搭配現在時間 (NOW()) 使用。 如果你想要一直用現在的日期和時間，可以把 DATE_TRUNC() 跟 NOW() 結合：

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- 結果會依照現在時間變動，例如：2023-10-01 15:00:00

依月份分組訂單。 來個實用一點的例子。假設我們有一個訂單的資料表，每筆資料都有訂單日期。我們想要算每個月有幾筆訂單：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

結果：

實用的使用情境

依期間分析時間資料：想知道每年、每月或每天有多少用戶註冊？用 DATE_TRUNC() 來分組資料就對了。

做報表：把 timestamp 四捨五入到對的單位，報表看起來會更清楚。

比較日期和時間：如果你的時間資料很精細（像有毫秒），可以先截到你要的等級再來比，才不會出錯。

用 DATE_TRUNC() 常見的錯誤

用到不支援的欄位。 比如說 millisecond 這個欄位不支援，用了會直接報錯。

資料型態不對。 DATE_TRUNC() 只能用在時間型態的資料，像 TIMESTAMP。如果你丟給它一個字串，會直接出錯。

四捨五入錯誤。 要記得 DATE_TRUNC() 永遠是把時間「截」到指定單位的開頭。如果你想要真正的四捨五入，可能要用別的方法。