topic Re: Diff between two timestamps expressed as an SLA in App Development

Diff between two timestamps expressed as an SLA

QFanatic — Mon, 25 Oct 2021 13:39:49 GMT

Hi,

I have 2 timestamps ..a CloseTime...2021/08/25 11:35:24, and an OpenTime...2021/08/20 10:07:47.

I subtract these in script and I get a time difference of 51:04:44 which is correct by using interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE,'hh') .

now I want to compare that difference to 24 hours - if it is more I want to flag SLA_Breached as 'Yes'.

I have tried every variation I can think of, but to no luck...

1. if(Timestamp#(DTTM_REQUEST_CLOSE) - Timestamp#(DTTM_ASSIGNED_DATE) > maketime(24), 'Y','N') as SLA_BREACHED

2. if(hour(interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE)) > 24, 'Y','N')

None of them work. Any idea how I can do this comparison?

Thanks.

Re: Diff between two timestamps expressed as an SLA

ManuelRühl — Mon, 25 Oct 2021 14:45:53 GMT

24h are one day. So you could just check, wether the difference is >1 :

if(Timestamp#(DTTM_REQUEST_CLOSE) - Timestamp#(DTTM_ASSIGNED_DATE) > 1, 'Y','N') as SLA_BREACHED

Re: Diff between two timestamps expressed as an SLA

micheledenardi — Mon, 25 Oct 2021 14:32:23 GMT

Try this:

Load *, if(SubField(interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE),':',1)>24,'Y','N') as SLA_BREACHED; load Timestamp(Timestamp#(DTTM_ASSIGNED_DATE,'YYYY/MM/DD hh:mm:ss')) as DTTM_ASSIGNED_DATE, Timestamp(Timestamp#(DTTM_REQUEST_CLOSE,'YYYY/MM/DD hh:mm:ss')) as DTTM_REQUEST_CLOSE inline [ DTTM_ASSIGNED_DATE,DTTM_REQUEST_CLOSE 2021/08/20 10:07:47,2021/08/25 11:35:24 ];

Re: Diff between two timestamps expressed as an SLA

QFanatic — Tue, 26 Oct 2021 04:38:54 GMT

Thank you!