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

一、日期函數(shù)概述

1.1 日期函數(shù)的作用

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

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

1.2 日期數(shù)據(jù)類型

在深入學(xué)習(xí)日期函數(shù)前，先回顧MySQL中常用的日期和時間數(shù)據(jù)類型：

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

二、日期和時間的獲取函數(shù)

2.1 獲取當(dāng)前日期和時間

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

SELECT CURDATE();

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

SELECT CURTIME();

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

SELECT NOW();

• SYSDATE()：同樣返回當(dāng)前日期和時間，與NOW()的區(qū)別在于SYSDATE()在函數(shù)執(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)：返回時間中的小時數(shù)。

SELECT HOUR(CURTIME()) AS current_hour;

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

SELECT MINUTE(CURTIME()) AS current_minute;

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

SELECT SECOND(CURTIME()) AS current_second;

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

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

SELECT WEEKDAY(NOW()) AS weekday_index;

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

SELECT DAYNAME(NOW()) AS weekday_name;

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

SELECT MONTHNAME(NOW()) AS month_name;

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

SELECT QUARTER(NOW()) AS current_quarter;

三、日期和時間的計算函數(shù)

3.1 日期和時間的加減運算

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

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

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

-- 在當(dāng)前日期上減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)：返回兩個日期之間相差的天數(shù)。

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

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

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

四、日期和時間的格式化函數(shù)

4.1 DATE_FORMAT(date, format)

DATE_FORMAT函數(shù)將日期按照指定的格式進(jìn)行格式化，常用的格式代碼如下：

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

示例：將當(dāng)前日期格式化為“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函數(shù)用于格式化時間，與DATE_FORMAT類似，但僅適用于時間部分。

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

五、日期和時間的比較與判斷函數(shù)

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

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

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

5.2 判斷日期是否在特定范圍內(nèi)

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

-- 查詢訂單表中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字段記錄了用戶的注冊日期，需求是計算每個用戶的注冊時長（以天數(shù)為單位）。

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

6.3 篩選本周內(nèi)的活動數(shù)據(jù)

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

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

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

最新評論