SQL日期時間函式應用與轉換

在資料函式庫應用中，處理日期和時間資料是不可或缺的一部分。不同資料函式庫系統提供的日期時間函式各有差異，瞭解這些函式的用法對於提升開發效率至關重要。本文將探討如何在常見的資料函式庫系統中，例如 MySQL、Oracle、PostgreSQL、SQL Server 和 SQLite，有效地運用日期時間函式進行資料處理和分析，包含日期差異計算、日期時間組成部分提取、星期幾的判斷、日期的四捨五入和截斷，以及字串與日期時間型別的相互轉換。這些技巧將幫助開發者更精確地處理時間相關資料，並提升資料函式庫操作的效率。

日期與時間函式的應用與轉換

在資料函式倉管理系統（DBMS）中，日期與時間的處理是常見且重要的任務。不同DBMS對日期與時間的處理方式各有不同，本文將介紹如何在MySQL、Oracle、PostgreSQL、SQL Server及SQLite中進行日期與時間的操作。

計算日期差異

計算兩個日期之間的差異是常見的需求。不同DBMS提供了不同的函式來實作這一功能。

PostgreSQL中的日期差異計算

在PostgreSQL中，可以使用AGE函式來計算兩個日期之間的差異。

SELECT AGE(end_dt, start_dt)
FROM my_table;

輸出結果可能如下：

age
---
-
---
-
---
-
---
-
---
-
---
-
---
---
4 years 1 mon 1 day 01:00:00
2 years 1 mon 1 day 00:32:13

若只需提取年份，可以使用EXTRACT函式。

SELECT EXTRACT(year FROM AGE(end_dt, start_dt))
FROM my_table;

輸出結果可能如下：

date_part
---
-
---
-
---
4
2

不同DBMS的日期差異計算方法

DBMS	計算日期差異的方法
MySQL	`TIMESTAMPDIFF` 或 `DATEDIFF`
Oracle	直接相減或使用`MONTHS_BETWEEN`
PostgreSQL	`AGE` 或直接相減
SQL Server	`DATEDIFF`
SQLite	直接相減

提取日期或時間的部分

不同DBMS提供了多種方法來提取日期或時間的部分，例如月份、年份等。

提取月份的範例

DBMS	提取月份的SQL陳述式
MySQL	`SELECT EXTRACT(month FROM CURRENT_DATE);` 或 `SELECT MONTH(CURRENT_DATE);`
Oracle	`SELECT EXTRACT(month FROM CURRENT_DATE) FROM dual;`
PostgreSQL	`SELECT EXTRACT(month FROM CURRENT_DATE);`
SQL Server	`SELECT DATEPART(month, CURRENT_TIMESTAMP);`
SQLite	`SELECT strftime('%m', CURRENT_DATE);`

取得星期幾

對於給定的日期，可以使用不同的方法來取得其對應的星期幾。

取得星期幾的範例

DBMS	取得星期幾的SQL陳述式（數字）	取得星期幾的SQL陳述式（名稱）
MySQL	`SELECT DAYOFWEEK('2020-03-16');`	`SELECT DAYNAME('2020-03-16');`
Oracle	`SELECT TO_CHAR(date '2020-03-16', 'd') FROM dual;`	`SELECT TO_CHAR(date '2020-03-16', 'day') FROM dual;`
PostgreSQL	`SELECT DATE_PART('dow', date '2020-03-16');`	`SELECT TO_CHAR(date '2020-03-16', 'day');`
SQL Server	`SELECT DATEPART(weekday, '2020-03-16');`	`SELECT DATENAME(weekday, '2020-03-16');`
SQLite	`SELECT strftime('%w', '2020-03-16');`	不支援

日期四捨五入或截斷

Oracle和PostgreSQL支援日期的四捨五入和截斷操作。

Oracle中的日期截斷範例

-- 截斷到月份的第一天
SELECT TRUNC(date '2020-02-25', 'month') FROM dual;
-- 結果：01-FEB-20

-- 四捨五入到最近的月份
SELECT ROUND(date '2020-02-25', 'month') FROM dual;
-- 結果：01-MAR-20

PostgreSQL中的日期截斷範例

-- 截斷到月份的第一天
SELECT DATE_TRUNC('month', DATE '2020-02-25');
-- 結果：2020-02-01 00:00:00-06

-- 截斷到分鐘
SELECT DATE_TRUNC('minute', TIME '10:30:59.12345');
-- 結果：10:30:00

字串轉換為日期或時間型別

將字串轉換為日期或時間型別是常見的需求。不同DBMS提供了不同的函式來實作這一轉換。

使用CAST函式進行轉換的範例

DBMS	日期格式	SQL陳述式（日期）	時間格式	SQL陳述式（時間）
MySQL, PostgreSQL, SQL Server	YYYY-MM-DD	`SELECT CAST('2020-10-15' AS DATE);`	hh:mm:ss	`SELECT CAST('14:30' AS TIME);`
Oracle	DD-MON-YYYY	`SELECT CAST('15-OCT-2020' AS DATE) FROM dual;`	hh:mm:ss AM/PM	`SELECT CAST('02:30:00 PM' AS TIME) FROM dual;`
SQLite	YYYY-MM-DD	`SELECT DATE('2020-10-15');`	hh:mm:ss	`SELECT TIME('14:30');`

