如何在SQL中處理日期和時間

教學

SQL

介紹

在處理關聯式資料庫和結構化查詢語言（SQL）時，有時您可能需要處理表示特定日期或時間的值。例如，您可能需要計算在某項活動上花費的總時間，或者您可能需要使用數學運算符和聚合函數來操作日期或時間值以計算它們的總和或平均值。

在本教程中，您將學習如何在SQL中使用日期和時間。您將首先通過執行算術並使用各種函數來處理日期和時間，僅使用SELECT語句。然後，您將通過對樣本數據運行查詢來進行練習，並學習如何使用CAST函數使輸出更易於閱讀。

先決條件

完成本教程，您將需要：

A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
在伺服器上安裝並安全配置MySQL。請參照我們的如何在Ubuntu 20.04上安裝MySQL指南進行設置。本指南假設您已經像本指南的第3步中所述設置了一個非根MySQL使用者。

注意：請注意，許多關聯式資料庫管理系統使用其自己獨特的 SQL 實現。儘管本教程中概述的命令在大多數 RDBMS 上都能正常工作，但如果您在除 MySQL 之外的系統上進行測試，則精確的語法或輸出可能會有所不同。

在本教程中練習使用日期和時間，您將需要一個包含範例數據的資料庫和表。如果您還沒有一個準備好要插入的，您可以閱讀以下連接到 MySQL 並設置範例資料庫部分，了解如何創建資料庫和表。本教程將在整個過程中參考這個範例資料庫和表。

使用日期和時間進行算術操作

在SQL中，您可以使用數學表達式來操作日期和時間值。所需的只是數學運算符和要計算的值。

舉例來說，假設您想要找到某個日期是另一個日期之後若干天的情況。以下查詢接受一個日期值（2022-10-05），並將 17 加到它上面，以返回在查詢中指定日期之後若干天的日期值。請注意，此示例將 2022-10-05 指定為 DATE 值，以確保 DBMS 不會將其解釋為字符串或其他數據類型：

SELECT DATE '2022-10-05' + 17 AS new_date;

Output+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

如此輸出顯示，2022-10-05 之後的 17 天是 2022-10-22，即 2022 年 10 月 22 日。

舉例來說，假設您想要計算兩個不同時間之間的總小時數。您可以通過將兩個時間相減來完成這一操作。對於以下查詢，11:00 是第一個時間值，3:00 是第二個時間值。在這裡，您需要指定兩者都是 TIME 值，以便返回小時差：

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

此輸出告訴您，11:00 和 3:00 之間的差異是 80000，即 8 小時。

現在，練習使用樣本數據中的日期和時間信息進行算術操作。對於第一個查詢，通過將 end_time 減去 start_time 來計算每場比賽選手完成比賽所需的總時間：

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

您會注意到 total_time 列中的此輸出相當冗長且難以閱讀。稍後，我們將演示如何使用 CAST 函數將這些數據值轉換為更清晰的形式。

現在，如果您只對每個選手在更長的賽跑比賽中的表現感興趣，比如半程馬拉松和全程馬拉松，您可以查詢您的數據以檢索該信息。對於此查詢，請從start_time中減去end_time，並使用WHERE子句來收集total_miles大於12的數據：

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

在這一部分，您使用SELECT語句對日期和時間進行了一些算術操作，並對樣本數據進行了實際操作。接下來，您將練習使用各種日期和時間函數進行查詢。

使用日期和時間函數和間隔表達式

SQL中有幾個函數可用於查找和操作日期和時間值。SQL函數通常用於處理或操作數據，可用的函數取決於SQL實現。但是，大多數SQL實現允許您通過查詢current_date和current_time值來查找當前日期和時間。

例如，要查找今天的日期，語法簡短，僅包括SELECT語句和current_date函數，如下所示：

SELECT current_date;

Output+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

使用相同的語法，您可以使用current_time函數查找當前時間：

SELECT current_time;

Output+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

如果您喜歡在輸出中查詢日期和時間，可以使用current_timestamp函數：

SELECT current_timestamp;

Output+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

您可以在算術函數中使用類似於上一節的日期和時間函數。例如，假設您想知道11天前的日期是從今天的日期開始的。在這種情況下，您可以使用與之前查詢current_date函數相同的語法結構，然後從中減去11來找到十一天前的日期：

SELECT current_date - 11;

Output+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

