CODEWITHSUNDEEP
sqloracle-databasetype-conversion
Lumpi
Lumpi

Reputation: 89

Converting a number to a date

I have a calculation where I subtract two date values from each other. I receive a decimal value which I multiply with 60 (seconds), 60 (minutes), 24 (hours). This value gives me the amount of seconds a specific ID spends in the system.

But I want to have the result of this calculation again in a date value, so I can work with date values in my design program.

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');

I'm stuck because I can't get things right.

Upvotes: 0

Views: 60

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

It seems very unwieldy, but if your really have to convert to a date datatype then you can add the date difference to another arbitrary date:

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

The fixed date can be anything, so I've just picked the Unix epoch.

Quick demo:

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

Sticking to a raw number or using an interval data type is neater but doesn't seem to fit into your design constraints.

Upvotes: 1

Related Questions