MySQL函数：`MAKEDATE()`和`MAKETIME()`根据年、日和时、分、秒创建日期和时间。

MySQL日期时间函数：MAKEDATE()和MAKETIME()详解

大家好，今天我们来深入探讨MySQL中两个非常有用的日期时间函数：MAKEDATE()和MAKETIME()。这两个函数允许我们根据给定的年、日（在一年中的天数）以及时、分、秒来构造日期和时间值。理解并熟练运用这两个函数，可以极大地提高我们在数据处理和分析中的灵活性。

1. MAKEDATE()函数：构建日期

MAKEDATE()函数用于创建一个日期值。它接受两个参数：

• year: 年份，一个整数。
• dayofyear: 一年中的天数，一个整数，范围从1到366（取决于年份是否为闰年）。

语法:

MAKEDATE(year, dayofyear)

返回值:

一个DATE类型的值。如果参数无效，则返回NULL。

示例:

SELECT MAKEDATE(2023, 1);  -- 返回 '2023-01-01'
SELECT MAKEDATE(2023, 32); -- 返回 '2023-02-01'
SELECT MAKEDATE(2024, 60); -- 返回 '2024-02-29' (闰年)
SELECT MAKEDATE(2023, 365); -- 返回 '2023-12-31'
SELECT MAKEDATE(2023, 366); -- 返回 NULL (2023不是闰年)
SELECT MAKEDATE(2024, 366); -- 返回 '2024-12-31' (2024是闰年)
SELECT MAKEDATE(2023, 0);  -- 返回 NULL (dayofyear必须大于0)
SELECT MAKEDATE(2023, -1); -- 返回 NULL (dayofyear必须大于0)

注意事项:

• dayofyear 必须是正整数。
• 如果 dayofyear 超出了该年份的有效天数范围（1-366），函数将返回 NULL。
• 年份可以是任何有效的年份值。
• 函数会自动处理闰年。

实际应用场景:

• 数据转换: 将存储为年份和一年中天数的两列数据转换为日期类型。
• 日期生成: 生成特定日期序列，例如，生成一年中所有周末的日期。
• 数据清洗: 验证 dayofyear 值是否与其对应的年份一致。

示例：数据转换

假设我们有一个名为 events 的表，其中包含 event_year 和 event_dayofyear 列，分别表示事件发生的年份和一年中的天数。我们可以使用 MAKEDATE() 函数创建一个新的 event_date 列，存储事件发生的实际日期。

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_year INT,
    event_dayofyear INT
);

INSERT INTO events (event_year, event_dayofyear) VALUES
(2023, 45),
(2023, 100),
(2024, 60),
(2024, 300);

ALTER TABLE events ADD COLUMN event_date DATE;

UPDATE events SET event_date = MAKEDATE(event_year, event_dayofyear);

SELECT * FROM events;

执行结果：

event_id	event_year	event_dayofyear	event_date
1	2023	45	2023-02-14
2	2023	100	2023-04-10
3	2024	60	2024-02-29
4	2024	300	2024-10-26

示例：生成日期序列

创建一个存储2023年所有周末日期的表。

CREATE TABLE weekends_2023 (
    weekend_date DATE
);

DELIMITER //
CREATE PROCEDURE generate_weekends_2023()
BEGIN
    DECLARE day_num INT DEFAULT 1;
    WHILE day_num <= 365 DO
        SET @current_date := MAKEDATE(2023, day_num);
        IF DAYOFWEEK(@current_date) IN (1, 7) THEN -- 1是星期日，7是星期六
            INSERT INTO weekends_2023 (weekend_date) VALUES (@current_date);
        END IF;
        SET day_num = day_num + 1;
    END WHILE;
END //
DELIMITER ;

CALL generate_weekends_2023();

SELECT * FROM weekends_2023 LIMIT 5; -- 显示前5个周末

2. MAKETIME()函数：构建时间

MAKETIME()函数用于创建一个时间值。它接受三个参数：

• hour: 小时，一个整数，范围从0到23。
• minute: 分钟，一个整数，范围从0到59。
• second: 秒，一个整数，范围从0到59。

语法:

MAKETIME(hour, minute, second)

返回值:

一个TIME类型的值。如果参数无效，则返回NULL。

示例:

SELECT MAKETIME(12, 30, 45); -- 返回 '12:30:45'
SELECT MAKETIME(0, 0, 0);    -- 返回 '00:00:00'
SELECT MAKETIME(23, 59, 59); -- 返回 '23:59:59'
SELECT MAKETIME(24, 0, 0);   -- 返回 NULL (hour超出范围)
SELECT MAKETIME(-1, 0, 0);   -- 返回 NULL (hour超出范围)
SELECT MAKETIME(12, 60, 0);  -- 返回 NULL (minute超出范围)
SELECT MAKETIME(12, 0, 60);  -- 返回 NULL (second超出范围)