如此輸出所示，從current_date（在撰寫本文時）算起，十一天前是2022-02-06，即2022年2月6日。現在嘗試運行相同的操作，但將current_date替換為current_time函數：

SELECT current_time - 11;

Output+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

該輸出顯示，當您從current_time值中減去11時，它會減去11秒。您之前使用current_date函數運行的操作將11解釋為天，而不是秒。在處理日期和時間函數時，數字被解釋的不一致性可能會令人困惑。許多數據庫管理系統允許您更明確地使用INTERVAL表達式，而不需要像這樣使用算術來操作日期和時間值。

INTERVAL表達式允許您找到在給定日期或時間表達式之前或之後的一段時間後的日期或時間。它們必須採取以下形式：

Example interval expression

INTERVAL value unit

例如，要找到五天後的日期，您可以運行以下查詢：

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

這個例子找到了current_date值，然後將間隔表達式INTERVAL '5' DAY添加到其中。這將返回從現在開始的5天後的日期：

Output+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

這比以下查詢要少含糊，該查詢產生了類似但不完全相同的輸出：

SELECT current_date + 5 AS "5_days_from_today";

Output+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

請注意，您還可以從日期或時間中減去間隔以找到指定日期值之前的值：

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

在INTERVAL表達式中可用的單位取決於您選擇的DBMS，但大多數選擇會有像HOUR、MINUTE和SECOND這樣的選項：

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

既然您已經了解了間隔表達式和一些日期和時間函數，請繼續練習使用您在第一步中插入的示例數據。

現在，讓我們將幾個聚合函數與CAST函數結合使用，以找到每個選手的最短、最長和總計時間結果。首先，使用MIN聚合函數查詢花費的最短時間。同樣，您需要使用CAST將TIMESTAMP數據值轉換為TIME數據值以確保清晰。請注意，像這個例子中使用兩個函數時，需要兩對括號，並且總時間的計算（end_time - start_time）應該嵌套在其中一對括號內。最後，添加GROUP BY子句來根據runner_name列組織這些值，以便輸出將呈現兩名選手的比賽結果：

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

此輸出顯示每位選手的最短跑步時間，本例中Bolt的最短時間是六分三十秒，Felix為七分十五秒。

接下來，找出每位選手的最長跑步時間。您可以使用與前一個查詢相同的語法，但這次將MIN替換為MAX：

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

此輸出告訴我們，Bolt的最長跑步時間總共是三小時二十三分十秒；而Felix是四小時二十二分十秒。

現在讓我們查詢一些關於每位跑者總共花費在跑步上的高層次資訊。對於這個查詢，結合 SUM 聚合函數來找出基於 end_time - start_time 的總時數，並使用 CAST 將這些資料值轉換為 TIME。不要忘記包含 GROUP BY 來組織每位跑者的結果值：

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

有趣的是，這個輸出顯示了對 MySQL 的解釋，實際上是將總時間計算為整數。如果我們將這些結果視為時間，Bolt 的總時間會分解為五小時、28分鐘和80秒；Felix 的時間會分解為七小時、61分鐘和49秒。正如你所見，這種時間的分解是不合理的，這表明它是以整數而不是時間來計算的。如果你在不同的 DBMS 中嘗試，例如 PostgreSQL，同樣的查詢會稍有不同：

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

在這種情況下，PostgreSQL 中的查詢將值解釋為時間並進行相應的計算，這樣 Felix 的結果會總計為10小時、一分鐘和44秒；Bolt 的結果會總計為六小時、九分鐘和20秒。這是各種 DBMS 實現可能以不同方式解釋資料值的例子，即使使用相同的查詢和資料集也是如此。

結論

了解如何在SQL中使用日期和时间在查询特定结果时非常有用，例如分钟、秒、小时、天、月、年，或者它们的组合。此外，有许多可用于日期和时间的函数，使得查找特定值更加容易，例如当前日期或时间。虽然此教程仅在SQL中对日期和时间使用了加法和减法算术，但您可以将日期和时间值与任何数学表达式一起使用。从我们的指南中了解更多关于数学表达式和聚合函数的信息，然后尝试在您的日期和时间查询中使用它们。数学表达式和聚合函数

Source:
https://www.digitalocean.com/community/tutorials/how-to-work-with-dates-and-times-in-sql

介紹

先決條件

連接到 MySQL 並設置範例資料庫

使用日期和時間進行算術操作

使用日期和時間函數和間隔表達式

使用CAST和日期時間聚合函數

結論