10.4 日期和时间函数

在MySQL中，日期和时间函数是处理数据库中时间相关数据的强大工具。无论是进行数据的时间分析、生成报告，还是确保数据的时间一致性，这些函数都扮演着至关重要的角色。本章节将详细介绍MySQL中常用的日期和时间函数，包括日期提取、时间计算、日期格式化等各个方面，帮助读者从入门到精通MySQL中的日期和时间处理技巧。

10.4.1 日期和时间基础

在深入探讨函数之前，先简要回顾一下MySQL中日期和时间的表示方法。MySQL支持多种日期和时间类型，包括DATE（仅日期）、TIME（仅时间）、DATETIME（日期和时间）、TIMESTAMP（时间戳，具有时区特性）、YEAR（年份）等。每种类型都有其特定的格式和应用场景。

10.4.2 当前日期和时间

• NOW()：返回当前的日期和时间，格式为'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'，具体取决于函数的调用方式（是否带有参数）或服务器的SQL模式。
• CURDATE()：仅返回当前日期，格式为'YYYY-MM-DD'。
• CURTIME()：仅返回当前时间，格式为'HH:MM:SS'。

10.4.3 日期和时间提取

MySQL提供了一系列函数来从日期或时间值中提取特定的部分，如年、月、日、小时等。

• YEAR(date)：从日期或日期时间表达式中提取年份。
• MONTH(date)：提取月份。
• DAY(date)：提取日。
• HOUR(time)：从时间或日期时间表达式中提取小时。
• MINUTE(time)：提取分钟。
• SECOND(time)：提取秒。
• DAYOFWEEK(date)：返回日期是周几（1=周日，2=周一，…，7=周六）。
• DAYOFYEAR(date)：返回日期是一年中的第几天（1-366）。

10.4.4 日期和时间计算

MySQL允许通过加减操作对日期和时间进行简单的计算，同时也提供了专门的函数来处理更复杂的日期时间运算。

• DATE_ADD(date, INTERVAL expr type)：在日期上加上一个时间间隔。expr是你要加上的时间量，type是间隔的单位（如DAY、MONTH、YEAR等）。
• DATE_SUB(date, INTERVAL expr type)：从日期上减去一个时间间隔，与DATE_ADD相反。
• ADDDATE(date, INTERVAL expr type) / SUBDATE(date, INTERVAL expr type)：ADDDATE和SUBDATE是DATE_ADD和DATE_SUB的同义词，功能上完全一致。
• DATEDIFF(expr1, expr2)：计算两个日期之间的天数差。
• TIMEDIFF(expr1, expr2)：计算两个时间或日期时间值之间的差异，结果是一个时间值。

10.4.5 日期和时间格式化

格式化日期和时间输出是常见的需求，MySQL的DATE_FORMAT()函数为此提供了强大的支持。

• DATE_FORMAT(date, format)：根据指定的格式字符串format来格式化日期或时间值。format字符串中可以包含各种格式化代码，如%Y代表四位年份，%m代表月份，%d代表日，%H代表小时（24小时制），%i代表分钟，%s代表秒等。

10.4.6 时间戳函数

时间戳是自1970年1月1日（称为Unix纪元）以来的秒数（或毫秒数，取决于具体的函数和系统）。MySQL中的UNIX_TIMESTAMP()函数和FROM_UNIXTIME()函数用于处理时间戳。

• UNIX_TIMESTAMP()：无参数时返回当前时间的UNIX时间戳；带日期参数时，返回该日期的UNIX时间戳。
• FROM_UNIXTIME(unix_timestamp)：将UNIX时间戳转换为'YYYY-MM-DD HH:MM:SS'格式的日期时间值。

10.4.7 实战应用

理解上述函数后，我们来看几个实际的应用场景。

• 场景一：计算员工入职天数：假设有一个员工表employees，包含字段hire_date（入职日期），可以使用DATEDIFF(CURDATE(), hire_date)来计算每个员工的入职天数。
• 场景二：格式化订单日期：在订单系统中，可能需要将订单日期按照特定格式（如YYYY年MM月DD日）展示给用户，这时可以使用DATE_FORMAT(order_date, '%Y年%m月%d日')来实现。
• 场景三：计算下个月的第一个星期一：这可能需要结合多个函数来实现，如先使用DATE_ADD()加上一个月，然后使用WEEKDAY()函数和条件逻辑来找到第一个星期一。

10.4.8 注意事项

• 在使用日期和时间函数时，务必注意输入值的类型和格式，确保它们与函数要求的参数类型相匹配。
• 考虑到时区的影响，特别是在处理TIMESTAMP类型的数据时，需要了解并正确设置MySQL服务器的时区设置，以避免因时区差异导致的数据不一致问题。
• 在进行日期时间计算时，要注意不同月份的天数差异（如2月可能只有28天或29天）以及闰年的情况。

通过本章节的学习，读者应该能够熟练掌握MySQL中日期和时间函数的使用，并能够灵活应用到实际的数据处理和分析工作中。无论是简单的日期提取和格式化，还是复杂的日期时间计算和转换，都能得心应手。