根据我之前的回答,您可以创建一个名为
sinceHumanReadable
的Oracle函数:
例如,来自
https://momentjs.com/docs/#/plugins/preciserange/的示例产生相同的结果。
moment("2014-01-01 12:00:00").preciseDiff("2015-03-04 16:05:06");
// 1 year 2 months 3 days 4 hours 5 minutes 6 seconds
http://sqlfiddle.com/#!4/d6783/1
create or replace FUNCTION sinceHumanReadable(start_date IN date,end_date IN date)
RETURN VARCHAR2
IS result VARCHAR2(255);
BEGIN
with
dates_parts as (
SELECT
EXTRACT(YEAR FROM (end_date - start_date) YEAR TO MONTH ) as Year,
EXTRACT(MONTH FROM (end_date - start_date) YEAR TO MONTH ) as Month,
EXTRACT(HOUR FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Hour,
EXTRACT(MINUTE FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Minute,
EXTRACT(SECOND FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Second
FROM dual
),
dates_parts_with_day as (
select
Year,Month,Hour,Minute,Second,
EXTRACT(DAY FROM (end_date - ADD_MONTHS(start_date,Month+Year*12)) DAY TO SECOND ) as Day
from dates_parts
)
select
decode(dates_parts_with_day.Year, 0,'', dates_parts_with_day.Year || ' years ' )||
decode(dates_parts_with_day.Month,0,'', dates_parts_with_day.Month || ' months ')||
decode(dates_parts_with_day.Day,0,'', dates_parts_with_day.Day || ' days ')||
decode(dates_parts_with_day.Hour,0,'', dates_parts_with_day.Hour || ' hours ')||
decode(dates_parts_with_day.Minute,0,'', dates_parts_with_day.Minute || ' minutes ')||
dates_parts_with_day.Second || ' seconds'
into result
from
dates_parts_with_day;
RETURN(result);
END sinceHumanReadable;
GO
查询
with dates as (
select sysdate-99.90032407407407407407407407407407407407 as d1,sysdate as d2 from dual
union all
select to_date('2016-03-30 17:14:53','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2016-07-08 14:51:21','yyyy-mm-dd hh24:mi:ss') as d2 from dual
union all
select to_date('2014-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2015-03-04 16:05:06','yyyy-mm-dd hh24:mi:ss') as d2 from dual
union all
select sysdate as d1,add_months(sysdate,35) as d2 from dual
union all
select sysdate as d1,sysdate as d2 from dual
)
select
d1,d2,
sinceHumanReadable(d1,d2) as since
from
dates;
将会产生以下内容:
| D1 | D2 | SINCE |
|----------------------|----------------------|-----------------------------------------------------|
| 2017-07-19T17:50:00Z | 2017-10-27T15:26:28Z | 3 months 7 days 21 hours 36 minutes 28 seconds |
| 2016-03-30T17:14:53Z | 2016-07-08T14:51:21Z | 3 months 7 days 21 hours 36 minutes 28 seconds |
| 2014-01-01T12:00:00Z | 2015-03-04T16:05:06Z | 1 years 2 months 3 days 4 hours 5 minutes 6 seconds |
| 2017-10-27T15:26:28Z | 2020-09-27T15:26:28Z | 2 years 11 months 0 seconds |
| 2017-10-27T15:26:28Z | 2017-10-27T15:26:28Z | 0 seconds |
moment.js
的示例添加了测试,我的代码产生了相同的结果。 - Indent