Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have One scenario in Which i have to subtract two datestamp e.g.
Date1 Date2 Date2-Date1
12/10/15 11:10:00 12/10/15 11:15:00 Output Should be 15 Minutes
12/10/15 11:10:00 13/10/15 10:10:00 Output Should be 1380 Minutes
I m Using (Frac(Date2)-Frac(Date1)) * 1440 for Minutes
and getting Correct output for case 1, but getting -ve output in case 2
Please Suggest..
thanks
Abhay
interval(
Date#(Date2, 'DD/MM/YY hh:mm:ss')
- Date#(Date1, 'DD/MM/YY hh:mm:ss')
, 'mm')
interval(
Date#(Date2, 'DD/MM/YY hh:mm:ss')
- Date#(Date1, 'DD/MM/YY hh:mm:ss')
, 'mm')
try this
LOAD *,
Interval(Time2-Time1,'m')&' Minutes' as Minutes;
LOAD *, timestamp(Timestamp#(Date1,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Time1,
timestamp(Timestamp#(Date2,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Time2 Inline [
Date1 , Date2
12/10/15 11:10:00, 12/10/15 11:15:00
12/10/15 11:10:00, 13/10/15 10:10:00 ];
Hi Maxgro,
I got i output but i need one more calculation in this. i.e
let say the field name is Resolution time
if( Resolution time >= 0 and Resolution time<= 30 ,' under 30 Min',
if( Resolution time >= 30 and Resolution time<= 60 ,' 1 hour')) and so on
I m trying the same mentioned way but not working..
thanks
Abhay
you can add as many if you want
if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 30/(24*60), '<30',
if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 60/(24*60), '<60',
if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 120/(24*60), '<120',
'other'
)))
You were on the right track, bit keep the date portion as well
(Date2 - Date1) * 1440 for Minutes