MySQL日期时间处理：全面掌握日期函数

本文还有配套的精品资源，点击获取

简介：MySQL中的时间日期相关函数是数据库管理的核心组件，它们让数据库管理员能够高效地处理日期和时间数据。这些函数涉及当前日期和时间的获取、格式转换、时间间隔计算及日期时间比较等方面。常用函数包括NOW()获取当前日期和时间，CURDATE()和CURTIME()分别获取当前日期和时间，以及DATE()和TIME()从日期时间值中提取相应部分。此外，STR_TO_DATE()和DATE_FORMAT()可以转换和格式化日期和时间字符串，DATE_ADD()和DATE_SUB()用于计算时间的加减，INTERVAL用于指定时间间隔。还有YEAR()、MONTH()、DAY()等函数用于提取特定的日期成分，ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的别名，TIMESTAMPDIFF()计算时间差，FROM_UNIXTIME()和UNIX_TIMESTAMP()分别实现时间戳与日期时间的转换，LAST_DAY()返回某月的最后一天，而EXTRACT()可以从日期时间中提取指定部分。掌握这些函数可以大幅提高数据处理效率，如示例查询所示，可找出特定日期范围内的记录。这些函数的深入理解对于任何使用MySQL的开发人员都是必不可少的。

1. MySQL时间日期相关函数概述

在数据库管理和数据处理中，时间日期是一个经常需要处理的数据类型。MySQL作为世界上使用最广泛的开源数据库之一，提供了一系列强大的时间日期相关函数，帮助开发者高效地处理与时间日期相关的问题。从基本的时间获取，到复杂的时间间隔计算，MySQL提供了丰富的内置函数，能够满足各种场景的需求。

在本章节中，我们将概览这些函数的功能和基本用法，为进一步深入学习打下坚实的基础。我们不仅会介绍如何获取当前的日期和时间，还会探讨如何对日期时间进行格式化、解析以及进行高级的增减操作。通过本章节的学习，读者将对MySQL中的日期时间处理有一个全面的认识，为进一步的学习和应用打下基础。

2. 基本日期时间函数的使用

2.1 获取当前日期和时间

2.1.1 NOW()函数的介绍与使用

NOW() 函数是MySQL中非常常用的一个函数，用于获取当前的日期和时间。该函数返回的是一个字符串，格式为'YYYY-MM-DD HH:MM:SS'。当没有参数传递给 NOW() 函数时，它返回当前的日期和时间。

要使用 NOW() 函数，您只需要在查询中直接调用它即可。例如：

SELECT NOW();

上述查询将返回服务器当前的日期和时间。 NOW() 函数特别适用于需要记录记录创建或修改时间的场景。

2.1.2 CURDATE()与CURTIME()函数的应用场景

CURDATE() 函数返回当前的日期，而 CURTIME() 函数返回当前的时间。这两个函数都是在没有参数的情况下被调用，返回值格式分别为'YYYY-MM-DD'和'HH:MM:SS'。

• CURDATE() 函数非常适合于只需要日期的场合，比如日志记录或统计报表中的日期字段。
• CURTIME() 函数则在需要记录操作发生的精确时间，但不需要日期时非常有用。

使用方法如下：

SELECT CURDATE();  -- 返回当前日期
SELECT CURTIME();  -- 返回当前时间

2.2 日期和时间的分离

2.2.1 DATE()函数在日期提取中的应用

DATE() 函数用于从日期时间字符串中提取出日期部分。它通常和 NOW() 或其它包含日期和时间的函数一起使用，以便在结果中仅保留日期信息。

比如，如果您有一个存储了时间戳的字段，而您需要从中提取出日期，可以使用 DATE() 函数，如下所示：

SELECT DATE('2023-03-01 12:00:00');  -- 返回 '2023-03-01'

在应用中， DATE() 函数可用于查询指定日期范围内的数据，或者在报表中仅仅展示日期。

2.2.2 TIME()函数在时间提取中的应用

与 DATE() 函数类似， TIME() 函数用于从日期时间字符串中提取时间部分。该函数返回值的格式为'HH:MM:SS'。

例如，如果你有一个时间戳字段并且只关心时间部分，可以使用 TIME() 函数来获取。

