資料函式庫日期運算

不同資料函式庫系統的日期函式與語法存在差異，這讓跨平台的日期處理變得複雜。本文針對 DB2、Oracle、PostgreSQL、MySQL 和 SQL Server 等主流資料函式庫，比較了它們在日期加減、計算日期差、以及計算工作日數方面的不同方法。瞭解這些差異有助於開發者編寫更具效率且可移植的 SQL 查詢。文章中提供的程式碼範例涵蓋了常見的日期運算場景，方便開發者直接應用於實際專案。此外，圖表說明更清晰地展現了計算工作日數的流程，有助於理解其背後的邏輯。

日期運算

本章節將介紹如何進行簡單的日期運算，包括在日期上加減天數、月份和年份，以及計算兩個日期之間的天數差異等常見任務。

8.1 日期加減運算

問題描述

需要在某個日期上加或減去特定的天數、月份或年份。例如，使用員工 CLARK 的 HIREDATE，分別計算其前後 5 天、前後 5 個月和前後 5 年的日期。

解決方案

DB2

SELECT 
  hiredate - 5 DAY AS hd_minus_5D,
  hiredate + 5 DAY AS hd_plus_5D,
  hiredate - 5 MONTH AS hd_minus_5M,
  hiredate + 5 MONTH AS hd_plus_5M,
  hiredate - 5 YEAR AS hd_minus_5Y,
  hiredate + 5 YEAR AS hd_plus_5Y
FROM emp
WHERE deptno = 10;

內容解密：

• 使用 hiredate 直接進行加減運算，需指定時間單位（DAY、MONTH、YEAR）。
• 這種語法適用於 DB2 資料函式庫。

Oracle

SELECT 
  hiredate - 5 AS hd_minus_5D,
  hiredate + 5 AS hd_plus_5D,
  ADD_MONTHS(hiredate, -5) AS hd_minus_5M,
  ADD_MONTHS(hiredate, 5) AS hd_plus_5M,
  ADD_MONTHS(hiredate, -5 * 12) AS hd_minus_5Y,
  ADD_MONTHS(hiredate, 5 * 12) AS hd_plus_5Y
FROM emp
WHERE deptno = 10;

內容解密：

• Oracle 中，直接對日期進行加減整數代表加減天數。
• 使用 ADD_MONTHS 函式進行月份和年份的加減。
• 年份的加減是透過乘以 12 將年份轉換為月份來實作的。

PostgreSQL

SELECT 
  hiredate - INTERVAL '5 day' AS hd_minus_5D,
  hiredate + INTERVAL '5 day' AS hd_plus_5D,
  hiredate - INTERVAL '5 month' AS hd_minus_5M,
  hiredate + INTERVAL '5 month' AS hd_plus_5M,
  hiredate - INTERVAL '5 year' AS hd_minus_5Y,
  hiredate + INTERVAL '5 year' AS hd_plus_5Y
FROM emp
WHERE deptno = 10;

內容解密：

• 使用 INTERVAL 關鍵字來指定要加減的時間單位。
• 時間間隔值需用單引號括起來。

MySQL

SELECT 
  hiredate - INTERVAL 5 DAY AS hd_minus_5D,
  hiredate + INTERVAL 5 DAY AS hd_plus_5D,
  hiredate - INTERVAL 5 MONTH AS hd_minus_5M,
  hiredate + INTERVAL 5 MONTH AS hd_plus_5M,
  hiredate - INTERVAL 5 YEAR AS hd_minus_5Y,
  hiredate + INTERVAL 5 YEAR AS hd_plus_5Y
FROM emp
WHERE deptno = 10;

內容解密：

• MySQL 也使用 INTERVAL，但不需單引號。

SQL Server

SELECT 
  DATEADD(DAY, -5, hiredate) AS hd_minus_5D,
  DATEADD(DAY, 5, hiredate) AS hd_plus_5D,
  DATEADD(MONTH, -5, hiredate) AS hd_minus_5M,
  DATEADD(MONTH, 5, hiredate) AS hd_plus_5M,
  DATEADD(YEAR, -5, hiredate) AS hd_minus_5Y,
  DATEADD(YEAR, 5, hiredate) AS hd_plus_5Y
FROM emp
WHERE deptno = 10;

內容解密：

• 使用 DATEADD 函式進行日期加減運算，需指定時間單位和增減量。

圖表翻譯：各資料函式庫日期加減語法比較

