第04章05节 查询DQL--数据处理函数（日期和时间）

数据处理函数(日期和时间相关函数)

一、获取当前日期和时间

image.png image.png image.png

now()和sysdate()的区别：

  • now()：获取的是执行select语句的时刻。
  • sysdate()：获取的是执行sysdate()函数的时刻。

二、获取当前日期

获取当前日期有三种写法，掌握任意一种即可：

  1. curdate()
  2. current_date()
  3. current_date
image.png

三、获取当前时间

获取档期时间有三种写法，掌握其中一种即可：

  1. curtime()
  2. current_time()
  3. current_time
image.png

四、获取单独的年、月、日、时、分、秒

1
2
3
4
5
6
7
8
1. select year(now());
2. select month(now());
3. select day(now());
4. select hour(now());
5. select minute(now());
6. select second(now());
7. select date(now());
8. select time(now());
image.png image.png

注意：这些函数在使用的时候，需要传递一个日期参数给它，它可以获取到你给定的这个日期相关的年、月、日、时、分、秒的信息。
一次性提取一个给定日期的“年月日”部分，可以使用date()函数，例如：

image.png

一次性提取一个给定日期的“时分秒”部分，可以使用time()函数，例如：

image.png

五、date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。
date_add函数的语法格式：date_add ( 日期, interval expr 单位 )

例如：

image.png

以’2023-01-03’为基准，间隔3天之后的日期：’2023-01-06’

image.png

以’2023-01-03’为基准，间隔3个月之后的日期：’2023-04-03’

详细解释一下这个函数的相关参数：date_add ( 日期, interval expr 单位 )

  • 日期：一个日期类型的数据
  • interval：关键字，翻译为“间隔”，固定写法
  • expr：指定具体的间隔量，一般是一个数字。也可以为负数，如果为负数，效果和date_sub函数相同。
  • 单位：
    • year：年
    • month：月
    • day：日
    • hour：时
    • minute：分
    • second：秒
    • microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）
    • week：周
    • quarter：季度

请分析下面这条SQL语句所表达的含义：

image.png

以上SQL表示：以2022-10-01 10:10:10为基准，在这个时间基础上添加-1微秒，也就是减去1微秒。

以上SQL也可以采用date_sub函数完成，例如：

image.png

另外，单位也可以采用复合型单位，例如：

  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND：几分几秒之后
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE：几小时几分之后
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR：几天几小时之后
  • YEAR_MONTH：几年几个月之后

如果单位采用复合型的话，expr该怎么写呢？例如单位采用：day_hour，假设我要表示3天2小时之后，怎么写？

image.png

‘3,2’这个应该很好理解，表示3天2个小时之后。’3,2’和day_hour是对应的。

六、date_format日期格式化函数

将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型）
语法格式： date_format ( 日期 , '日期格式' )
该函数有两个参数：

  • 第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。
  • 第二个参数：指定要格式化的格式字符串。
    • %Y：四位年份
    • %y：两位年份
    • %m：月份（1..12）
    • %d：日（1..30）
    • %H：小时（0..23）
    • %i：分（0..59）
    • %s：秒（0..59）

例如：获取当前系统时间，让其以这个格式展示：10:55:22 2024-06-06

注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串：

image.png

自定义员工入职日期格式

1
select ename,sal,date_format(hiredate, '%y/%m/%d') as hiredate from emp;

七、str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）
假设有一个学生表t_student，学生有一个生日的字段，类型是date类型：

1
2
3
4
5
6
drop table if exists t_student;
create table t_student(
  name varchar(255),
  birth date
);
desc t_student;

我们要给这个表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：

image.png

错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串’10/01/1985’我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：

image.png

image.png

当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：

image.png

如果日期格式符合以上的几种格式，mysql都会自动进行类型转换的。

八、dayofweek、dayofmonth、dayofyear函数

  • dayofweek：一周中的第几天（1~ 7），周日是1，周六是7。
  • dayofmonth：一个月中的第几天（1~31）
  • dayofyear：一年中的第几天（1~366）
image.png

九、last_day、datediff、timediff函数

9.1 last_day函数

获取给定日期所在月的最后一天的日期：

image.png

9.2 datediff函数

计算两个日期之间所差天数：

image.png

时分秒不算，只计算日期部分相差的天数。

9.3 timediff函数

计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

image.png