MySQL 日期函数、时间函数在实际场景中的应用

  • 阿里云国际版折扣https://www.yundadi.com

  • 阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6
    整理日常业务中用到日期函数的一些场景,并对日期函数按照使用类型做了分类,实例也尽可能符合日常需求。为了方便查阅,可以先看目录,再根据需要看具体方法和实例。

    整理日常业务中用到日期函数的一些场景,并对日期函数按照使用类型做了分类,实例也尽可能符合日常需求。为了方便查阅,可以先看目录,再根据需要看具体方法和实例。
    首先明确日期和时间类型有哪些,也就是日期函数括号中的变量类型。

    一、日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

    类型 格式 描述
    DATE YYYY-MM-DD 日期值
    TIME HH:MM:SS 时间值或持续时间
    YEAR YYYY 年份值
    DATETIME YYYY-MM-DD hh:mm:ss 混合日期和时间值
    TIMESTAMP YYYY-MM-DD hh:mm:ss 混合日期和时间值,时间戳

    二、具体方法

    (1)获取年、月、日、周等

    函数 描述
    date(datetime) 返回datetime中的日期date部分
    year(date) 返回年份
    quarter(date) 返回日期date是第几季节,返回 1 到 4
    month(date) 返回日期date中的月份值,1 到 12
    week(date) 计算日期 date 是本年的第几个星期,范围是 0 到 53
    day(date) 返回日期值 date 的日期部分
    hour(time) 返回 date 中的小时值
    minute(time) 返回 date 中的分钟值
    second(time) 返回 t 中的秒钟值
    microsecond(time) 返回日期参数所对应的微秒数
    extract(type from date) 从日期 date 中获取指定的值,type 指定返回的值
    weekday(date) 日期 date 是星期几,0 表示星期一,1 表示星期二...

    本文中type的取值:

    year(年)、quarter(季)、month(月)、week(周)、day(天)、hour(小时)、minute(分钟)、second(秒)、microsecond(毫秒)。

    (2)获取年月、年月日等

    函数 描述
    year、month、day 使用and连接单个返回值
    date_format(event_time,’unit‘) 按照unit取值
    substring(date, start, length) 从字符串 str 的 start 位置开始截取长度为 length 的子字符串
    like 模糊查询

    本文中unit的取值:

    %M 月名字(January……December) 
    %W 星期名字(Sunday……Saturday) 
    %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。) 
    %Y 年, 数字, 4 位 
    %y 年, 数字, 2 位 
    %a 缩写的星期名字(Sun……Sat) 
    %d 月份中的天数, 数字(00……31) 
    %e 月份中的天数, 数字(0……31) 
    %m 月, 数字(01……12) 
    %c 月, 数字(1……12) 
    %b 缩写的月份名字(Jan……Dec) 
    %j 一年中的天数(001……366) 
    %H 小时(00……23) 
    %k 小时(0……23) 
    %h 小时(01……12) 
    %I 小时(01……12) 
    %l 小时(1……12) 
    %i 分钟, 数字(00……59) 
    %r 时间,12 小时(hh:mm:ss [AP]M) 
    %T 时间,24 小时(hh:mm:ss) 
    %S 秒(00……59) 
    %s 秒(00……59) 
    %p AM或PM 
    %w 一个星期中的天数(0=Sunday ……6=Saturday ) 
    %U 星期(0……52), 这里星期天是星期的第一天 
    %u 星期(0……52), 这里星期一是星期的第一天 
    %% 一个文字“%”。 
    

    例如:date_format(%Y-%m-%d %H:%i:%s)得到时间戳

    (3)两个日期时间的整数差

    函数 描述
    datediff(date_1,date_2) 返回日期 date_1 - date_2 之间相隔的天数
    timestampdiff(type,datetime_1,datetime_2) 返回 datetime_2 − datetime_1 的时间差
    timediff(time_1, time_2)计算时间差值 返回time_1 - time_2 的时间差值

    参数说明:

    type:参数确定(end-begin)结果的单位,表示为整数。可以填year(年)、quarter(季)、month(月)、week(周)、day(天)、hour(小时)、minute(分钟)、second(秒)、microsecond(毫秒)。

    返回datetime_2-datetime_1的结果,其中datetime_1和datetime_2是date或者datetime表达式,也可以是混合类型;

    当datetime_1>datetime_2时,返回的结果为负;

    当datetime_1<datetime_2时,返回的结果为正。

    • 什么时候使用datediff?什么时候使用timestampdiff?什么时候使用timediff?
    三个函数的区别主要在于对象的不同一个是日期,一个是时间,另外一个既可以是日期格式也可以是时间格式。
    timestampdiff更加灵活,既可以对日期求整数差也可以对时间求整数差。而datediff只能求日期的天数差。
    

    (4)日期减去/加上天数得到日期 date_sub、date_add

    函数 描述
    date_add(date,interval expr type) 返回起始日期 date 加上一个时间段后的日期
    date_sub(date,interval expr type) 返回函数从日期减去指定的时间间隔
    addtime(time,n) 返回n 是一个时间表达式,时间 t 加上时间表达式 n
    subtime(time,n) 时间 t 减去 n 秒的时间

    (5)日期date是周几

    函数 描述
    weekday(date) 日期 date 是星期几,0 表示星期一,1 表示星期二...
    date_format(date,'%W') 日期 date 是星期几,Monday周一,Tuesday周二...
    week(date) 计算日期 date 是本年的第几个星期,范围是 0 到 53

    (6)某个月的最后一天

    函数 描述
    last_day(date) 返回给定日期的那一月份的最后一天

    (7)返回当前日期/时间

    函数 描述
    curdate()、current_date() 返回当前日期
    curtime()、current_time() 返回当前时间
    now() 返回当前日期和时间

    三、实例

    分为两部分,简单和复杂实例,简单实例是为了便于理解函数的使用,复杂实例是结合业务场景给出了常见的问题。

    1、简单实例

    • 获取年、月、日、周等
    # 返回'2023-01-09 22:32:17'的 年月日等 部分
    select
    	date('2023-01-09 22:32:17');			  -- 2023-01-09
    select
    	year('2023-01-09 22:32:17');			  -- 2023
    select
    	quarter('2023-01-09 22:32:17');			  -- 1
    select
    	month('2023-01-09 22:32:17');			  -- 1
    select
    	week('2023-01-09 22:32:17');			  -- 2(本年第二周)
    select
    	day('2023-01-09 22:32:17');			      -- 9
    select
    	hour('2023-01-09 22:32:17');			  -- 22
    select
    	minute('2023-01-09 22:32:17');			  -- 32
    select
    	second('2023-01-09 22:32:17');			  -- 17
    select
    	extract(day from '2023-01-09 22:32:17');  -- 9
    select
    	weekday('2023-01-09 22:32:17');			-- 0(周一)
    
    • 获取年月、年月日等
    # 返回'2023-01-09 22:32:17'的 年月、年月日等部分
    select 
    	date_format('2023-01-09 22:32:17','%Y%m');						  -- 202301
    select 
    	date_format('2023-01-09 22:32:17','%y%m');						  -- 2301
    select 
    	date_format('2023-01-09 22:32:17','%Y-%m-%d');					  -- 2023-01-09
    select 
    	date_format('2023-01-09 22:32:17','%H:%i');					  	  -- 22:32
    select 
    	date_format('2023-01-09 22:32:17','%s');					  	  -- 17
    select 
    	substring('2023-01-09 22:32:17',1,7);						  	  -- 2023-01
    select 
    	substring('2023-01-09 22:32:17',1,10);						  	  -- 2023-01-09
    select 
    	substring('2023-01-09 22:32:17',12,5);						  	  -- 22:32
    select 
    	substring('2023-01-09 22:32:17',18,2);						  	  -- 17
    
    • 两个日期时间的整数差/和
    # 返回'2023-01-09 22:32:17'和 '2023-03-15 04:18:56'的 整数差或者和
    select 
    	datediff('2023-01-09 22:32:17','2023-03-15 04:18:56');			  	  -- -65
    select 
    	datediff('2023-03-15 04:18:56','2023-01-09 22:32:17');			  	  -- 65
    	
    select 
    	timediff('2023-01-09 22:32:17','2023-03-15 04:18:56');			  	  -- -838:59:59
    select 
    	timediff('2023-03-15 04:18:56','2023-01-09 22:32:17');			  	  -- 838:59:59
    	
    select 
    	timestampdiff(year,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 0
    select 
    	timestampdiff(year,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- 0
    	
    select 
    	timestampdiff(quarter,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 0
    select 
    	timestampdiff(quarter,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- 0
    	
    select 
    	timestampdiff(month,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 2
    select 
    	timestampdiff(month,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -2
    	
    select 
    	timestampdiff(week,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 9
    select 
    	timestampdiff(week,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -9
    	
    select 
    	timestampdiff(day,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  	  -- 64
    select 
    	timestampdiff(day,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  	  -- -64
    	
    select 
    	timestampdiff(hour,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 1541
    select 
    	timestampdiff(hour,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -1541
    	
    select 
    	timestampdiff(minute,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 92506
    select 
    	timestampdiff(minute,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -92506
    	
    select 
    	timestampdiff(second,'2023-01-09 22:32:17','2023-03-15 04:18:56');	  -- 5550399
    select 
    	timestampdiff(second,'2023-03-15 04:18:56','2023-01-09 22:32:17');	  -- -5550399
    
    • 日期减去/加上天数得到日期 date_sub、date_add
    # 返回日期'2023-01-09 22:32:17'加上或者减去 某个时间段 的日期
    select 
    	date_add('2023-01-09 22:32:17',interval 2 year);			  -- 2025-01-09 22:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 quarter);			  -- 2023-07-09 22:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 month);		  	  -- 2023-03-09 22:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 week);		  	  -- 2023-01-23 22:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 day);			  	  -- 2023-01-11 22:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 hour);			  -- 2023-01-10 00:32:17
    select 
    	date_add('2023-01-09 22:32:17',interval 2 second);			  -- 2023-01-09 22:32:19
    select 
    	date_add('2023-01-09 22:32:17',interval 2 minute);			  -- 2023-01-09 22:34:17
    
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 year);			  -- 2025-01-09 22:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 quarter);			  -- 2023-07-09 22:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 month);		  	  -- 2023-03-09 22:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 week);		  	  -- 2023-01-23 22:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 day);			  	  -- 2023-01-11 22:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 hour);			  -- 2023-01-10 00:32:17
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 second);			  -- 2023-01-09 22:32:19
    select 
    	date_sub('2023-01-09 22:32:17',interval 2 minute);			  -- 2023-01-09 22:34:17
    # 时间格式默认秒
    select 
    	addtime('2023-01-09 22:32:17','22:12:00');			  		  -- 2023-01-10 20:44:17
    select 
    	addtime('2023-01-09 22:32:17','22:12');			  			  -- 2023-01-10 20:44:17
    select 
    	addtime('2023-01-09 22:32:17','22');			  			  -- 2023-01-09 22:32:39
    	
    select 
    	subtime('2023-01-09 22:32:17','22:12:00');			  		  -- 2023-01-09 00:20:17
    select 
    	subtime('2023-01-09 22:32:17','22:12');			  			  -- 2023-01-09 00:20:17
    select 
    	subtime('2023-01-09 22:32:17','22');			  			  -- 2023-01-09 22:31:55
    
    • 日期date是周几
    # 返回日期'2023-01-09 22:32:17' 是周几
    select 
    	weekday('2023-01-09 22:32:17');			  				  -- 0(周一)
    select 
    	week('2023-01-09 22:32:17');			  				  -- 2(本年第二周)
    select 
    	date_format('2023-01-09 22:32:17','%W');			  	  -- Monday(周一的英文)
    
    • 某个月的最后一天
    # 返回日期'2023-01-09 22:32:17' 该月最后一天
    select 
    	last_day('2023-01-09 22:32:17');			  				  -- 2023-01-31
    
    • 返回当前日期/时间
    # 返回当前日期或者时间
    select 
    	curdate(),			  				  	  -- 2023-01-23
    	current_date(),			  				  -- 2023-01-23
    	curtime(),			  				  	  -- 23:32:37
    	current_time(),			  				  -- 23:32:37
    	now();  								  -- 2023-01-23 23:32:37
    

    2、复杂实例

    students 表 (id表示主键,student_id是学号,score是平均成绩,date是作答日期)

    id student_id score answer_date
    1 1123 98 2023-01-08
    2 2341 74 2022-02-12
    3 5623 66 2022-12-23
    4 4386 80 2023-01-12

    (1)统计每个月12号作答的次数——month、day

    #使用month、day
    select 
    	month(answer_date) as month,
    	count(answer_date) as count
    from students_score
    where day(answer_date) = 12
    group by month;
    
    • 结果
    month  count
    1		1
    2		1
    

    (2)统计2023年1月作答的次数——year、month、day、date_format、substring

    #1、使用year、month、day
    select 
    	count(answer_date) 
    from students_score
    where month(answer_date) = 1 and year(answer_date) = 2023;
    
    #2、使用date_format
    select 
    	count(answer_date) 
    from students_score
    where date_format(answer_date,'%Y%m') = '202301';
    
    #3、使用substring
    select 
    	count(answer_date) 
    from students_score
    where substring(answer_date,1,7) = '2023-01';
    
    #4、使用like
    select 
    	count(answer_date) 
    from students_score
    where answer_date like '2023-01%';
    
    #结果:2
    

    (3)计算2022-02-12是2022年的第几周——week(date)

    select week('2022-02-12');   #6(表示第六周)
    

    (4)计算2023-01-05是周几——date_format(date,'%W')

    select date_format('2023-01-05','%W');   #Thursday(表示周四)
    

    (5)输出students_score表中2023年在周一到周五作答的学生学号——weekday(date)

    #1、使用weekday
    select 
    	student_id,
    	weekday(answer_date) as week
    from students_score
    where weekday(answer_date) < 5;
    
    #2、使用date_format(date,'%W')
    select 
    	student_id,
    	date_format(answer_date,'%W') as week
    from students_score
    where date_format(answer_date,'%W') in ('Monday','Tuesday','Wednesday','Thursday','Friday');
    

    注意两种方法的区别

    • 结果
    student_id  week
    5623	4
    4386	3
    
    5623	Friday
    4386	Thursday
    

    (6)计算每月平均作答次数——last_day(date)

    select 
    	date_format(answer_date,'%Y%m') as date_,
    	count(answer_date)/day(last_day(answer_date)) as avg_num
    from students_score
    group by date_;
    
    • 结果
    date_	avg_num
    202202	0.0357
    202212	0.0323
    202301	0.0645
    

    (7)近7天和近30天分别作答题目数量

    如何计算近7天和近30天的日期:日期减去天数得到日期;另一种是日期减去日期得到天数。

    • [使用]:date_sub(date,interval expr type)、datediff(date,dt_max) 、timestampdiff(day,date,dt_max)
    #先求出今日是哪一天(最大日期),往前七天是哪一天,往前30天是哪一天
    with 
    	main as(
        	select
    			max(date(answer_date)) as now_date,
    			date_sub(max(date(answer_date)),interval 6 day) as 7_date,
    			date_sub(max(date(answer_date)),interval 29 day) as 30_date
    		from students_score
        )
    
    select 
    	sum(if(datediff(answer_date,7_date)) >= 0,1,0) as ans_7_num,
    	sum(if(datediff(answer_date,30_date)) >= 0,1,0) as ans_30_num
    from students_score, main
    
    • 结果
    ans_7_num	ans_30_num
    2				3
    

    创建 students_score 表的代码

    -- ----------------------------
    -- Table structure for students_score
    -- ----------------------------
    DROP TABLE IF EXISTS `students_score`;
    CREATE TABLE `students_score`  (
      `id` int(11) NOT NULL COMMENT '主键',
      `student_id` int(11) NOT NULL COMMENT '学号',
      `score` int(11) NULL DEFAULT NULL COMMENT '成绩',
      `date` date NOT NULL COMMENT '作答日期',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of students_score
    -- ----------------------------
    INSERT INTO `students_score` VALUES (1, 1123, 98, '2023-01-08');
    INSERT INTO `students_score` VALUES (2, 2341, 74, '2023-01-12');
    INSERT INTO `students_score` VALUES (3, 5623, 66, '2022-12-23');
    INSERT INTO `students_score` VALUES (4, 4386, 80, '2023-01-12');
    
  • 阿里云国际版折扣https://www.yundadi.com

  • 阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6
    标签: mysql

    “MySQL 日期函数、时间函数在实际场景中的应用” 的相关文章