圖表翻譯： 此圖示呈現了不同資料函式庫系統（DB2、Oracle、PostgreSQL、MySQL 和 SQL Server）中進行日期加減運算的語法差異。每種資料函式庫都有其特定的函式或關鍵字來支援日期運算，例如 DB2 的直接加減並指定單位，Oracle 的 ADD_MONTHS 函式，以及 PostgreSQL 和 MySQL 的 INTERVAL關鍵字等。這種比較有助於開發者在不同資料函式庫系統之間切換時快速理解和應用相應的日期運算方法。

日期計算：計算兩個日期之間的天數與工作日數

計算兩個日期之間的天數

在資料函式倉管理中，計算兩個日期之間的天數是一項常見任務。不同資料函式庫系統提供了不同的函式來實作這一功能。以下將介紹如何在MySQL和SQL Server中計算兩個日期之間的天數。

MySQL與SQL Server的DATEDIFF函式

MySQL和SQL Server都提供了DATEDIFF函式用於計算兩個日期之間的天數。然而，這兩個資料函式庫系統的DATEDIFF函式在使用上存在一些差異。

• MySQL的DATEDIFF函式：只需要兩個日期引數，並且應該將較小的日期放在前面以避免傳回負值。

SELECT DATEDIFF(ward_hd, allen_hd) FROM ( SELECT hiredate AS ward_hd FROM emp WHERE ename = ‘WARD’ ) x, ( SELECT hiredate AS allen_hd FROM emp WHERE ename = ‘ALLEN’ ) y;

- **SQL Server的DATEDIFF函式**：允許指定傳回值的單位（例如，天、月、年）。在這個例子中，我們指定傳回兩個日期之間的天數差。
  ```sql
SELECT DATEDIFF(day, allen_hd, ward_hd)
FROM (
  SELECT hiredate AS ward_hd
  FROM emp
  WHERE ename = 'WARD'
) x,
(
  SELECT hiredate AS allen_hd
  FROM emp
  WHERE ename = 'ALLEN'
) y;

邏輯解析

無論是在MySQL還是在SQL Server中，上述查詢都使用了內聯檢視（inline views）X和Y來分別檢索員工WARD和ALLEN的聘用日期（HIREDATE）。這兩個內聯檢視透過笛卡爾積（Cartesian product）結合在一起，因為沒有指定任何連線條件。但是，由於X和Y都只有一行資料，所以結果集最終也只有一行資料。

計算兩個日期之間的工作日數

在許多實際應用中，我們不僅需要計算兩個日期之間的所有天數，還需要知道這段時間內的工作日數（排除週末）。以下介紹如何在不同的資料函式庫系統中實作這一功能。

使用T500輔助表

多數資料函式庫系統的解決方案都涉及使用一個輔助表（pivot table），如T500，來生成兩個日期之間的所有日期。然後，透過檢查這些日期是否為週末來統計工作日數。

• DB2、MySQL、Oracle、PostgreSQL及SQL Server的通用邏輯：
  1. 使用T500表生成兩個日期之間的所有日期。
  2. 對每個生成的日期，使用相應的函式（如DAYNAME、DATE_FORMAT、TO_CHAR、DATENAME）來判斷該日期是星期幾。
  3. 如果該日期不是週末（星期六或星期日），則計為一個工作日。

程式碼範例

以下是各資料函式庫系統的具體實作範例，用於計算員工BLAKE和JONES的聘用日期之間的工作日數：

• DB2：

SELECT SUM(CASE WHEN DAYNAME(jones_hd + t500.id DAY - 1 DAY) IN (‘Saturday’, ‘Sunday’) THEN 0 ELSE 1 END) AS days FROM ( SELECT MAX(CASE WHEN ename = ‘BLAKE’ THEN hiredate END) AS blake_hd, MAX(CASE WHEN ename = ‘JONES’ THEN hiredate END) AS jones_hd FROM emp WHERE ename IN (‘BLAKE’, ‘JONES’) ) x, T500 WHERE t500.id <= blake_hd - jones_hd + 1;

- **MySQL**：
  ```sql
SELECT SUM(CASE WHEN DATE_FORMAT(DATE_ADD(jones_hd, INTERVAL t500.id - 1 DAY), '%a') IN ('Sat', 'Sun') THEN 0 ELSE 1 END) AS days
FROM (
  SELECT MAX(CASE WHEN ename = 'BLAKE' THEN hiredate END) AS blake_hd,
         MAX(CASE WHEN ename = 'JONES' THEN hiredate END) AS jones_hd
  FROM emp
  WHERE ename IN ('BLAKE', 'JONES')
) x,
T500
WHERE t500.id <= DATEDIFF(blake_hd, jones_hd) + 1;

