mysql 日期相减，并将结果以“x天x小时x分钟”显示

WizAOO

已于 2023-04-07 10:59:01 修改

阅读量504

点赞数 1

分类专栏： mysql 文章标签： mysql

于 2023-04-07 10:47:58 首次发布

本文链接：https://blog.csdn.net/m0_68503106/article/details/130006772

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

两个日期相减：

if 
( b.assign_time - str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ) > 0,
  NULL,
  CONCAT(
  IF(FLOOR( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ) / 24 ) = 0,
    '',
    CONCAT( FLOOR( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ), b.assign_time ) ) / 24 ), '天' ) ),
  IF(FLOOR( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ) / 24 ) >= 1,
   IF(MOD ( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ), 24 ) = 0,'0小时',
     CONCAT( MOD ( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ), 24 ), '小时' ) ),
   IF(MOD ( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ), 24 ) = 0,
     '',
     CONCAT( MOD ( HOUR ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ), 24 ), '小时' ) ) ),
  IF(MINUTE ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ) = 0,
    '1分钟',CONCAT( MINUTE ( TIMEDIFF( str_to_date(a.create_time, '%Y-%m-%d %H:%i:%s' ),b.assign_time ) ), '分钟' ) ) ) )

三个日期相减(全部换算成秒后计算)：

CONCAT(	case when ((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) *24*60*60
						            -TIMESTAMPDIFF( SECOND,a.create_time, now( ) ))<0 then '-' else'' end ,			
					CONCAT(
						IF ( (FLOOR((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) *24*60*60
						            -TIMESTAMPDIFF( SECOND,a.create_time, now( ) )) / (24*60*60)) = 0, '0',
									FLOOR(abs(((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) 
									*24*60*60-TIMESTAMPDIFF( SECOND,a.create_time, now( ) )) / (24*60*60)))), '天' ) ,
					CONCAT(
					 IF ( FLOOR(((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) *24*60*60
					              -TIMESTAMPDIFF( SECOND,a.create_time, now( ) )) mod (24*60*60)/3600) = 0, '0',
									FLOOR(abs(((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) 
									*24*60*60-TIMESTAMPDIFF( SECOND,a.create_time, now( ) )) mod (24*60*60)/3600))), '小时' ) ,
					CONCAT(
					 IF ( FLOOR(((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) *24*60*60
					             -TIMESTAMPDIFF( second,a.create_time, now( ) )) mod (24*60)/60) = 0, '0',
									TIME_FORMAT(SEC_TO_TIME(abs((CASE b.process_state  WHEN 'UNAUDITED' THEN 1 WHEN 'REJECTREVIEW' THEN 1 ELSE 0 END) 
									*24*60*60-TIMESTAMPDIFF( SECOND,a.create_time, now( ) ))), '%i')),'分钟' )						
				 
				)