Date Substraction

[microwin88x]

Hello,

I have the following table:

ID_TICKET	CREATE_DATE	CREATE_TIME	CLOSE_DATE	CLOSE_TIME
ID1	17/05/2016	17:52:55	04/06/2016	02:00:12

I need to get a Field named DIFFERENCE with the following value: DD HH:MM:SS

In this case, the value would be: 18 08:07:17

Thanks!!!

[sunny_talwar · Accepted solution]

May be try this:

Interval(CLOSE_DATE + CLOSE_TIME - CREATE_DATE - CREATE_TIME, 'D hh:mm:ss') as DIFFERENCE

[sunny_talwar]

Not sure if you need a 1 in there:

Table:

LOAD *,

  Interval(CLOSE_DATE + CLOSE_TIME - CREATE_DATE - CREATE_TIME + 1, 'D hh:mm:ss') as DIFFERENCE;

LOAD * INLINE [

    ID_TICKET, CREATE_DATE, CREATE_TIME, CLOSE_DATE, CLOSE_TIME

    ID1, 17/05/2016, 17:52:55, 04/06/2016, 02:00:12

];