need help

[mshailaja]

HI ,

i have opendate of the incident. i want to see the time differnce of that incident

The calculation which user has given is currrentdate-opendate<60 minutes. How to implement in  script level( i need the output as in minutes)

i have tried as interval(Timestamp(now)-Tiestamp(Opendate))<60.  i am not getting correct output.please help me

[sasikanth]

Try this in Script:

LOAD

Opendate,

ceil(Timestamp(Now())- Timestamp(opendate))*24*60  AS Minutes,

From Tabale..

[mshailaja]

Open date as

1/1/2016 12:00:05 AM

1/1/2016 12:00:25 AM

1/1/2016 12:00:15 AM

1/1/2016 12:00:50 AM

1/1/2016 12:00:35 AM

1/1/2016 12:00:45 AM

1/1/2016 12:00:12 AM

Here i  have calculations as (Current date- Opendate)<60 mins

so i have written in script as

Timestamp(Timestamp(now())- (INOPEN_DATE)<60,'HH;MM') as TimeTaken,
Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'hh:mm:ss TT') as time

i have written like above but the output is coming wrong

Please help me

[MayilVahanan]

Hi

Please Post some sample data and expected output to fix the issue.

[mshailaja]

I WANT TO SHOW THE CALCULATION AS (cURRENT DATE- OPENDATE)<60 MINUTES AND THE OUTPUT TO BE SHOWN IN MINUTES

[MayilVahanan]

Hi

Try like this

If(Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm') < 60,

Interval(Timestamp#(now(),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm')) As TimeDifference

[mshailaja]

Hi one morething , here the opendate is in GMT timings

andin the calculations i am using Now function.

the server is located in US.

so if i use now function the calculation will  go wrong.

So i need to convert the Current time as GMT timings (i cannot use Now function as it is in US server)

Can any one help  me how to write the calculation (currentdate(which i have to convert into GMT)- Opendate)<60 mins

[sasiparupudi1]

Try this

Load

Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') as USNeWYorkTime,

Timestamp(Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as INOPEN_DATE,

Interval(Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss')- Timestamp(Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss'),'mm') as TimeTaken// Diff in minutes

;

Load * Inline

[

INOPEN_DATE

1/1/2016 12:00:05 AM

1/1/2016 12:00:25 AM

1/1/2016 12:00:15 AM

1/1/2016 12:00:50 AM

1/1/2016 12:00:35 AM

1/1/2016 12:00:45 AM

1/1/2016 12:00:12 AM

];

hth

Sasi

[annafuksa1]

try

if(num(Timestamp#([Open date],'D/M/YYYY hh:mm:ss[.fff]'))- num('$(vNow)') <0.00070601851621177 ,1,0) as [60minFlagnum] ,

where vNow= now()

[MayilVahanan]

If(Interval(Timestamp(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm') < 60,

Interval(Timestamp#(ConvertToLocalTime(UTC(), 'GMT-05:00'),'DD/MM/YYYY hh:mm:ss') - Timestamp#(INOPEN_DATE,'DD/MM/YYYY hh:mm:ss TT'), 'mm')) As TimeDifference