oracle】将两Timestamp字段的差值变成时分秒毫秒的形式

【代码】

package com.hy.lab.timestampdiff;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class Test {
    public static void main(String[] args) throws Exception{
        final String sql="select id,start_time,end_time from emp73 order by id";

        try(Connection conn=DbUtil.getConn();
            PreparedStatement pstmt=conn.prepareStatement(sql)){

            ResultSet rs=pstmt.executeQuery();

            while(rs.next()){
                int id=rs.getInt(1);
                Date startDate=rs.getDate(2);
                Date endDate=rs.getDate(3);

                System.out.println(String.format("%d-%s",id,getDhms(startDate,endDate)));
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    private static String getDhms(Date startDate,Date endDate){
        return ms2DHMS(startDate.getTime(),endDate.getTime());
    }

    private static String ms2DHMS(long startMs, long endMs) {
        String retval = null;
        long secondCount = (endMs - startMs) / 1000;
        String ms = (endMs - startMs) % 1000 + "ms";

        long days = secondCount / (60 * 60 * 24);
        long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
        long minutes = (secondCount % (60 * 60)) / 60;
        long seconds = secondCount % 60;

        if (days > 0) {
            retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            retval = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            retval = minutes + "m" + seconds + "s";
        } else if(seconds > 0) {
            retval = seconds + "s";
        }else {
            return ms;
        }

        return retval + ms;
    }
}

【输出】

1-1s0ms
2-1m1s0ms
3-1h0m1s0ms
4-1d0h0m1s0ms
5-31d0h0m1s0ms

注：如果毫秒不需要可以到ms2DHMS中进行调整。

【建表及充值】

create table emp73(
    id number(3),
    start_time timestamp,
    end_time timestamp,
    primary key(id)
);

insert into emp73(id,start_time,end_time) values(1,to_date('20220701 12:23:34','yyyymmdd hh24:mi:ss'),to_date('20220701 12:23:35','yyyymmdd hh24:mi:ss'));
insert into emp73(id,start_time,end_time) values(2,to_date('20220701 12:23:34','yyyymmdd hh24:mi:ss'),to_date('20220701 12:24:35','yyyymmdd hh24:mi:ss'));
insert into emp73(id,start_time,end_time) values(3,to_date('20220701 12:23:34','yyyymmdd hh24:mi:ss'),to_date('20220701 13:23:35','yyyymmdd hh24:mi:ss'));
insert into emp73(id,start_time,end_time) values(4,to_date('20220701 12:23:34','yyyymmdd hh24:mi:ss'),to_date('20220702 12:23:35','yyyymmdd hh24:mi:ss'));
insert into emp73(id,start_time,end_time) values(5,to_date('20220701 12:23:34','yyyymmdd hh24:mi:ss'),to_date('20220801 12:23:35','yyyymmdd hh24:mi:ss'));