# Thread: calculating the difference between days along with timestamp

1. Registered User
Join Date
Mar 2012
Posts
6

## Unanswered: calculating the difference between days along with timestamp

Hi All,

I need help in calculating the difference between days along with timestamp.
i.e, for eg I have
x= 22:20:15:10(Days:Hours:Mins : Secs)
and
y= 34:10:58:10(Days:Hours:Mins : Secs)

Now I need to find the difference between y and x, i.e. (y-x)
Please do suggest me how to acquire the result.

Thanks & Regards,
Kashyap

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Code:
```SQL> create table test
2    (x date,
3     y date
4    );

Table created.

SQL> insert into test (x, y)
2    value
3
SQL> insert into test (x, y) values (to_date('01.03.2012 14:30:15', 'dd.mm.yyyy hh24:mi:ss'),
2                                  to_date('10.03.2012 23:45:50', 'dd.mm.yyyy hh24:mi:ss'));

1 row created.

SQL> select * from test;

X                   Y
------------------- -------------------
01.03.2012 14:30:15 10.03.2012 23:45:50

SQL> select y - x result
2  from test;

RESULT
----------
9,38582176

SQL>```
The result is number of DAYS. It means that you'll need to use some arithmetics to convert it to another format (you know ... a day has 24 hours, an hour has 60 minutes, and so forth).

3. Registered User
Join Date
Oct 2002
Location
Cape Town, South Africa
Posts
253
I hope you are using the formal interval datatype for this: INTERVAL DAY TO SECOND datatype

Anyway, an alternative to @LittleFoot's example which would not require you to convert your interval to a date:
Code:
```dayneo@SANDBOX> declare
2
3  	     l_x interval day to second (0);
4  	     l_y interval day to second (0);
5  	     l_z interval day to second (0);
6
7  begin
8
9  	     l_x := INTERVAL '+22 20:15:10' day to second;
10  	     l_y := INTERVAL '+34 10:58:10' day to second;
11  	     l_z := l_y - l_x;
12
13  	     dbms_output.put_line(l_z);
14
15  end;
16  /
+11 14:43:00

PL/SQL procedure successfully completed.

dayneo@SANDBOX>```
Note that the output is returned as days hours minutes seconds automatically... no additional calculations required. You can also now use this for any other date based arithmetic.

It is worth noting that Oracles interval format is different from the one you are using. I would recommend you change your format to comply with Oracles. It will just make such things that much easier for you.

Read this: Not quite your case, but relevant to storing timestamps/intervals as strings

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•