注意事项:

• hour 必须在0到23之间。
• minute 必须在0到59之间。
• second 必须在0到59之间。
• 如果任何参数超出范围，函数将返回 NULL。

实际应用场景:

• 数据转换: 将存储为小时、分钟和秒的单独列转换为时间类型。
• 时间计算: 创建特定的时间点用于时间间隔计算或比较。
• 数据验证: 验证小时、分钟和秒值是否有效。

示例：数据转换

假设我们有一个名为 calls 的表，其中包含 call_hour, call_minute, 和 call_second 列，分别表示通话的小时、分钟和秒。我们可以使用 MAKETIME() 函数创建一个新的 call_time 列，存储通话的实际时间。

CREATE TABLE calls (
    call_id INT PRIMARY KEY AUTO_INCREMENT,
    call_hour INT,
    call_minute INT,
    call_second INT
);

INSERT INTO calls (call_hour, call_minute, call_second) VALUES
(9, 15, 30),
(14, 0, 0),
(17, 45, 59),
(23, 59, 59);

ALTER TABLE calls ADD COLUMN call_time TIME;

UPDATE calls SET call_time = MAKETIME(call_hour, call_minute, call_second);

SELECT * FROM calls;

执行结果：

call_id	call_hour	call_minute	call_second	call_time
1	9	15	30	09:15:30
2	14	0	0	14:00:00
3	17	45	59	17:45:59
4	23	59	59	23:59:59

示例：时间计算

计算从午夜开始经过特定小时、分钟和秒后的时间。

SELECT MAKETIME(0,0,0); -- 午夜
SELECT MAKETIME(0,0,0) + INTERVAL 5 HOUR;  -- 凌晨5点
SELECT MAKETIME(0,0,0) + INTERVAL '2:30' MINUTE_SECOND; -- 凌晨2分30秒

3. 组合使用 MAKEDATE() 和 MAKETIME()：构建日期时间

虽然 MAKEDATE() 和 MAKETIME() 分别用于创建日期和时间，但我们可以将它们组合起来，创建一个 DATETIME 类型的值。 我们可以将MAKEDATE()返回的日期与MAKETIME()返回的时间相结合。

语法:

SELECT CAST(MAKEDATE(year, dayofyear) AS DATETIME) + MAKETIME(hour, minute, second);

或者使用 CONCAT() 函数将日期和时间字符串连接起来，然后使用 STR_TO_DATE() 函数将其转换为 DATETIME 类型。

语法:

SELECT STR_TO_DATE(CONCAT(MAKEDATE(year, dayofyear), ' ', MAKETIME(hour, minute, second)), '%Y-%m-%d %H:%i:%s');

示例:

SELECT CAST(MAKEDATE(2023, 120) AS DATETIME) + MAKETIME(10, 30, 0); -- 返回 '2023-04-30 10:30:00'
SELECT STR_TO_DATE(CONCAT(MAKEDATE(2024, 60), ' ', MAKETIME(18, 0, 0)), '%Y-%m-%d %H:%i:%s'); -- 返回 '2024-02-29 18:00:00'

实际应用场景:

• 日志记录: 将事件发生的日期和时间分别存储在不同的列中，然后组合成一个完整的 DATETIME 值。
• 计划任务: 根据预定的日期和时间执行任务。
• 数据分析: 将分散的日期和时间信息整合，以便进行更深入的分析。

示例：日志记录

假设我们有一个名为 logs 的表，其中包含 log_year, log_dayofyear, log_hour, log_minute, 和 log_second 列，分别表示日志记录的年份、一年中的天数、小时、分钟和秒。我们可以创建一个新的 log_datetime 列，存储完整的日期和时间。

CREATE TABLE logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    log_year INT,
    log_dayofyear INT,
    log_hour INT,
    log_minute INT,
    log_second INT
);

INSERT INTO logs (log_year, log_dayofyear, log_hour, log_minute, log_second) VALUES
(2023, 150, 8, 0, 0),
(2023, 200, 12, 30, 45),
(2024, 50, 16, 15, 0),
(2024, 330, 20, 0, 0);

ALTER TABLE logs ADD COLUMN log_datetime DATETIME;

UPDATE logs SET log_datetime = CAST(MAKEDATE(log_year, log_dayofyear) AS DATETIME) + MAKETIME(log_hour, log_minute, log_second);

