資料函式庫日期處理技巧

在資料函式庫應用中，日期時間的處理是不可或缺的一環。各家資料函式庫系統都提供了各自的函式與方法來處理日期時間資料，瞭解這些差異並善用這些函式，能提升資料處理效率。本文將介紹如何在常見的資料函式庫系統中進行日期處理，例如閏年判斷、計算一年中的天數、以及從日期中提取時間單位等。不同資料函式庫系統的日期函式略有不同，本文將提供對應的 SQL 語法和說明，方便讀者參考與應用。

如何判斷某年是否為閏年及計算一年中的天數

閏年的判斷方法

判斷某年是否為閏年的問題，可以透過多種資料函式倉管理系統（DBMS）來實作。以下將介紹在不同DBMS中如何解決這個問題。

DB2

在DB2中，首先需要找到當前日期是當年的第幾天，然後透過一系列日期運算來得到二月的最後一天。

with x (dy, mth) as (
  select dy, month(dy)
  from (
    select (current_date - dayofyear(current_date) days + 1 days) + 1 months as dy
    from t1
  ) tmp1
  union all
  select dy + 1 days, mth
  from x
  where month(dy + 1 day) = mth
)
select max(dy) as last_day_of_february
from x;

內容解密：

首先，使用DAYOFYEAR函式計算當前日期是當年的第幾天。
然後，減去這個天數並加1，得到當年的第一天（1月1日）。
再加1個月，得到二月的第一天。
使用遞迴查詢（WITH子句）來生成二月的所有日期。
最後，使用MAX函式找出二月的最後一天。

Oracle

在Oracle中，可以使用TRUNC和ADD_MONTHS函式來簡化這個過程。

select last_day(add_months(trunc(sysdate, 'y'), 1)) as last_day_of_february
from t1;

內容解密：

使用TRUNC(sysdate, 'y')得到當年的第一天。
使用ADD_MONTHS函式加1個月，得到二月的第一天。
使用LAST_DAY函式得到二月的最後一天。

PostgreSQL

在PostgreSQL中，可以使用DATE_TRUNC和GENERATE_SERIES函式。

select max(tmp2.dy + x.id) as last_day_of_february
from (
  select dy, to_char(dy, 'MM') as mth
  from (
    select cast(cast(date_trunc('year', current_date) as date) + interval '1 month' as date) as dy
    from t1
  ) tmp1
) tmp2, generate_series(0, 29) x(id)
where to_char(tmp2.dy + x.id, 'MM') = tmp2.mth;

內容解密：

使用DATE_TRUNC函式得到當年的第一天。
加1個月，得到二月的第一天。
使用GENERATE_SERIES函式生成一系列數字（0到29），並將這些數字加到二月的第一天上，以生成二月的所有日期。
使用MAX函式找出二月的最後一天。

MySQL

在MySQL中，可以使用DATE_ADD和LAST_DAY函式。

select last_day(
  date_add(
    date_add(
      date_add(current_date, interval -dayofyear(current_date) day),
      interval 1 day),
    interval 1 month)
) as last_day_of_february
from t1;

內容解密：

使用DAYOFYEAR函式計算當前日期是當年的第幾天，並減去這個天數，然後加1，得到當年的第一天。
加1個月，得到二月的第一天。
使用LAST_DAY函式得到二月的最後一天。

一年中的天數計算

計算一年中的天數可以透過找出下一年第一天和今年第一天的差值來實作。

DB2

select days(add_months(trunc(current_date, 'y'), 12)) - days(trunc(current_date, 'y')) as days_in_year
from t1;

內容解密：

使用TRUNC(current_date, 'y')得到當年的第一天。
加12個月，得到下一年的第一天。
使用DAYS函式計算這兩個日期之間的差值，得到一年中的天數。

其他DBMS的實作方式類別似，主要差異在於使用的日期函式不同。這裡以DB2為例進行了詳細說明，其他DBMS的具體實作請參照上述範例及對應的內容解密。

日期處理中的年天數計算與時間單位擷取

計算一年中的天數

在多數資料函式倉管理系統中，計算一年中的天數是一項常見需求。不同資料函式庫系統提供了各自的函式與方法來實作這一功能。

