【java/jdbc】求Oracle表中字符串类型、Data类型、TimeStamp类型字段的时间差(时分秒hms形式)

【需求】

Oracle表中记录时间的字段有字符串类型、Date类型、Timestamp类型三种，要求取得三种类型的时间差。

【表结构】

create table emp706(
    id number(3),
    f1s nvarchar2(20),
    f1e nvarchar2(20),
    f2s date,
    f2e date,
    f3s timestamp,
    f3e timestamp,
    primary key(id)
);

注意，f1,f2,f3都是成对出现的，后缀s代表start，e代表end。

【充值】

insert into emp706(id,f1s,f1e,f2s,f2e,f3s,f3e)
values(
    1,
    '20220706 09:01:02',
    '20220706 12:34:56',
    to_date('20220706 09:01:02','yyyymmdd hh24:mi:ss'),
    to_date('20220706 12:34:56','yyyymmdd hh24:mi:ss'),
    to_date('20220706 09:01:02','yyyymmdd hh24:mi:ss'),
    to_date('20220706 12:34:56','yyyymmdd hh24:mi:ss')
);

注意三对起止时间都是一样的。

【窍门】

将三种类型都转成java.util.Date,取起止时间的毫秒数差，再由此得到时分秒时间差。

【代码】

连接DB的类：

package com.hy.lab.timediff;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbUtil {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public static Connection getConn() throws Exception{
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USER, PSWD);
        return conn;
    }
}

测试类：

package com.hy.lab.timediff;

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,to_date(f1s,'yyyymmdd hh24:mi:ss'),to_date(f1e,'yyyymmdd hh24:mi:ss'),f2s,f2e,f3s,f3e from emp706 where id=1";

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

            ResultSet rs=pstmt.executeQuery();

            while(rs.next()){
                int id=rs.getInt(1);

                Date f1s=rs.getDate(2);
                Date f1e=rs.getDate(3);
                System.out.println(String.format("字符串时间差=%s",getDhs(f1s,f1e)));

                Date f2s=rs.getDate(4);
                Date f2e=rs.getDate(5);
                System.out.println(String.format("Date时间差=%s",getDhs(f2s,f2e)));

                Date f3s=rs.getDate(6);
                Date f3e=rs.getDate(7);
                System.out.println(String.format("Timestamp时间差=%s",getDhs(f3s,f3e)));

            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
    }

    /**
     * 取得时分秒
     * @param startDate
     * @param endDate
     * @return
     */
    private static String getDhs(Date startDate,Date endDate){
        return ms2DHS(startDate.getTime(),endDate.getTime());
    }

    /**
     * 毫秒转时分秒
     * @param startMs
     * @param endMs
     * @return
     */
    private static String ms2DHS(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 {
            retval = "0s";
        }

        return retval;
    }
}

【运行结果】

字符串时间差=3h33m54s
Date时间差=3h33m54s
Timestamp时间差=3h33m54s

END