SELECT * FROM logs;

执行结果：

log_id	log_year	log_dayofyear	log_hour	log_minute	log_second	log_datetime
1	2023	150	8	0	0	2023-05-30 08:00:00
2	2023	200	12	30	45	2023-07-19 12:30:45
3	2024	50	16	15	0	2024-02-19 16:15:00
4	2024	330	20	0	0	2024-11-25 20:00:00

4. 错误处理和边界情况

在使用 MAKEDATE() 和 MAKETIME() 时，需要注意一些常见的错误和边界情况，以避免意外的结果。

常见错误:

• dayofyear 为零或负数。
• dayofyear 超出年份的有效范围。
• hour 超出 0-23 的范围。
• minute 或 second 超出 0-59 的范围。

边界情况:

• 闰年的处理：MAKEDATE() 会自动处理闰年，但需要确保 dayofyear 不超过 366。
• NULL 值的处理：如果任何参数为 NULL，函数将返回 NULL。

示例：错误处理

SELECT MAKEDATE(2023, NULL);   -- 返回 NULL
SELECT MAKETIME(NULL, 30, 45); -- 返回 NULL
SELECT MAKEDATE(NULL,NULL);    --返回 NULL

示例：边界情况

SELECT MAKEDATE(2000, 366); -- 返回 '2000-12-31' (2000是闰年)
SELECT MAKEDATE(1900, 366); -- 返回 NULL (1900不是闰年)

5. 性能考虑

MAKEDATE() 和 MAKETIME() 函数通常具有良好的性能，但如果在大型数据集上频繁使用，仍然需要考虑性能优化。

优化技巧:

• 尽量避免在 WHERE 子句中使用函数，因为这可能导致全表扫描。如果可能，预先计算好日期或时间值，并将其存储在表中。
• 如果需要频繁生成日期或时间序列，可以考虑创建一个临时表来存储这些值，而不是每次都调用函数。
• 确保数据库的索引正确设置，以便优化查询性能。

6. 替代方案

虽然 MAKEDATE() 和 MAKETIME() 函数在某些情况下非常有用，但在其他情况下，可能存在更合适的替代方案。

替代方案:

• STR_TO_DATE() 函数：可以将字符串转换为日期或时间类型。
• DATE() 和 TIME() 函数：可以从 DATETIME 值中提取日期或时间部分。
• DATE_ADD() 和 DATE_SUB() 函数：可以进行日期和时间计算。

示例：使用 STR_TO_DATE()

SELECT STR_TO_DATE('2023-05-20', '%Y-%m-%d'); -- 返回 '2023-05-20'
SELECT STR_TO_DATE('10:30:45', '%H:%i:%s');    -- 返回 '10:30:45'
SELECT STR_TO_DATE('2023-05-20 10:30:45', '%Y-%m-%d %H:%i:%s'); -- 返回 '2023-05-20 10:30:45'

示例：使用 DATE() 和 TIME()

SELECT DATE('2023-05-20 10:30:45'); -- 返回 '2023-05-20'
SELECT TIME('2023-05-20 10:30:45'); -- 返回 '10:30:45'

示例：使用 DATE_ADD()

SELECT DATE_ADD('2023-05-20', INTERVAL 1 DAY); -- 返回 '2023-05-21'
SELECT DATE_ADD('10:30:45', INTERVAL 30 MINUTE); -- 返回 '11:00:45'

7. MAKEDATE() 和 MAKETIME()的限制

MAKEDATE() 和 MAKETIME() 虽有其用武之地，但也存在一些局限性。

• 精度限制：MAKETIME() 函数只能精确到秒级别。如果需要更高的精度（例如，毫秒或微秒），则需要使用其他方法。MySQL 5.6.4 及更高版本支持微秒级别的时间精度，但 MAKETIME() 本身并不支持直接创建包含微秒的时间值。
• 日期范围限制：MAKEDATE() 受 MySQL DATE 类型的日期范围限制。MySQL DATE 类型通常支持 ‘1000-01-01’ 到 ‘9999-12-31’ 的日期范围。
• 缺乏时区支持：MAKEDATE() 和 MAKETIME() 函数创建的日期和时间值没有时区信息。如果需要处理时区相关的问题，需要使用其他函数或方法。

总结：掌握日期时间构建技巧

我们详细介绍了MySQL中的MAKEDATE()和MAKETIME()函数，它们分别用于构建日期和时间值。理解并熟练运用这两个函数，可以帮助我们更灵活地处理日期和时间数据。同时，需要注意错误处理、边界情况和性能优化，并在适当的情况下考虑替代方案。