從記錄訂單時間、用戶注冊時間，到統(tǒng)計每月銷售額、分析數據變化趨勢，都離不開對日期時間的操作，MySQL提供了豐富的日期函數，能夠高效地進行日期和時間的提取、計算、格式化等操作。本文我將深入剖析MySQL常用日期函數的功能、語法及應用場景，并結合實戰(zhàn)案例，帶你全面掌握日期函數的使用技巧。

一、日期函數概述

1.1 日期函數的作用

MySQL日期函數主要用于處理日期和時間數據，其核心作用包括：

• 日期和時間的提取：從日期時間字段中獲取年、月、日、時、分、秒等具體信息。
• 日期和時間的計算：執(zhí)行日期的加減運算，計算兩個日期之間的間隔。
• 日期和時間的格式化：將日期時間數據轉換為指定的格式，方便展示和處理。
• 日期和時間的比較與判斷：判斷日期是否在特定范圍內，比較兩個日期的先后順序。

1.2 日期數據類型

在深入學習日期函數前，先回顧MySQL中常用的日期和時間數據類型：

• DATE：用于存儲日期，格式為YYYY-MM-DD。
• TIME：用于存儲時間，格式為HH:MM:SS。
• DATETIME：用于存儲日期和時間，格式為YYYY-MM-DD HH:MM:SS。
• TIMESTAMP：也用于存儲日期和時間，范圍比DATETIME更窄，但占用空間更小，自動更新為當前時間。

二、日期和時間的獲取函數

2.1 獲取當前日期和時間

• CURDATE()：返回當前日期，格式為YYYY-MM-DD。

SELECT CURDATE();

• CURTIME()：返回當前時間，格式為HH:MM:SS。

SELECT CURTIME();

• NOW()：返回當前日期和時間，格式為YYYY-MM-DD HH:MM:SS。

SELECT NOW();

• SYSDATE()：同樣返回當前日期和時間，與NOW()的區(qū)別在于SYSDATE()在函數執(zhí)行時獲取時間，而NOW()在語句開始執(zhí)行時獲取時間。

SELECT SYSDATE();

2.2 提取日期和時間的部分信息

• YEAR(date)：返回日期中的年份。

SELECT YEAR(NOW()) AS current_year;

• MONTH(date)：返回日期中的月份（1 - 12）。

SELECT MONTH(NOW()) AS current_month;

• DAY(date)：返回日期中的日（1 - 31）。

SELECT DAY(NOW()) AS current_day;

• HOUR(time)：返回時間中的小時數。

SELECT HOUR(CURTIME()) AS current_hour;

• MINUTE(time)：返回時間中的分鐘數。

SELECT MINUTE(CURTIME()) AS current_minute;

• SECOND(time)：返回時間中的秒數。

SELECT SECOND(CURTIME()) AS current_second;

2.3 獲取日期中的星期、季度等信息

• WEEKDAY(date)：返回日期對應的星期索引（0表示星期一，6表示星期日）。

SELECT WEEKDAY(NOW()) AS weekday_index;

• DAYNAME(date)：返回日期對應的星期名稱。

SELECT DAYNAME(NOW()) AS weekday_name;

• MONTHNAME(date)：返回日期對應的月份名稱。

SELECT MONTHNAME(NOW()) AS month_name;

• QUARTER(date)：返回日期所在的季度（1 - 4）。

SELECT QUARTER(NOW()) AS current_quarter;

三、日期和時間的計算函數

3.1 日期和時間的加減運算

• DATE_ADD(date, INTERVAL expr unit)：在指定日期上增加一段時間間隔。

-- 在當前日期上加7天
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS add_7_days;

• DATE_SUB(date, INTERVAL expr unit)：在指定日期上減去一段時間間隔。

-- 在當前日期上減1個月
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS subtract_1_month;

• ADDDATE(date, INTERVAL expr unit)：功能與DATE_ADD相同。

SELECT ADDDATE(NOW(), INTERVAL 3 HOUR) AS add_3_hours;

• SUBDATE(date, INTERVAL expr unit)：功能與DATE_SUB相同。

SELECT SUBDATE(NOW(), INTERVAL 15 MINUTE) AS subtract_15_minutes;

3.2 計算兩個日期之間的間隔

• DATEDIFF(date1, date2)：返回兩個日期之間相差的天數。

-- 計算兩個日期之間的天數差
SELECT DATEDIFF('2024-12-31', '2024-01-01') AS day_difference;

• TIMESTAMPDIFF(unit, datetime1, datetime2)：返回兩個日期時間之間的間隔，unit可以是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等。

-- 計算兩個時間之間相差的小時數
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 00:00:00', '2024-01-01 12:00:00') AS hour_difference;

四、日期和時間的格式化函數

4.1 DATE_FORMAT(date, format)

DATE_FORMAT函數將日期按照指定的格式進行格式化，常用的格式代碼如下：

• %Y：四位年份（如2024）
• %y：兩位年份（如24）
• %m：兩位月份（01 - 12）
• %b：月份縮寫（如Jan）
• %M：月份全稱（如January）
• %d：兩位日期（01 - 31）
• %H：24小時制小時數（00 - 23）
• %h：12小時制小時數（01 - 12）
• %i：分鐘數（00 - 59）
• %s：秒數（00 - 59）

示例：將當前日期格式化為“2024年12月31日 12:00:00”

SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS formatted_date;

4.2 TIME_FORMAT(time, format)

TIME_FORMAT函數用于格式化時間，與DATE_FORMAT類似，但僅適用于時間部分。

-- 將當前時間格式化為“下午12時00分00秒”
SELECT TIME_FORMAT(CURTIME(), '%p%h時%i分%s秒') AS formatted_time;

五、日期和時間的比較與判斷函數

5.1 比較日期和時間的先后順序

可以直接使用比較運算符（>、<、>=、<=、=）對日期和時間進行比較。

-- 判斷當前日期是否大于指定日期
SELECT NOW() > '2024-01-01' AS is_greater;

5.2 判斷日期是否在特定范圍內

使用BETWEEN... AND...語句判斷日期是否在指定的范圍內。

-- 查詢訂單表中2024年1月1日到2024年12月31日之間的訂單
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

六、實戰(zhàn)案例

6.1 統(tǒng)計每月銷售額

在sales表中，有order_date（訂單日期）和amount（訂單金額）字段，需求是統(tǒng)計每個月的總銷售額。

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    month;

6.2 計算用戶注冊時長

在users表中，register_date字段記錄了用戶的注冊日期，需求是計算每個用戶的注冊時長（以天數為單位）。

SELECT 
    user_id,
    DATEDIFF(CURDATE(), register_date) AS registration_days
FROM 
    users;

6.3 篩選本周內的活動數據

在activities表中，activity_date字段記錄活動發(fā)生的日期，需求是篩選出本周內的活動數據。

SELECT *
FROM activities
WHERE activity_date BETWEEN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 
                    AND DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 6 DAY);

到此這篇關于MySQL常用函數詳解之日期函數的文章就介紹到這了,更多相關mysql日期函數內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！