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

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

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

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

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

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

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

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

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

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

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

SELECT CURDATE();

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

SELECT CURTIME();

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

SELECT NOW();

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

SELECT SYSDATE();

2.2 提取日期和時(shí)間的部分信息

• 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í)間中的小時(shí)數(shù)。

SELECT HOUR(CURTIME()) AS current_hour;

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

SELECT MINUTE(CURTIME()) AS current_minute;

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

SELECT SECOND(CURTIME()) AS current_second;

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

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

SELECT WEEKDAY(NOW()) AS weekday_index;

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

SELECT DAYNAME(NOW()) AS weekday_name;

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

SELECT MONTHNAME(NOW()) AS month_name;

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

SELECT QUARTER(NOW()) AS current_quarter;

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

3.1 日期和時(shí)間的加減運(yùn)算

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

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

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

-- 在當(dāng)前日期上減1個(gè)月
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 計(jì)算兩個(gè)日期之間的間隔

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

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

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

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

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

4.1 DATE_FORMAT(date, format)

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

• %Y：四位年份（如2024）
• %y：兩位年份（如24）
• %m：兩位月份（01 - 12）
• %b：月份縮寫(xiě)（如Jan）
• %M：月份全稱(chēng)（如January）
• %d：兩位日期（01 - 31）
• %H：24小時(shí)制小時(shí)數(shù)（00 - 23）
• %h：12小時(shí)制小時(shí)數(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ù)用于格式化時(shí)間，與DATE_FORMAT類(lèi)似，但僅適用于時(shí)間部分。

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

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

5.1 比較日期和時(shí)間的先后順序

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

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

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

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

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

六、實(shí)戰(zhàn)案例

6.1 統(tǒng)計(jì)每月銷(xiāo)售額

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

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 計(jì)算用戶(hù)注冊(cè)時(shí)長(zhǎng)

在users表中，register_date字段記錄了用戶(hù)的注冊(cè)日期，需求是計(jì)算每個(gè)用戶(hù)的注冊(cè)時(shí)長(zhǎng)（以天數(shù)為單位）。

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

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

在activities表中，activity_date字段記錄活動(dòng)發(fā)生的日期，需求是篩選出本周內(nèi)的活動(dòng)數(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論