SELECT TIME('2023-03-01 12:30:45');  -- 返回 '12:30:45'

在实际应用中， TIME() 函数可以用于日志分析，比如统计访问的高峰时间。

代码块逻辑分析：

1. SELECT NOW();

   • 此代码块展示如何获取当前的日期和时间。 NOW() 函数不需要任何参数，返回当前服务器时间。

2. SELECT CURDATE();

   • 这行SQL展示了如何仅获取当前日期，常用于只需要日期的查询和记录场景。

3. SELECT CURTIME();

   • 此代码块说明了如何只获取当前的时间，适用于需要时间而不需要日期的场景。

4. SELECT DATE('2023-03-01 12:00:00');

   • 在这个例子中， DATE() 函数从一个完整的日期时间字符串中提取出日期部分。

5. SELECT TIME('2023-03-01 12:30:45');

   • 这里， TIME() 函数被用来从日期时间字符串中提取出时间部分。

3. 日期时间格式化与解析函数

3.1 字符串转换为日期时间

在处理时间日期数据时，经常需要将存储为字符串的日期时间信息转换成数据库能够识别和操作的日期时间格式。MySQL提供了两种函数来实现这一需求： STR_TO_DATE() 和 DATE_FORMAT() 。

3.1.1 STR_TO_DATE()函数的使用方法

STR_TO_DATE() 函数将字符串转换为日期时间值。它的基本语法如下：

STR_TO_DATE(string, format)

其中， string 是需要转换的日期时间格式的字符串， format 是该字符串的日期时间格式。使用该函数时，需要提前了解MySQL中各个日期和时间格式符的含义，以确保字符串能被正确解析。

举个例子：

SELECT STR_TO_DATE('2023-03-01 12:34:56', '%Y-%m-%d %H:%i:%s');

此例中，字符串 '2023-03-01 12:34:56' 会被解析为 datetime 类型，并且根据指定的格式 %Y-%m-%d %H:%i:%s 正确地转换。其中 %Y 代表四位数的年份， %m 代表月份， %d 代表日， %H 代表小时（24小时制）， %i 代表分钟， %s 代表秒。

3.1.2 DATE_FORMAT()函数的格式化技巧

DATE_FORMAT() 函数用于将日期时间格式化为字符串。其语法结构为：

DATE_FORMAT(date, format)

这里的 date 是日期或时间的值， format 是指定的输出格式字符串。此函数非常有用，特别是在需要自定义日期时间显示格式的场景中。

例如：

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y %H:%i:%s');

这将返回当前日期和时间，格式化为 '日-月-年 时:分:秒' 的形式，例如 '01-03-2023 13:45:57'。

3.1.2.1 格式化字符串解释

在 DATE_FORMAT() 函数中，格式化字符串可以包含很多部分，以下是一些常用的格式化占位符：

• %Y ：四位数字表示的年份。
• %y ：两位数字表示的年份。
• %m ：月份，以0开头的数字。
• %c ：月份，不以0开头。
• %D ：带有英文后缀的日。
• %d ：两位数字表示的日。
• %H ：小时（24小时制）。
• %h ：小时（12小时制）。
• %i ：分钟，两位数字。
• %s ：秒，两位数字。

在格式化输出时，根据需要选择合适的占位符，可以灵活地展示日期时间信息。

3.2 日期时间的增减操作

在数据处理时，经常需要对日期时间进行增减操作，以实现对时间间隔的计算。MySQL中用于日期时间增减操作的函数主要有 DATE_ADD() 和 DATE_SUB() 。

3.2.1 DATE_ADD()函数进行日期时间加法

DATE_ADD() 函数向日期时间添加指定的时间间隔，其基本语法如下：

DATE_ADD(date, INTERVAL value unit)

date 是原始的日期时间值， value 是要增加的时间间隔数量， unit 是时间间隔的单位。常见的时间间隔单位有 SECOND , MINUTE , HOUR , DAY , MONTH , YEAR 等。

例如，给定当前时间，我们可以计算30天后的日期：

SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);

这将返回从当前时间起30天后的日期和时间。

3.2.2 DATE_SUB()函数进行日期时间减法

