Oracle 函数或者 SQL查询

Oracle 中存有三条数据 分别如下：

我需要根据如下时间区间和地址匹配记录
起算点：2021-01-22 12:00:00
结算点：2021-01-27 23:45:00
地址：24
想要得到的结果是

我这里的用时 是换算成了分钟

如果起算点 >=开始时间 用于计算用时的起算点 = 起算点
如果起算点 < 开始时间 用于计算用时的起算点 = 开始时间

如果结算点 <= 结束时间 用于计算用时的结算点= 结算点
如果结算点 > 结束时间 用于计算用时的结算点= 结束时间

这个需求如何用 SQL查询或者函数实现。

1. 计算分钟函数

round(to_number(end-date-start_date)*1440)

2. 计算一条记录时间

round(
    to_number(
        to_date(min(结束时间, '2021-01-27 23:45:00'), 'yyyy-mm-dd hh24:mi:ss') -
        to_date(max(开始时间, '2021-01-22 12:00:00'), 'yyyy-mm-dd hh24:mi:ss')
    ) * 1440
)

3. 汇总

select '24' as '地址', '2021-01-22 12:00:00' as '开始时间', '2021-01-27 23:45:00' as '结束时间',
sum(round(
    to_number(
        to_date(min(结束时间, '2021-01-27 23:45:00'), 'yyyy-mm-dd hh24:mi:ss') -
        to_date(max(开始时间, '2021-01-22 12:00:00'), 'yyyy-mm-dd hh24:mi:ss')
    ) * 1440
)) from table where 地址 = 24;

--创建测试数据
create table test_a (addr number,start_time date,end_time date);

insert into test_a (ADDR, START_TIME, END_TIME)
values (24, to_date('24-01-2021 05:40:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('24-01-2021 17:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_a (ADDR, START_TIME, END_TIME)
values (24, to_date('22-01-2021 06:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('22-01-2021 18:20:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_a (ADDR, START_TIME, END_TIME)
values (24, to_date('25-01-2021 07:20:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-01-2021 20:00:00', 'dd-mm-yyyy hh24:mi:ss'));
commit;

--执行查询
with t as
 (select 24 addr,
         to_date('2021-01-22 12:00:00', 'yyyy-mm-dd hh24:mi:ss') st,
         to_date('2021-01-27 23:45:00', 'yyyy-mm-dd hh24:mi:ss') et
    from dual)
    ,m as (
select a.addr 地址,
       min(case
             when t.st >= a.start_time then
              t.st
             else
              a.start_time
           end) 开始时间,
       max(case
             when t.et <= a.end_time then
              t.et
             else
              a.end_time
           end) 结束时间
  from test_a a, t
 where a.addr = t.addr
 group by a.addr)
 select m.*,(结束时间-开始时间)*24*60 用时 from m;

输出结果

甲乙兮 2021-12-08 18:02
这个很简单呀，你说的这个
起算点：2021-01-22 12:00:00结算点：2021-01-27 23:45:00 你放到where条件中，
比如 select *from 表 where t.开始时间>=起算点 and t.结束时间<=结算点；这样你就查出来你说的这个时间段的数据了，然后用时多久，你搞个转换减一下就OK了

为了方便，我们将起算点和结算点加入表中直接进行记录比较
create table a as
select 主键，地址，开始时间，结束时间 from tablename
union all
select 124,24,to_date(2021-01-22 12:00:00,yyyy-mm-dd 24hh:mm:ss) from dual;
直接求出对应值即可：
select 地址，min(开始时间),max(结束时间),(max(结束时间)-min(开始时间))2460 from a group by 地址

这个很简单呀，你说的这个
起算点：2021-01-22 12:00:00结算点：2021-01-27 23:45:00 你放到where条件中，
比如 select *from 表 where t.开始时间>=起算点 and t.结束时间<=结算点；这样你就查出来你说的这个时间段的数据了，然后用时多久，你搞个转换减一下就OK了

SELECT * FROM 你的表名 T WHERE T."开始时间" > '2021-01-22 12:00:00' AND T."结束时间"<'2021-01-27 23:45:00';