【MySQL】MySQL日期函数官方文档及笔记

前言

MySQL 5.7日期函数官方文档
遇到了日期边界的一些问题，这里统一记录
MySQL 5.7 比较运算符的类型转换

运算符的日期比较

• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
• A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

大意为：

• 如果比较的值一个为常量，一个为TIMESTAMP 或DATETIME ，常量会被转换为时间戳。言下之意是，比较运算符支持细化到毫秒级别（甚至更高）的比较。
• 常量的自动转化失效的场景
  1. 使用in 运算符
  2. 使用子查询
• 最佳实践是把常量用CAST() 函数进行转化

where 
date_col = CAST(datetime_col AS DATE)

where
date_time_col = CAST(date_col AS DATETIME)

DATETIME 类型比较的坑

Conversion of DATETIME and TIMESTAMP values:
Conversion to a DATE value takes fractional seconds into account and rounds the time part. For example, ‘1999-12-31 23:59:59.499’ becomes ‘1999-12-31’, whereas ‘1999-12-31 23:59:59.500’ becomes ‘2000-01-01’.

• 常量1999-12-31 23:59:59 与入库显示值为1999-12-31 23:59:59的日期 可能是不相等的

谨慎使用TIMESTAMP类型

TIMESTAMP values cannot be earlier than 1970 UTC or later than ‘2038-01-19 03:14:07’ UTC. This means that a date such as ‘1968-01-01’, while valid as a DATE or DATETIME value, is not valid as a TIMESTAMP value and is converted to 0.

• TIMESTAMP 类型只能记录到 2038-01-19 03:14:07

DATEDIFF(expr1,expr2)

DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
bash
mysql> SELECT DATEDIFF(‘2007-12-31 23:59:59’,‘2007-12-30’);
-> 1
mysql> SELECT DATEDIFF(‘2010-11-30 23:59:59’,‘2010-12-31’);
-> -31

• expr1 和 expr2 可以传 '2007-12-31 23:59:59' 或 2007-12-31 格式的数据，但都只读取到天的部分即 2007-12-31 。
• 计算规则：expr1 - expr2，仅仅用天作为单位运算，并返回整数，会出现负数。

DATEDIFF(expr1,expr2) 解决的需求

根据LeetCode上题目的特点

• 表中有date类型字段activity_date
• 不能精确到时分秒的区间，只计算天数
• 过滤条件为近x天，x > 0
  近1天（表示今天） where activity_date DATEDIFF(activity_date, CURDATE()) = 0
  近2天 where activity_date DATEDIFF(activity_date, CURDATE()) = 1
  近3天 where activity_date DATEDIFF(activity_date, CURDATE()) < 3
  需要取区间，所以是 <符号，更多的天数条件也需要用<

筛选截至今天近30天内的用户

WHERE datediff(CURDATE(),activity_date) < 30

等价于

where activity_date between date_add(CURDATE(), interval -29 day) and CURDATE()

# 其中
#-29 day表示29天前的日期
# CURDATE()表示当天
# 关键字计算 between and 作用在date类型时，取闭左右区间