与 DATE_ADD() 相反， DATE_SUB() 函数用于从日期时间中减去指定的时间间隔，其语法结构与 DATE_ADD() 相似：

DATE_SUB(date, INTERVAL value unit)

这里 date 是原始日期时间值， value 是要减去的时间间隔数量， unit 是时间间隔的单位。

例如，要得到当前时间30天前的日期：

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

这个操作将返回当前时间减去30天后的日期。

3.2.2.1 时间间隔操作的注意事项

使用 DATE_ADD() 和 DATE_SUB() 函数进行日期时间的加减操作时，需要注意的是，结果总是返回 datetime 类型。如果操作的结果超过或低于 datetime 的范围，MySQL会自动进行处理，但有时会导致意外的结果，因此在使用这些函数时需要格外小心。

3.2.2.2 时间间隔和单位的完整列表

时间间隔单位不仅限于 SECOND 、 MINUTE 、 HOUR 、 DAY 、 MONTH 、 YEAR ，还包括以下扩展单位：

• SECOND_MICROSECOND ：秒和毫秒。
• MINUTE_MICROSECOND ：分钟和毫秒。
• HOUR_MICROSECOND ：小时和毫秒。
• MINUTE_SECOND ：分钟和秒。
• HOUR_SECOND ：小时和秒。
• DAY_MICROSECOND ：天和毫秒。
• DAY_SECOND ：天和秒。
• YEAR_MONTH ：年和月。

使用这些单位时，需要将值和单位对用 value unit 的形式表示，其中 value 和 unit 之间用空格分隔。

3.2.2.3 实际应用案例

例如，要计算从当前时间开始的未来3年零2个月零3天零4小时零5分钟零6秒的时间点：

SELECT DATE_ADD(NOW(), INTERVAL '3-2 4:5:6' YEAR_MONTH_MINUTE_SECOND);

此例中，使用了 YEAR_MONTH_MINUTE_SECOND 这样一个复合单位，它允许我们一次性指定多个不同的时间间隔。对于复杂的日期时间计算，这种复合单位的使用可以大大简化查询语句。

通过上述例子可以看出， DATE_ADD() 和 DATE_SUB() 函数的灵活性，能够应对大多数日期时间的计算需求。不过，开发者需要对时间间隔单位有充分的认识，才能正确使用这些函数。

4. 高级日期时间处理函数

4.1 时间间隔与日期时间运算

在处理日期和时间时，我们经常会遇到需要计算时间间隔、进行日期时间运算的情况。这时候，MySQL提供了相应的函数来满足这类需求。

4.1.1 INTERVAL关键字的使用

INTERVAL关键字在MySQL中用于表示时间间隔。它可以在某些日期时间函数中使用，来指定时间间隔的单位，例如年、月、日、时、分、秒等。INTERVAL的使用通常与一些比较函数一起出现，比如在比较日期之间的时间间隔时。

SELECT INTERVAL(1 DAY);  -- 返回 '1 day'
SELECT INTERVAL(1 YEAR);  -- 返回 '1 year'
SELECT INTERVAL(10 SECOND);  -- 返回 '10 second'

4.1.2 年、月、日、时、分、秒的提取方法

我们经常需要提取日期时间中的特定部分，如年份、月份等。在MySQL中，可以直接使用 YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND() 这些函数来实现。

SELECT 
    YEAR('2023-01-15 12:34:56') AS Year,
    MONTH('2023-01-15 12:34:56') AS Month,
    DAY('2023-01-15 12:34:56') AS Day,
    HOUR('2023-01-15 12:34:56') AS Hour,
    MINUTE('2023-01-15 12:34:56') AS Minute,
    SECOND('2023-01-15 12:34:56') AS Second;

以上代码会返回给定日期时间的各个组成部分，如2023年、1月、15日等。

4.2 ADDDATE()与SUBDATE()函数的别名应用

4.2.1 ADDDATE()函数的参数和用法

ADDDATE()函数是一个非常灵活的日期时间函数，它允许对日期或者日期时间值进行加法运算。它有多种别名，例如 DATE_ADD() ，但它们在功能上是相同的。

