Timestamp Calculation Help

[abhaysingh]

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

[maxgro · Accepted solution]

interval(

Date#(Date2, 'DD/MM/YY hh:mm:ss')

- Date#(Date1, 'DD/MM/YY hh:mm:ss')

, 'mm')

[Kushal_Chawda]

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 ];   

[abhaysingh]

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

[maxgro]

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'

)))

[jonathandienst]

You were on the right track, bit keep the date portion as well

(Date2 - Date1) * 1440 for Minutes