Timestamp difference

[yoganantha321]

I have two timestamp fields JOB_CREATE and  JOB_ONSITETIME

in the script, I need to calculate a new field JOB_RESPONSE  in minutes  calculated using JOB_ONSITETIME - JOB_CREATE

for eg: 12/29/2016 00:12:20 - 12/29/2016 00:12:16   = 0.04 SECONDS   CONVERT TO MINUTE AS 00

I have used the following in script and got the output as:

Num(Timestamp(JOB_ONSITETIME,'MM/DD/YYYY HH:MM:SS')-Timestamp(JOB_CREATETIME,'MM/DD/YYYY HH:MM:SS'))  as JOB_RESPTIME

Output as:

which must be 0.04

I need to develop in the script wise, can anyone help me,

regards,

Yoganantha Prakash G P

[Anil_Babu_Samineni]

May be use this?

Hour(Timestamp(JOB_ONSITETIME,'MM/DD/YYYY HH:MM:SS')-Timestamp(JOB_CREATETIME,'MM/DD/YYYY HH:MM:SS'))

OR

Inteval(Timestamp(JOB_ONSITETIME,'MM/DD/YYYY HH:MM:SS')-Timestamp(JOB_CREATETIME,'MM/DD/YYYY HH:MM:SS'),'HH')

OR

Time(Timestamp(JOB_ONSITETIME,'MM/DD/YYYY HH:MM:SS')-Timestamp(JOB_CREATETIME,'MM/DD/YYYY HH:MM:SS'),'HH')

[Anonymous]

Try This one.

   INTERVAL(JOB_ONSITETIME - JOB_CREATE,'SEC')

[MarcoWedel]

Hi,

maybe like this?

LOAD *,
     Interval(JOB_ONSITETIME-JOB_CREATETIME, 'm.ss') as JOB_RESPTIME
INLINE [
    JOB_CREATETIME, JOB_ONSITETIME
    12/29/2016 00:12:16, 12/29/2016 00:12:20
];

hope this helps

regards

Marco