SELECT ADDDATE('2023-01-15', INTERVAL 1 YEAR);  -- 在2023年1月15日上加上1年
SELECT DATE_ADD('2023-01-15', INTERVAL 1 MONTH);  -- 在2023年1月15日上加上1个月

ADDDATE()函数的第一个参数是要操作的日期值，第二个参数是INTERVAL关键字后跟时间间隔的描述。

4.2.2 SUBDATE()函数的参数和用法

与ADDDATE()相对应的，SUBDATE()函数用于执行日期时间值的减法运算。

SELECT SUBDATE('2023-01-15', INTERVAL 1 YEAR);  -- 从2023年1月15日减去1年
SELECT SUBDATE('2023-01-15', INTERVAL 1 MONTH);  -- 从2023年1月15日减去1个月

SUBDATE()的使用方法与ADDDATE()相同，第一个参数是一个日期或日期时间值，第二个参数是表示时间间隔的INTERVAL表达式。

通过使用这些高级日期时间函数，我们可以灵活地进行日期和时间的计算，满足不同场景下的数据处理需求。这些函数在处理复杂的日期时间问题时，提供了一种高效而强大的解决方案。

5. 日期时间函数的高级应用

5.1 TIMESTAMPDIFF()函数的时间间隔计算

5.1.1 TIMESTAMPDIFF()函数的参数介绍

在进行日期时间相关的高级计算时， TIMESTAMPDIFF() 函数提供了一种便捷的方式来计算两个日期时间之间的差异，它会根据指定的单位返回两个时间点之间的间隔。该函数的基本语法如下：

TIMESTAMPDIFF(unit, start_date, end_date)

• unit ：指定返回值的单位，可以是年（ YEAR ）、季度（ QUARTER ）、月（ MONTH ）、周（ WEEK ）、日（ DAY ）、小时（ HOUR ）、分钟（ MINUTE ）或秒（ SECOND ）等。
• start_date ：时间间隔的起始日期时间。
• end_date ：时间间隔的结束日期时间。

TIMESTAMPDIFF() 函数对于计算诸如工作天数、年龄等场景特别有用，能够根据业务需求灵活地计算出时间差。

5.1.2 实际场景中的应用示例

假设我们要计算一个用户的年龄，假设用户的出生日期为 2000-01-15 ，现在日期为 2023-04-01 ，使用 TIMESTAMPDIFF() 函数可以直接计算出用户的年龄，代码示例如下：

SELECT TIMESTAMPDIFF(YEAR, '2000-01-15', CURDATE()) AS age;

执行上述查询后，将返回用户的年龄，即23岁。

再举个例子，如果需要计算员工在公司工作的年数，假设员工入职日期存储在 employees 表的 hire_date 字段，可以执行如下查询：

SELECT employee_id,
       TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_of_service
FROM employees;

这段代码会返回员工的ID和他们已服务的年数。

5.2 UNIX时间戳转换函数的使用

5.2.1 UNIX_TIMESTAMP()函数的转换机制

UNIX_TIMESTAMP() 函数用于将日期时间转换为 UNIX 时间戳，即从 '1970-01-01 00:00:00' UTC 到指定日期时间的秒数。这个函数非常适用于时间数据的跨平台操作和存储，尤其是需要进行时间计算或排序时。其基本语法如下：

UNIX_TIMESTAMP([date])

• date ：可选参数，表示要转换的日期时间，默认值为当前系统日期和时间。

转换为UNIX时间戳之后，可以进行数值计算，比如加减秒数来得到新的时间点。

5.2.2 FROM_UNIXTIME()函数的转换应用

与 UNIX_TIMESTAMP() 函数相对应的是 FROM_UNIXTIME() 函数，它将UNIX时间戳转换回日期时间格式。这在从日志文件或外部系统中提取时间戳并需要将其转换为更可读的日期时间格式时非常有用。其基本语法如下：

FROM_UNIXTIME(unix_timestamp)

• unix_timestamp ：UNIX时间戳。

例如，假设从一个应用程序中提取到一个UNIX时间戳 1656126400 ，要将其转换为日期时间格式，可以使用以下SQL语句：