DB2 的實作方式

DB2 提供了簡潔的日期運算功能。以下 SQL 陳述式展示瞭如何計算當年的天數：

select days((curr_year + 1 year)) - days(curr_year)
from (
  select (current_date - dayofyear(current_date) day + 1 day) curr_year
  from t1
) x

內容解密：

內層查詢：首先找出當年的第一天。dayofyear(current_date) 函式傳回當前日期在當年中的第幾天。將這個值從 current_date 中減去並加 1，即可得到當年的第一天。
外層查詢：將當年第一天的日期加一年得到次年第一天，然後計算這兩個日期之間的天數差。

Oracle 的實作方式

Oracle 使用 TRUNC 函式來取得當年第一天，並使用 ADD_MONTHS 函式來計算次年第一天：

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
from dual

內容解密：

TRUNC(sysdate,'y')：將當前日期截斷至當年第一天。
add_months(...,12)：在當年第一天的基礎上加 12 個月，即得到次年第一天。
日期相減：計算次年第一天與當年第一天之間的天數差。

PostgreSQL 的實作方式

PostgreSQL 使用 DATE_TRUNC 函式來取得當年第一天，並利用區間運算來計算次年第一天：

select cast((curr_year + interval '1 year') as date) - curr_year
from (
  select cast(date_trunc('year',current_date) as date) as curr_year
  from t1
) x

內容解密：

date_trunc('year',current_date)：將當前日期截斷至當年第一天。
區間運算：在當年第一天的基礎上加一年得到次年第一天，然後進行日期相減。

MySQL 的實作方式

MySQL 使用 ADDDATE 和 DATEDIFF 函式來實作相同的功能：

select datediff((curr_year + interval 1 year),curr_year)
from (
  select adddate(current_date,-dayofyear(current_date)+1) curr_year
  from t1
) x

內容解密：

adddate(current_date,-dayofyear(current_date)+1)：計算當年第一天。
datediff：計算次年第一天與當年第一天之間的天數差。

SQL Server 的實作方式

SQL Server 使用 DATEADD 和 DATEDIFF 函式來計算當年的天數：

select datediff(d,curr_year,dateadd(yy,1,curr_year))
from (
  select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year
  from t1
) x

內容解密：

dateadd(d,-datepart(dy,getdate())+1,getdate())：計算當年第一天。
datediff：計算次年第一天與當年第一天之間的天數差。

從日期中擷取時間單位

大多數資料函式庫廠商現在都採用 ANSI 標準的 EXTRACT 函式來從日期中擷取各個時間單位，但 SQL Server 除外。

DB2 的實作方式

DB2 提供了一系列內建函式，如 HOUR、MINUTE、SECOND、DAY、MONTH 和 YEAR，用於擷取日期中的各個部分：

select 
  extract(hour from current_timestamp) hr,
  extract(minute from current_timestamp) min,
  extract(second from current_timestamp) sec,
  extract(day from current_timestamp) dy,
  extract(month from current_timestamp) mth,
  extract(year from current_timestamp) yr
from t1

內容解密：

EXTRACT 函式：用於從 current_timestamp 中擷取小時、分鐘、秒、天、月和年等資訊。

Oracle 的實作方式

Oracle 使用 TO_CHAR 和 TO_NUMBER 函式來擷取日期中的各個時間單位：

select 
  to_number(to_char(sysdate,'hh24')) hour,
  to_number(to_char(sysdate,'mi')) min,
  to_number(to_char(sysdate,'ss')) sec,
  to_number(to_char(sysdate,'dd')) day,
  to_number(to_char(sysdate,'mm')) mth,
  to_number(to_char(sysdate,'yyyy')) year
from dual

內容解密：

TO_CHAR 函式：將日期格式化為字串。
TO_NUMBER 函式：將字串轉換為數字。

日期處理中的時間單位提取與月份首尾日期計算

在資料函式倉管理中，處理日期與時間是一項基本且重要的任務。不同的資料函式庫系統提供了多樣化的函式來幫助開發者從日期中提取特定的時間單位，或是計算某個月份的第一天和最後一天。本篇文章將介紹如何在常見的資料函式庫系統中（包括 PostgreSQL、MySQL、SQL Server、DB2 和 Oracle）完成這些任務。

