java时间戳 时分秒_orale获取两个时间戳字符串之间的时间差（时分秒）（含java实现）...

@

需求描述

两个时间戳字符串，如2018-11-23 08:59:07 和 2018-11-20 07:58:06，求他们之间的时间差，返回结果为73:01:01(75小时1分1秒)

sql方案

方案一(时分秒)

SELECT

FLOOR(hours) AS 小时,

FLOOR((secs - ( FLOOR(hours)) * 3600 ) / 60) AS 分钟,

secs - (FLOOR(hours)) * 3600 - (FLOOR((secs - ( FLOOR(hours)) * 3600 ) / 60)) * 60 AS 秒

from

(SELECT

secs/60/60 AS hours,

secs/60 AS mins,

secs AS secs

FROM

(select (To_date('2018-11-23 08:59:07' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2018-11-20 07:58:06' , 'yyyy-mm-dd hh24-mi-ss')) * (24 * 60 * 60 ) AS secs FROM DUAL) tt) temp;

2. sql 方案二(天，时，分，秒)

SELECT

str AS 总秒数,

FLOOR((str / 86400 ))AS 天,

floor(MOD(str,86400)/ 3600) AS 小时,

FLOOR(mod(MOD(str,86400) , 3600)/60) AS 分钟,

mod(mod(MOD(str,86400),3600),60) AS 秒

FROM

(select (To_date('2018-11-23 08:59:07' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2018-11-20 07:58:06' , 'yyyy-mm-dd hh24-mi-ss')) * (24 * 60 * 60 ) AS str FROM DUAL) tt;

86400为24 * 60 * 60

java方案

/** V1.0 Create By dongxiaoyong on 2018/11/23

* description求两个时间戳的时间差，转换为时分秒(天，hh:mm:ss,多少天几小时几分钟几秒)

* @param args

* @return void

*/

public static void main(String[] args){

int str = 25209;//两个时间戳相差的秒数

System.out.println(str / (24 * 60 * 60) + "天");

System.out.println(str % (24 * 60 * 60 ) / (60 * 60) + "小时");

System.out.println(str % (24 * 60 * 60 ) % (60 * 60 ) /60 + "分钟");

System.out.println(str % (24 * 60 * 60) % (60 *60 ) % 60 +"秒") ;

}