程式碼解析

這些查詢首先使用內聯檢視檢索員工BLAKE和JONES的聘用日期。然後，透過與T500表的笛卡爾積生成這兩個日期之間的所有日期。最後，利用條件聚合函式統計出非週末的天數，即工作日數。

圖表說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫日期運算

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示展示了計算兩個日期之間工作日數的流程。首先確定開始和結束日期，然後生成這兩個日期之間的所有日期。接著，逐一判斷這些日期是否為週末。如果是週末，則不計入工作日；如果不是，則計入工作日。最後，統計所有工作日的總數，得出結果。

計算兩個日期之間的工作天數

在不同的關聯式資料函式倉管理系統（RDBMS）中，雖然需要使用不同的內建函式來計算工作天數，但整體的解決方案是相同的。解決方案可以分為兩個步驟：

1. 計算起始日期和結束日期之間的所有日期（包含起始和結束日期）。
2. 排除週末，計算工作天數。

步驟解析

內嵌檢視（Inline View）X 執行了第一步。如果檢查內嵌檢視 X，會注意到使用了聚合函式 MAX 來去除 NULL 值。如果 MAX 的使用方式不清楚，可以參考以下輸出，這有助於理解。

SQL 範例（Oracle 語法）

select x.*, t500.*, jones_hd+t500.id-1
from (
  select max(case when ename = 'BLAKE'
                  then hiredate
             end) as blake_hd,
         max(case when ename = 'JONES'
                  then hiredate
             end) as jones_hd
    from emp
   where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id <= blake_hd-jones_hd+1

內容解密：

• 首先，使用內嵌檢視 X 從 emp 表中取出 ‘BLAKE’ 和 ‘JONES’ 的僱用日期。
• 使用 MAX 聚合函式確保結果集中只包含一行資料，並且去除了 NULL 值。
• 將結果與 t500 表進行聯接，t500 表用於生成一系列連續的數字，從而生成從 JONES_HD 到 BLAKE_HD 的所有日期。
• 在 WHERE 子句中，透過比較 t500.id 和兩個日期之間的差值（加 1），來確保生成的日期數量正確。
• 在 SELECT 清單中，透過將 t500.id 加到 JONES_HD 上（並減去 1），生成連續的日期。

不同 RDBMS 的實作方法

Oracle

使用 MONTHS_BETWEEN 函式來計算兩個日期之間相差的月數，並除以 12 得到年數。

select months_between(max_hd,min_hd),
       months_between(max_hd,min_hd)/12
  from (
    select min(hiredate) min_hd, max(hiredate) max_hd
      from emp
  ) x

內容解密：

• 使用子查詢找出 emp 表中最早和最晚的僱用日期。
• 使用 MONTHS_BETWEEN 函式計算這兩個日期之間的月數。
• 將結果除以 12 得到年數。

DB2 和 MySQL

使用 YEAR 和 MONTH 函式來提取日期中的年和月，並進行計算。

select mnth, mnth/12
  from (
    select (year(max_hd) - year(min_hd))*12 +
           (month(max_hd) - month(min_hd)) as mnth
      from (
        select min(hiredate) as min_hd, max(hiredate) as max_hd
          from emp
      ) x
  ) y

內容解密：

• 首先，使用子查詢找出最早和最晚的僱用日期。
• 然後，計算這兩個日期之間的年差和月差，並將其轉換為總月數。
• 最後，將總月數除以 12 得到年數。

PostgreSQL

使用 EXTRACT 函式來提取日期中的年和月。

select mnth, mnth/12
  from (
    select (extract(year from max_hd) -
            extract(year from min_hd)) * 12 +
           (extract(month from max_hd) -
            extract(month from min_hd)) as mnth
      from (
        select min(hiredate) as min_hd, max(hiredate) as max_hd
          from emp
      ) x
  ) y

內容解密：

• 使用 EXTRACT 函式從日期中提取年和月。
• 計算兩個日期之間的總月數，並將其除以 12 得到年數。

SQL Server

使用 DATEDIFF 函式來計算兩個日期之間的差值。

select datediff(month,min_hd,max_hd),
       datediff(year,min_hd,max_hd)
  from (
    select min(hiredate) min_hd, max(hiredate) max_hd
      from emp
  ) x

內容解密：

• 使用子查詢找出最早和最晚的僱用日期。
• 使用 DATEDIFF 函式計算這兩個日期之間的月數和年數。

日期計算：計算兩個日期之間的天數、月數和年數

在進行日期計算時，瞭解如何計算兩個日期之間的天數、月數和年數非常重要。本章節將介紹如何在不同的資料函式庫系統中（例如DB2、MySQL、SQL Server、Oracle和PostgreSQL）進行這些計算。

計算兩個日期之間的天數

首先，我們來看如何計算兩個日期之間的天數。這可以透過簡單地對兩個日期進行減法運算來實作。

Oracle和PostgreSQL

在Oracle和PostgreSQL中，您可以直接對日期進行減法運算來獲得天數差。

SELECT (DATE '2023-01-15' - DATE '2023-01-01') AS days_diff
FROM dual;

內容解密：

• 上述查詢計算了兩個日期（‘2023-01-15’和'2023-01-01’）之間的天數差。
• 在Oracle中，DUAL表是一個特殊的表，用於在不涉及實際表的情況下進行查詢。
• 日期減法直接傳回天數。

MySQL

在MySQL中，可以使用DATEDIFF函式。

SELECT DATEDIFF('2023-01-15', '2023-01-01') AS days_diff;

內容解密：

• DATEDIFF函式用於計算兩個日期之間的天數差。
• 函式的第一個引數是結束日期，第二個引數是開始日期。

SQL Server

在SQL Server中，也可以使用DATEDIFF函式，但需要指定間隔單位為day。

SELECT DATEDIFF(DAY, '2023-01-01', '2023-01-15') AS days_diff;

內容解密：

• DATEDIFF函式的第一個引數指定了間隔單位，在這裡是DAY，表示計算天數差。
• 第二個引數是開始日期，第三個引數是結束日期。

計算兩個日期之間的月數和年數

接下來，我們討論如何計算兩個日期之間的月數和年數。

Oracle

在Oracle中，可以使用MONTHS_BETWEEN函式來計算兩個日期之間的月數。

SELECT MONTHS_BETWEEN(DATE '2023-01-15', DATE '2023-01-01') AS months_diff
FROM dual;

內容解密：

• MONTHS_BETWEEN函式計算了兩個日期之間的月數差。
• 結果可能包含小數部分，表示不足一個月的部分。

要計算年數，可以將月數除以12。

SELECT MONTHS_BETWEEN(DATE '2023-06-15', DATE '2023-01-01') / 12 AS years_diff
FROM dual;

內容解密：

• 將月數除以12即可得到年數差。

SQL Server

在SQL Server中，DATEDIFF函式同樣可以用於計算月數和年數，方法是改變第一個引數為MONTH或YEAR。

SELECT DATEDIFF(MONTH, '2023-01-01', '2023-06-15') AS months_diff;
SELECT DATEDIFF(YEAR, '2022-01-01', '2023-06-15') AS years_diff;

內容解密：

• 使用DATEDIFF函式，透過指定不同的間隔單位，可以直接計算出月數或年數差。

統計一年中每週的出現次數

要統計一年中每週的出現次數，需要生成一年中的所有日期，然後格式化這些日期以取得它們對應的星期名稱，最後統計每個星期名稱的出現次數。

Oracle

在Oracle中，可以使用遞迴查詢（CONNECT BY）生成一年中的所有日期。

WITH x AS (
  SELECT LEVEL lvl
  FROM dual
  CONNECT BY LEVEL <= (ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y'))
)
SELECT TO_CHAR(TRUNC(SYSDATE, 'y') + lvl - 1, 'DAY'), COUNT(*)
FROM x
GROUP BY TO_CHAR(TRUNC(SYSDATE, 'y') + lvl - 1, 'DAY');

內容解密：

1. 使用遞迴查詢生成一年中的每一天。
2. TRUNC(SYSDATE, 'y')用於取得當前年的第一天。
3. ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12)用於計算下一年第一天的日期，從而確定當前年的天數。
4. TO_CHAR(date, 'DAY')將日期轉換為對應的星期名稱。
5. 最後按照星期名稱分組統計出現次數。

本章節介紹瞭如何在不同資料函式庫系統中進行日期相關的計算，包括計算兩個日期之間的天數、月數和年數，以及統計一年中每週的出現次數。這些操作對於資料分析和處理具有重要意義。