SELECT FROM_UNIXTIME(1656126400) AS formatted_date_time;

执行该语句后，输出将是：

formatted_date_time
2022-06-27 12:00:00

在MySQL中，UNIX时间戳是以秒为单位的，但MySQL也支持以毫秒为单位的UNIX时间戳，例如 FROM_UNIXTIME(1656126400000 / 1000) 。

以上就是对MySQL日期时间函数中 TIMESTAMPDIFF() 和 UNIX_TIMESTAMP() / FROM_UNIXTIME() 函数的高级应用示例。通过这些例子，我们可以看到，MySQL提供的日期时间函数能够帮助我们完成从基本的日期时间运算到复杂的时间戳转换等多种任务。这无疑增强了我们在数据处理和分析时的灵活性和效率。

6. 特殊日期时间处理函数

在处理日期和时间数据时，我们经常需要执行一些特定的任务，如获取月份的最后一天，或者从日期中提取特定的时间单位。在MySQL中，有一些函数专门用于这类特殊处理，它们可以极大地简化我们的工作。

6.1 LAST_DAY()函数的月份末尾天数获取

6.1.1 LAST_DAY()函数的原理

LAST_DAY()函数是一个非常有用的函数，它能够返回给定日期所在月份的最后一天。这个函数只需要一个参数，即一个有效的日期值。它对于计算工资、发票到期日、或是任何需要月底日期的场景都十分有用。

LAST_DAY(date)

这里的 date 参数是一个合法的日期值，返回值是给定日期所在月份的最后一天的日期。

6.1.2 LAST_DAY()函数的应用场景

假设你正在开发一个财务应用程序，需要为每笔交易计算到期日。在这种情况下，LAST_DAY()函数就可以派上用场。

SELECT LAST_DAY('2023-02-01') AS last_day_of_month;

此查询将返回 2023-02-28 ，因为2023年2月没有29日。

6.2 EXTRACT()函数的数据提取

6.2.1 EXTRACT()函数的基本使用

EXTRACT()函数能够从日期时间表达式中提取出单独的日期部分。这个函数可以指定一个时间单位，并返回这个单位对应的值。其语法如下：

EXTRACT(unit FROM date)

unit 参数是一个关键字，可以是以下之一： MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH。 date 参数是需要从中提取数据的日期时间表达式。

6.2.2 从日期时间中提取特定部分的实例

假如我们需要从某个时间戳中提取出星期几的信息，可以使用EXTRACT()函数来实现。

SELECT EXTRACT(DAYOFWEEK FROM '2023-03-21') AS day_of_week;

查询结果将是数字表示的星期几，例如，如果结果是7，则表示星期六。

通过这些例子，我们可以看到，利用LAST_DAY()和EXTRACT()等特殊日期时间处理函数，我们可以以更高的效率和精度来处理涉及日期和时间的复杂数据。这些函数的深入理解和灵活运用，可以极大地提升数据库操作的专业性和精确度。

本文还有配套的精品资源，点击获取

简介：MySQL中的时间日期相关函数是数据库管理的核心组件，它们让数据库管理员能够高效地处理日期和时间数据。这些函数涉及当前日期和时间的获取、格式转换、时间间隔计算及日期时间比较等方面。常用函数包括NOW()获取当前日期和时间，CURDATE()和CURTIME()分别获取当前日期和时间，以及DATE()和TIME()从日期时间值中提取相应部分。此外，STR_TO_DATE()和DATE_FORMAT()可以转换和格式化日期和时间字符串，DATE_ADD()和DATE_SUB()用于计算时间的加减，INTERVAL用于指定时间间隔。还有YEAR()、MONTH()、DAY()等函数用于提取特定的日期成分，ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的别名，TIMESTAMPDIFF()计算时间差，FROM_UNIXTIME()和UNIX_TIMESTAMP()分别实现时间戳与日期时间的转换，LAST_DAY()返回某月的最后一天，而EXTRACT()可以从日期时间中提取指定部分。掌握这些函数可以大幅提高数据处理效率，如示例查询所示，可找出特定日期范围内的记录。这些函数的深入理解对于任何使用MySQL的开发人员都是必不可少的。

本文还有配套的精品资源，点击获取