Calculating the difference between two timestamps

Question

I am trying to calculate the difference between to timestamps, but the output only returns the difference in days, but not time:

DECLARE
   a INTERVAL DAY(2) TO SECOND(00);
  TT DATE := TO_DATE('15-Nov-2015 10:00 am', 'dd-mon-yyyy hh:mi am');
  TS DATE := TO_DATE('17-Nov-2015 12:12 am',  'dd-mon-yyyy hh:mi am');

BEGIN


   --Compute interval and assign to an INTERVAL DAY TO SECOND variable

   a := TO_TIMESTAMP(TS,'dd-Mon-yyyy hh:mi am')
        - TO_TIMESTAMP(TT,'dd-Mon-yyyy hh:mi am');
     DBMS_OUTPUT.PUT_LINE(a);
END;

This returns

+02 00:00:00

You have tagged your question MySQL, yet you are using Oracle syntax. What database are you really using? — Gordon Linoff, Sep 22 '15 at 10:47
See http://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds — Paul Maxwell, Sep 22 '15 at 11:01

Santosh Jagtap · Answer 1 · 2015-09-22T10:56:15.673

0

Use the following code

$start_date = new DateTime('2015-11-15 10:00:00');
$since_start = $start_date->diff(new DateTime('2015-11-17 12:12:25'));
echo $since_start->days.' days total<br>';
echo $since_start->y.' years<br>';
echo $since_start->m.' months<br>';
echo $since_start->d.' days<br>';
echo $since_start->h.' hours<br>';
echo $since_start->i.' minutes<br>';
echo $since_start->s.' seconds<br>';

This code will output

The above code will output:

2 days total

0 years

0 months

2 days

2 hours

12 minutes

25 seconds

You can further calculate the time difference in minutes and seconds

edited Sep 22 '15 at 10:56

answered Sep 22 '15 at 10:50

Santosh Jagtap

995
8
17

Santosh Jagtap, what language is that? PHP? I am using Oracle pl/sql – Vincent Shikomba Sep 22 '15 at 10:59

score 0 · Answer 2 · answered Sep 22 '15 at 14:42

DECLARE
   a INTERVAL DAY(2) TO SECOND(0);

BEGIN

   --Compute interval and assign to an INTERVAL DAY TO SECOND variable

   a := TO_TIMESTAMP('17:00',' hh24:mi ')
        - TO_TIMESTAMP('08:00',' hh24:mi ');
     --   - TO_TIMESTAMP('20-SEP-2015 10:59 pm','dd-Mon-yyyy hh:mi am');
     DBMS_OUTPUT.PUT_LINE(SUBSTR(a, 5, 6));
END;

This is the correct solution! Thanks for the attempted help!

Calculating the difference between two timestamps

This returns

2 Answers2