postgresql 一个需求：围绕两个字段时间差得到时分秒格式

阳光穿透心脏的1/2处 2023-02-24 14:25 64阅读 0赞

初步sql，思路是先获取两个时间差的秒数，再相除获取时钟和分钟  
(没有考虑负时间)

select case when char_length(trunc(a.between_sec/a.hour_sec)::varchar ) <=2 
                     then lpad(trunc(a.between_sec/a.hour_sec)::varchar,2,'0')
    		    else trunc(a.between_sec/a.hour_sec)::varchar
            end||':'||
            lpad(trunc((a.between_sec-trunc(a.between_sec/a.hour_sec)*a.hour_sec)/a.min_sec)::varchar,2,'0') || ':' ||
    		lpad((a.between_sec - trunc(a.between_sec/a.hour_sec) * a.hour_sec - trunc((a.between_sec-trunc(a.between_sec/a.hour_sec)*a.hour_sec)/a.min_sec)*a.min_sec)::varchar,2,'0')
     from ( select extract(epoch FROM (now() - (now()-interval '2.11 day') )) as between_sec,
    			   60*60 as hour_sec,
    		       60    as min_sec
          ) a;

![在这里插入图片描述][20200709173317511.png]

优化后的sql如下(同时考虑到了负时间)

select case when (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) ) < 0
    				  and (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) ) >= -9
    				  then  '-0'||abs( (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) ) )::varchar
    			when (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) ) >= 0
    				  and (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) ) <= 9
    		    	  then  '0'||(extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) )::varchar	
    			else (extract(day FROM ( a.bet ))*a.day_hour + extract(hour FROM ( a.bet )) )::varchar		 
            end || ':' ||
           case when extract(minute FROM ( a.bet )) < 0 and extract(minute FROM ( a.bet )) >= -9
    			     then '-0'||abs(extract(minute FROM ( a.bet )))::varchar
    			when extract(minute FROM ( a.bet )) >=0 and extract(minute FROM ( a.bet )) <= 9
    			     then '0'||(extract(minute FROM ( a.bet ))::varchar)
    			else extract(minute FROM ( a.bet ))::varchar
    		end || ':' ||
    	    case when extract(second FROM ( a.bet )) < 0 and extract(second FROM ( a.bet )) >= -9
    	              then '-0'||abs(extract(second FROM ( a.bet )))::varchar
    			 when extract(second FROM ( a.bet )) >=0 and extract(second FROM ( a.bet )) <= 9
    				  then '0'||(extract(second FROM ( a.bet ))::varchar)
    			 else extract(second FROM ( a.bet ))::varchar
    		 end as between_str 
      from ( select (now() - (now()-interval '2.11 day') ) as bet,
    				24     as day_hour,
    				60*60  as hour_sec,
    				60     as min_sec
    	      ) a;

![在这里插入图片描述][20200709173347873.png]

[20200709173317511.png]: /images/20230209/267f46bc1a924850a6d8f9fcbe5751bb.png
[20200709173347873.png]: https://img-blog.csdnimg.cn/20200709173347873.png