從日期中提取時間單位

PostgreSQL

在 PostgreSQL 中，可以使用 TO_CHAR 和 TO_NUMBER 函式來提取時間單位。例如：

SELECT 
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'hh24'), '99') AS hr,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'mi'), '99') AS min,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'ss'), '99') AS sec,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'dd'), '99') AS day,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'mm'), '99') AS mth,
    TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'yyyy'), '9999') AS yr
FROM t1;

內容解密：

TO_CHAR(CURRENT_TIMESTAMP, 'hh24')：將目前時間轉換為24小時制的時。
TO_NUMBER(..., '99')：將字元轉換為數字，格式為兩位數。
依此類別推，提取分鐘、秒、天、月、年等時間單位。

MySQL

MySQL 使用 DATE_FORMAT 函式來提取時間單位。例如：

SELECT 
    DATE_FORMAT(CURRENT_TIMESTAMP, '%k') AS hr,
    DATE_FORMAT(CURRENT_TIMESTAMP, '%i') AS min,
    DATE_FORMAT(CURRENT_TIMESTAMP, '%s') AS sec,
    DATE_FORMAT(CURRENT_TIMESTAMP, '%d') AS dy,
    DATE_FORMAT(CURRENT_TIMESTAMP, '%m') AS mon,
    DATE_FORMAT(CURRENT_TIMESTAMP, '%Y') AS yr
FROM t1;

內容解密：

DATE_FORMAT(CURRENT_TIMESTAMP, '%k')：將目前時間格式化為小時（24小時制）。
DATE_FORMAT(CURRENT_TIMESTAMP, '%i')：提取分鐘。
其他格式符號如 %s（秒）、%d（天）、%m（月）、%Y（年）用於提取相應的時間單位。

SQL Server

SQL Server 使用 DATEPART 函式來提取時間單位。例如：

SELECT 
    DATEPART(HOUR, GETDATE()) AS hr,
    DATEPART(MINUTE, GETDATE()) AS min,
    DATEPART(SECOND, GETDATE()) AS sec,
    DATEPART(DAY, GETDATE()) AS dy,
    DATEPART(MONTH, GETDATE()) AS mon,
    DATEPART(YEAR, GETDATE()) AS yr
FROM t1;

內容解密：

DATEPART(HOUR, GETDATE())：提取目前日期時間的小時部分。
DATEPART(MINUTE, GETDATE())：提取分鐘部分。
其他引數如 SECOND、DAY、MONTH、YEAR用於提取相應的時間單位。

計算某個月份的第一天和最後一天

DB2

DB2 中，可以透過 DAY 函式和日期運算來計算。例如：

SELECT 
    (DATE(CURRENT_DATE) - DAY(DATE(CURRENT_DATE)) DAY + 1 DAY) AS firstday,
    (DATE(CURRENT_DATE) + 1 MONTH - DAY(DATE(CURRENT_DATE) + 1 MONTH) DAY) AS lastday
FROM t1;

內容解密：

計算第一天：先取得目前日期的天數，再從目前日期減去該天數並加1。
計算最後一天：先將目前日期加一個月，再減去該月的天數。

Oracle

Oracle 使用 TRUNC 和 LAST_DAY 函式。例如：

SELECT 
    TRUNC(SYSDATE, 'mm') AS firstday,
    LAST_DAY(SYSDATE) AS lastday
FROM dual;

內容解密：

TRUNC(SYSDATE, 'mm')：將目前日期截斷至該月的第一天。
LAST_DAY(SYSDATE)：取得目前日期所在月的最後一天。

PostgreSQL

PostgreSQL 使用 DATE_TRUNC 函式。例如：

SELECT 
    firstday,
    CAST(firstday + INTERVAL '1 month' - INTERVAL '1 day' AS DATE) AS lastday
FROM (
    SELECT CAST(DATE_TRUNC('month', CURRENT_DATE) AS DATE) AS firstday
    FROM t1
) x;

內容解密：

DATE_TRUNC('month', CURRENT_DATE)：截斷至該月的第一天。
計算最後一天：在第一天的基礎上加一個月再減一天。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。