除了使用CAST函式外，不同DBMS還提供了其他函式，如MySQL的STR_TO_DATE、Oracle的TO_DATE、SQL Server的CONVERT等，用於處理非標準格式的字串轉換。

不同DBMS的字串轉換函式對照表

DBMS	日期轉換函式
MySQL	`STR_TO_DATE`
Oracle	`TO_DATE`
SQL Server	`CONVERT` 或 `PARSE`

日期和時間函式的應用與轉換

在處理非標準格式的日期和時間資料時，資料函式倉管理系統（RDBMS）提供了多種函式來進行轉換和處理。本文將詳細介紹如何在不同的 RDBMS 中使用這些函式，以及如何應用它們來解決實際問題。

日期和時間轉換函式

不同的 RDBMS 提供了不同的函式來處理日期和時間的轉換。以下是常見的 RDBMS 及其對應的轉換函式：

MySQL

使用 STR_TO_DATE 函式將字串轉換為日期。
使用 STR_TO_DATE 或其他相關函式處理時間。

SELECT STR_TO_DATE('10-15-22', '%m-%d-%y');
SELECT STR_TO_DATE('1030', '%H%i');

Oracle

使用 TO_DATE 函式將字串轉換為日期。
使用 TO_TIMESTAMP 函式處理時間。

SELECT TO_DATE('10-15-22', 'MM-DD-YY') FROM dual;
SELECT TO_TIMESTAMP('1030', 'HH24MI') FROM dual;

PostgreSQL

使用 TO_DATE 函式將字串轉換為日期。
使用 TO_TIMESTAMP 函式處理時間。

SELECT TO_DATE('10-15-22', 'MM-DD-YY');
SELECT TO_TIMESTAMP('1030', 'HH24MI');

SQL Server

使用 CONVERT 函式進行日期轉換。
使用 CAST 和 CONCAT 函式處理時間。

SELECT CONVERT(VARCHAR, '10-15-22', 105);
SELECT CAST(CONCAT(10,':',30) AS TIME);

SQLite

SQLite 對於非標準日期格式沒有直接的轉換函式，但可以使用 SUBSTR 等字串處理函式進行間接處理。

SELECT SUBSTR(str_column, 7) FROM my_table;

日期格式符號

不同的 RDBMS 使用不同的格式符號來表示日期和時間。以下是常見的格式符號：

| 符號 | MySQL | Oracle 和 PostgreSQL | 描述 | |

|

| | %Y / YYYY | 是 | 是 | 四位數年份 | | %y / YY | 是 | 是 | 兩位數年份 | | %m / MM | 是 | 是 | 月份（1-12） | | %b / MON | 是 | 是 | 月份縮寫（Jan - Dec） | | %M / MONTH | 是 | 是 | 月份全名（January - December） | | %d / DD | 是 | 是 | 日期（1-31） | | %h / HH 或 HH12 | 是 | 是 | 12小時制時間（1-12） | | %H / HH24 | 是 | 是 | 24小時制時間（0-23） | | %i / MI | 是 | 是 | 分鐘（0-59） | | %s / SS | 是 | 是 | 秒（0-59） |

將日期函式應用於字串欄位

假設有一個字串欄位 str_column，其內容如下：

10/15/2022
10/16/2023
10/17/2024

要從中提取年份，可以先將字串轉換為日期，然後再應用日期函式。不同 RDBMS 的實作方法如下：

MySQL

SELECT YEAR(STR_TO_DATE(str_column, '%m/%d/%Y')) FROM my_table;

Oracle

SELECT EXTRACT(YEAR FROM TO_DATE(str_column, 'MM/DD/YYYY')) FROM my_table;

PostgreSQL

SELECT EXTRACT(YEAR FROM TO_DATE(str_column, 'MM/DD/YYYY')) FROM my_table;

SQL Server

SELECT YEAR(CONVERT(CHAR, str_column, 101)) FROM my_table;

SQLite

SELECT SUBSTR(str_column, 7) FROM my_table;

處理 NULL 值的函式

在 SQL 中，可以使用多種函式來處理 NULL 值。以下是一些常見的函式：

COALESCE 函式

用於傳回第一個非 NULL 的值。

SELECT COALESCE(greeting, 'hi') AS greeting FROM my_table;

其他 RDBMS 特有的 NULL 處理函式包括：

MySQL 和 SQLite：IFNULL(greeting, 'hi')
Oracle：NVL(greeting, 'hi')
SQL Server：ISNULL(greeting, 'hi')

SQL進階查詢概念：條件邏輯與分組聚合

SQL查詢不僅僅侷限於簡單的資料檢索，還能夠透過各種進階技巧實作複雜的資料處理和分析。本文將探討SQL中的條件邏輯處理和分組聚合操作，幫助讀者掌握更強大的資料查詢能力。

