将数字转换为日期

debugcn 发表于 Dev

隆比

我有一个计算，其中我彼此减去两个日期值。我收到一个十进制值，将其乘以60（秒），60（分钟），24（小时）。这个值让我知道特定ID在系统中花费的秒数。

但是我想再次将计算结果包含在日期值中，因此我可以在设计程序中使用日期值。

select to_date((t2.time_event - t1.time_event) * 24 * 60 * 60,'hh24:mm:ss') as "Throughput_Time", count(t1.) as "Payments"
    from TBL_DUMMYFEED t1 
    join TBL_DUMMYFEED t2 on t1.trax_id = t2.trax_id
    where t1.event = 'created' and t2.event = 'sent'
    group by to_date((t2.time_event - t1.time_event) * 24 * 60 * 60,'hh24:mm:ss')
    order by to_date((t2.time_event - t1.time_event) * 24 * 60 * 60,'hh24:mm:ss');

我陷入困境是因为我做对了事情。

亚历克斯·普尔

看起来非常笨拙，但是如果您真的必须转换为日期数据类型，则可以将日期差添加到另一个任意日期：

select date '1970-01-01' + (t2.time_event - t1.time_event) as ...

固定日期可以是任何日期，因此我选择了Unix时代。

快速演示：

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

with TBL_DUMMYFEED as (
  select 1 as trax_id, 'created' as event,
    to_date('2015-02-25 06:49:15', 'YYYY-MM-DD HH24:MI:SS') as time_event
  from dual
  union all
    select 1 as trax_id, 'sent' as event,
    to_date('2015-02-25 08:13:47', 'YYYY-MM-DD HH24:MI:SS') as time_event
  from dual
)
select t2.time_event - t1.time_event as raw_diff,
   (t2.time_event - t1.time_event) * 24 * 60 * 60 as diff_in_seconds,
   numtodsinterval(t2.time_event - t1.time_event, 'DAY') as diff_interval,
   date '1970-01-01' + (t2.time_event - t1.time_event) as fake_date
from TBL_DUMMYFEED t1 
join TBL_DUMMYFEED t2 on t1.trax_id = t2.trax_id
where t1.event = 'created'
and t2.event = 'sent';

   RAW_DIFF DIFF_IN_SECONDS DIFF_INTERVAL FAKE_DATE          
----------- --------------- ------------- -------------------
.0587037037            5072 0 1:24:32.0   1970-01-01 01:24:32

保持原始数字或使用间隔数据类型比较整洁，但似乎不适合您的设计约束。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。