Skip to Main Content
  • Questions
  • Time difference between two date/times

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Lisa.

Asked: April 19, 2017 - 12:31 pm UTC

Last updated: April 28, 2017 - 10:55 pm UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hey Guys

Hoping you will be able to help with an issue I am having.

I currently have information stored in a table as StartDate - stored as date (eg 18-APR-17) and StartTime - stored as a number(4,2) so 10.30am would be stored as 10.3 or 3.45pm would be stored as 15.45

I would like to be able to combine the date and number value together to represent a date/time and subtract this from the current date/time and show the difference in hours and minutes.

For Example say the current date/time was 19th April 2017 13.25pm and the startdate/time was 18th April 2017 21.45pm then the difference between these two values would be 15 hours 40 mins written as 15.40

Any help greatly appreciated

Thanks

Lisa

and Connor said...

I'd convert them to dates, and then use normal date arithmetic, eg

SQL> create table t(
  2    s date,
  3    st number(4,2),
  4    e date,
  5    et number(4,2)
  6    );

Table created.

SQL>
SQL> insert into t values (sysdate,10.31,sysdate+3,17.47);

1 row created.

SQL>
SQL> select * from t;

S                 ST E                 ET
--------- ---------- --------- ----------
27-APR-17      10.31 30-APR-17      17.47

1 row selected.

SQL>
SQL> with t1 as
  2  (
  3  select
  4    to_date(to_char(s,'yyyymmdd')||to_char(st,'fm00.00'),'yyyymmddhh24.mi') real_start,
  5    to_date(to_char(e,'yyyymmdd')||to_char(et,'fm00.00'),'yyyymmddhh24.mi') real_end
  6  from t
  7  )
  8  select
  9    real_start,
 10    real_end,
 11    real_end - real_start,
 12    trunc(real_end - real_start) days,
 13    trunc(24*mod(real_end - real_start,1)) as hrs,
 14    trunc( mod(mod(real_end - real_start,1)*24,1)*60 ) as mins
 15  from t1;

REAL_STAR REAL_END  REAL_END-REAL_START       DAYS        HRS       MINS
--------- --------- ------------------- ---------- ---------- ----------
27-APR-17 30-APR-17          3.30277778          3          7         16

1 row selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exactly the answer I needed

Lisa, April 28, 2017 - 2:05 pm UTC

Hi Connor

That's exactly what I was looking for - thank you so much for your help - really appreciate it

Thanks

Lisa
Connor McDonald
April 28, 2017 - 10:55 pm UTC

glad we could help