如何在mysql中有效地處理日期和時間

學習在SQL中管理日期和時間以及指定與時間相關的資料庫查詢所需的所有命令。...

日期和時間很重要，它們有助於保持事物的有序性，並且是任何軟體操作不可或缺的一個方面。

在資料庫中高效地使用它們有時看起來很混亂，不管是跨不同的時區工作、新增/減去日期，還是其他操作。

學習各種MySQL函式，以便輕鬆地處理和管理資料庫中的日期/時間。

使用時區

為了幫助保持標準化，您應該只使用UTC時區中的日期/時間。每次建立到MySQL資料庫的連線時，都應該將時區切換到UTC，這可以透過以下SQL語句完成：

SET TIME_ZONE = '+0:00'

由於所有日期現在都將儲存在UTC中，所以您總是知道您正在使用什麼，使事情更加簡單和直接。

必要時，您可以使用方便的convert\u TZ（）MySQL函式輕鬆地轉換任何datetime/timestamp值的時區。首先需要知道偏移量，例如，北美西海岸的PST是UTC-08:00，因此可以使用：

SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

結果是2021-02-04 13:47:23，這是完全正確的。傳遞給CONVERT\u TZ（）的三個引數是第一個開始的日期時間/時間戳（使用now（）表示當前時間），第二個引數總是“+0:00”，因為資料庫中的所有日期都強制為UTC，最後一個引數是我們希望將日期轉換為的偏移量。

加減日期

很多時候，您需要新增或減去日期，例如，如果您需要檢索一週前的記錄，或者從現在開始安排一個月的時間。

值得慶幸的是，MySQL擁有出色的DATE\u ADD（）和DATE\u SUB（）函式，使這項任務非常簡單。例如，可以使用SQL語句從當前日期減去兩週：

SELECT DATE_SUB(now(), interval 2 week);

如果您希望在現有時間戳上新增三天，則可以使用：

SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

兩個函式的工作原理相同，第一個引數是開始的時間戳，第二個引數是要加或減的間隔。第二個引數的格式總是相同的，從單詞interval開始，後跟一個數字值和interval本身，可以是以下任意值：second、minute、hour、day、week、month、quarter、year。

例如，如果要檢索過去34分鐘內發生的所有登入，可以使用SQL語句，例如：

SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

如您所見，這將從logins表中檢索登入日期大於當前時間減去45分鐘的所有記錄，換句話說，就是過去的45分鐘。

獲取日期之間的差異

有時你需要知道兩次約會之間經過了多少時間。使用DATEDIFF函式，您可以很容易地獲得兩個不同日期之間的天數，例如下面的SQL語句：

SELECT DATEDIFF(now(), '2020-12-15');

DATEDIFF函式接受兩個引數，這兩個引數都是日期/時間戳，並給出它們之間的天數。上面的例子將顯示從2020年12月15日到今天的天數。

要獲取兩個日期之間的秒數，可以使用To_seconds（）函式，例如：

SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

這將導致提供的兩個日期之間的秒數。

從日期中提取段

有各種MySQL函式可以讓您輕鬆地從日期中提取特定的片段，例如如果您只想要月份、一年中的某一天或某個小時。下面是一些此類函式的示例：

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

上面的SQL語句將導致02，即當前時間，以及196，因為9月15日是一年中的第196天。以下是所有可用日期提取函式的列表，每個函式只接受一個引數，提取的日期來自：

- SECOND()- MINUTE()- HOUR()- DAY()- WEEK() - Number 0 - 52 defining the week within the year.- MONTH()- QUARTER() - Number 1 - 4 defining the quarter of the year.- YEAR()- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).- LAST_DAY() - The last day in the given month.- DATE() - The date in YYYY-MM-DD format without the time.- TIME() The time in HH:II:SS format without the date.- TO_DAYS() - The number of days since A.D. 0.- TO_SECONDS() - The number of seconds since A.D. 0.- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

例如，如果您只想檢索建立所有使用者的月份和年份，則可以使用SQL語句，例如：

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

這將檢索users表中的所有記錄，並顯示每個使用者在中建立的id、、月份和年份。

按日期期間分組記錄

date函式的一個很好的用法是能夠在SQL語句中使用groupby按日期段對記錄進行分組。例如，您可能希望將2020年所有訂單的總金額按月份分組。您可以使用SQL語句，例如：

SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

這將檢索2020年下達的所有訂單，按建立的月份對其進行分組，並返回12條記錄，顯示年度每個月訂購的總金額。

請注意，為了獲得更好的索引效能，最好避免在SQL語句的WHERE子句中使用諸如YEAR（）之類的日期函式，而是使用BETWEEN運算子，如上面的示例所示。