课时15 第十讲光阴可数 —— 处理日期与时间 15:08

JiaqiFan42

Programmer

课时15 第十讲光阴可数 —— 处理日期与时间 15:08

要知道对于从事电子商务银行会计等等的朋友来说，日期比如交易日期开户日期等等可以说是最重要的信息之一。所以咱们今天就研究一下怎样在SQL中表示日期时间，怎样使用函数来处理和筛选日期与时间

大多数的主流数据库软件都特意设计了几种类型专门用来表示时间数据。

比如，在MySQL数据库中就有三种数据类型：

DATE（仅年月日）
DATETIME（日期+时间）
TIMESTAMP（时间戳，1970年至今的秒或毫秒数）

均可以用来保存时间。

这些细分类型的区别和适用范围讲起来比较复杂，而且其他数据库在这些类型上又有进一步的区别，基础篇的最后会专门的有一两节课绐大家讲解数据库的时间类型

这节课，我们就以 MySQL 中最常见的 DATETIME 类型为例来讲解怎样处理时间数据

案例

假设一个直播平台，斗虾直播，有个打赏收入表，上面记录了谁给谁打赏了多少钱，并且每条记录都有打赏时间和到账时间，这两个时间字段都是 DATETIME 类型

MySQL、SQLSERVER 等常见数据库中都有名为 DATETIME 的类型,可以保存“日期+时间”形式的完整时间数据（年月日时分秒）。

筛选精确的日期和时间

比如查找特定时日期和时间的那条打赏记录：

SELECT *
FROM 打赏收入
WHERE 打赏时间 = 2020-09-04 15:19:53

结果报错。

注意，虽然时间日期类型不是数字也不是字符串，但在 SQL 语句中书写具体日期时间数值，必须用单引号括起来，以类似字符串的形式写在 SQL 语句里：

……
WHERE 打赏时间 = '2020-09-04 15:19:53'

这次就不会报错了。

要再说明一点的是，虽然我们打赏了单引号，但其实 SQL 并不是按照字符串去跟它比较，而仍然是把它看做一个日期类型的数据。

为了证明这一点，我们可以把日期间的 “-” 换成 “/”：

……
WHERE 打赏时间 = '2020/09/04 15:19:53'

仍然能筛选出正确的结果，因为对于 MySQL 来说用减号隔开年月日与用斜线隔开年月日，都是有效规范的日期表示方法。（不过如果改成用空格来间隔年月日等 MySQL 认为不规范的时间表示格式，就会报错：Incorrect DATETIME value（错误的 DATETIME 数值））

筛选某一天/月的记录

很少能记得特定的时间的，更实用的需求是，怎样查找某一天之内的所有记录？

可以直接只写日期吗：

……
WHERE 打赏时间 = '2020-09-04'

没报错，但找不到结果。

因为，对于 DATETIME 类型，如果只写日期不指定时间的话，则 SQL 会默认是该日的0点0分0秒，而该时刻没有打赏记录。

可以在打赏记录中发现 9 月 12 号的一条打赏记录恰好位于 00:00:00，所以搜索：

……
WHERE 打赏时间 = '2020-09-12'

会得到该条记录，而这也可以验证上面的说法。

那到底如何查找某一天的记录？

有很多办法。

方法1：使用日期比较

日期时间虽然不是数字，但也有先后关系，所以也可以像数字一样进行比较运算，比如，查找 9 月 3 号的记录：

……
WHERE 打赏时间 > '2020-09-03 0:0:0'
    AND 打赏时间 < '2020-09-04 0:0:0'

又如，查找 9 月的所有记录：

……
WHERE 打赏时间 > '2020-09-01 0:0:0'
    AND 打赏时间 < '2020-10-01 0:0:0'

这种区间型的比较运算，可以用 BETWEEN …… AND …… 来进行简化

例如，BETWEEN 5 AND 8 表示 “在5和8之间的数值，包括5和8本身”

注意用 BETWEEN 和 AND 表示的区间包括了起点和终点（即，闭区间）

所以查找 9 月的记录可以简化为：

……
WHERE 打赏时间 BETWEEN '2020-09-01 0:0:0'
    AND 【'2020-09-30 23:59:59'】

BETWEEN AND 既可以用于时间日期，也可以用于数字，如，查找打赏总额在5千到一万的记录：

SELECT * FROM 打赏收入
WHERE 打赏总额 BETWEEN 5000 AND 10000;

方法2：使用时间函数

MySQL 常用时间函数：

DATE(d)：只要我们把一个时间类型的字段 d 交给他作为参数，他就可以在里面抽取出年月日的信息而忽略掉时分秒（但结果仍然是一个日期时间类型，而且应该是其中的 DATE 这个小类）
YEAR(d)：从日期 d 取得年份数字（结果为一个数字类型）
MONTH(d)：取得 d 的月份数字
DAY(d)：取得 d 的日数字
HOUR(d)：从日期 d 取得小时数字
MINUTE(d)：从d取得分钟数字
SECOND(d)：从日期d取得秒数

因此，想要查看某一天的记录，可以先用 DATE() 函数将打赏时间的年月日抽取出来再进行比较

SELECT *
FROM 打赏收入
WHERE DATE(打赏时间) = '2020-09-03';

但需要补充的是，使用Date函数虽然语法更简洁，但是在很多情况下，它的查询时间会比之前 Between大于号小于号等等花费的更多一些。但还是那句话，基础篇我们先把功能搞定，先不着急考虑效率的问题

又如，查询 9 月的记录：

……
WHERE MONTH(打赏时间) = 9;

需要注意，DATE() 取出的是年月日组成的日期，因而要与 ‘2020-09-03’ 这样加了引号的日期作比较，但 YEAR()、MONTH()、DAY()、HOUR() 等函数取出的是纯粹的数字，因此是与数字作比较，不需要加引号。

还有其它的时间日期还是可以做时间日期的运算。比如，我们的打赏使用了各种第三方平台，到账速度各不相同，当数额巨大时，到账周期就十分重要了，假设我们想看看到账周期超过 5 天的都是哪些支付平台，直接对日期相加减：

SELECT *
FROM 打赏收入
WHERE 到账时间 - 打赏时间 > 5;

得到了错误的结果，可见时间日期不能直接相加减

这里就要用到一个专门计算日期差距的函数

DATEDIFF(d1, d2) ：计算d1和d2相差多少天（结果为数字类型）

SELECT 
    交易流水号,
    打赏总额,

课时15 第十讲 光阴可数 —— 处理日期与时间 15:08

课时15 第十讲光阴可数 —— 处理日期与时间 15:08