使用CASE運算元實作條件邏輯

SQL中的CASE運算元允許在查詢中實作IF-THEN邏輯，根據不同的條件傳回不同的值。CASE運算元有兩種主要形式：簡單CASE運算元和搜尋CASE運算元。

簡單CASE運算元

簡單CASE運算元用於檢查某個欄位的值是否符合特定的條件。例如，我們有一個concert表，包含name和flag欄位：

SELECT * FROM concert;
+
---
-
---
+
---
---
+
| name  | flag |
+
---
-
---
+
---
---
+
| anton | 1    |
| julia | 0    |
| maren | 1    |
| sarah | NULL |
+
---
-
---
+
---
---
+

我們可以使用簡單CASE運算元根據flag的值傳回不同的票務型別：

SELECT name, flag,
       CASE flag 
           WHEN 1 THEN 'vip'
           WHEN 0 THEN 'reserved seating'
           ELSE 'general admission'
       END AS ticket
FROM concert;

輸出結果如下：

+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+
| name  | flag | ticket            |
+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+
| anton | 1    | vip               |
| julia | 0    | reserved seating  |
| maren | 1    | vip               |
| sarah | NULL | general admission |
+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+

搜尋CASE運算元

搜尋CASE運算元提供了更靈活的條件檢查，可以對多個欄位進行複雜的邏輯判斷。例如：

SELECT name, flag,
       CASE 
           WHEN name = 'anton' THEN 'vip'
           WHEN flag IN (0,1) THEN 'reserved seating'
           ELSE 'general admission'
       END AS ticket
FROM concert;

輸出結果如下：

+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+
| name  | flag | ticket            |
+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+
| anton | 1    | vip               |
| julia | 0    | reserved seating  |
| maren | 1    | reserved seating  |
| sarah | NULL | general admission |
+
---
-
---
+
---
---
+
---
-
---
-
---
-
---
-
---
+

處理NULL值

除了使用CASE運算元外，還可以使用COALESCE函式來替換NULL值。COALESCE函式傳回其引數列表中的第一個非NULL值。

分組與聚合操作

SQL允許將資料行分成多組，並對每組內的資料進行聚合計算，最終每組傳回一行結果。

基本的分組操作

假設我們有一個workouts表，記錄了不同人燃燒的卡路里：

SELECT * FROM workouts;
+
---
---
+
---
-
---
---
+
| name | calories |
+
---
---
+
---
-
---
---
+
| ally | 80       |
| ally | 75       |
| ally | 90       |
| jess | 100      |
| jess | 92       |
+
---
---
+
---
-
---
---
+

我們可以使用GROUP BY子句按name欄位分組，並計算每組的總卡路里消耗：

SELECT name, 
       SUM(calories) AS total_calories
FROM workouts
GROUP BY name;

輸出結果如下：

+
---
---
+
---
-
---
-
---
-
---
-+
| name | total_calories |
+
---
---
+
---
-
---
-
---
-
---
-+
| ally | 245            |
| jess | 192            |
+
---
---
+
---
-
---
-
---
-
---
-+

多欄位分組

對於包含多個欄位的表，我們可以按多個欄位進行分組。例如，有一個daily_workouts表：

SELECT * FROM daily_workouts;
+
---
---
+
---
---
+
---
-
---
-
---
-+
---
-
---
---
+
| id   | name | date       | calories |
+
---
---
+
---
---
+
---
-
---
-
---
-+
---
-
---
---
+
| 1    | ally | 2021-03-03 | 80       |
| 1    | ally | 2021-03-04 | 75       |
| 1    | ally | 2021-03-05 | 90       |
| 2    | jess | 2021-03-03 | 100      |
| 2    | jess | 2021-03-05 | 92       |
+
---
---
+
---
---
+
---
-
---
-
---
-+
---
-
---
---
+

我們可以按id和name進行分組，並計算每個人的訓練次數和總卡路里消耗：

SELECT id, name,
       COUNT(date) AS workouts,
       SUM(calories) AS total_calories
FROM daily_workouts
GROUP BY id, name;

輸出結果如下：

+
---
---
+
---
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
| id   | name | workouts | total_calories |
+
---
---
+
---
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
| 1    | ally | 3        | 245            |
| 2    | jess | 2        | 192            |
+
---
---
+
---
---
+
---
-
---
---
+
---
-
---
-
---
-
---
-+

最佳化GROUP BY操作

在某些情況下，可以透過減少GROUP BY子句中的欄位數量來提高查詢效率。例如，如果已知每個id對應一個唯一的name，則可以這樣寫查詢：

SELECT id,
       MAX(name) AS name,
       COUNT(date) AS workouts,
       SUM(calories) AS total_calories
FROM daily_workouts
GROUP BY id;

這種寫法透過使用聚合函式（如MAX）來保留需要的欄位，同時減少了GROUP BY的欄位數量，從而提高了查詢效率。

玄貓 BlackCat

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