Hi,
Input is
date :5/25/2018
start_time: 210
end_time:30
I need to calculate duration and answer should be 10 hour and 20 min.
Need help on this
You need to give us units to work with here, a bit more information please.
Thanks for your prompt reply
input is coming as String
I meant what time units are you using for start_time and end_time for this to result in 10 hours and 20 min
Sorry I did not understand your question but I am using 24HR
You have
start_time: 210
end_time:30
What are these? Seconds? Hours? Some other unit? Show us how the calculation using these that results in 10 hours and 20 minutes.
start_time: 210 ==2hours 10 min >> 2:10 AM
end_time:30 ==0hours 30 min >>00:30 AM
other example is
start_time: 1020 ==10hours 20 min >> 10:20 AM
end_time:2020 ==20hours 20 min >>10:20 PM
difference is 10 hours
That's not really a common format, as this represents a combination of two values of different units (some number of hours and some number of minutes) instead of a number of a single unit. It would be better to express this either solely as a number of a single unit (minutes, seconds, or milliseconds, for example) or as a string with a delimiter between units (like "02:10").
In any case you should take a look at our native temporal values which does include ways to calculate duration between given dates / datetimes.
Usually flight record comes in this way and we can get the difference by transforming them in duration() however that will be costly process and I want to avoid that.
I think your examples are off.
210 = 2:10 AM
30 = 12:30 AM
The time difference is 22 hours and 20 minutes, not 10 hours 20 minutes.
Only if the last value was 1230 = 12:30PM would the difference be 10 hours 20 minutes.
You need to parse it somehow, since we can't treat the number as a single time unit (since 0050 + 10 = 0100, so it's not base 10 for the digits representing minutes). You'll need to transform it to a string and split it or substring it to extract the separate values.
If it was a string rather than a number you could parse it with APOC using something like:
return apoc.date.parse("0210", 's', "hhmm") as secondsSinceEpoch
My bad if difference was wrong.
I solved to get the difference of 2 time intervals
:params time1:"2300",time2:"35"
Return Case When duration.between(Localtime(Case size($time1) when 1 then "00:0"+$time1 when 2 then "00:"+$time1 when 3 then "0"+Left($time1,1)+":"+Right($time1,2) Else Left($time1,2)+":"+Right($time1,2)end) , Localtime(Case size($time2) when 1 then "00:0"+$time2 when 2 then "00:"+$time2 when 3 then "0"+Left($time2,1)+":"+Right($time2,2) Else Left($time2,2)+":"+Right($time2,2)end)).seconds <=0 then duration.between(Localtime(Case size($time1) when 1 then "00:0"+$time1 when 2 then "00:"+$time1 when 3 then "0"+Left($time1,1)+":"+Right($time1,2) Else Left($time1,2)+":"+Right($time1,2)end) , Localtime(Case size($time2) when 1 then "00:0"+$time2 when 2 then "00:"+$time2 when 3 then "0"+Left($time2,1)+":"+Right($time2,2) Else Left($time2,2)+":"+Right($time2,2)end)).seconds +86400 Else duration.between(Localtime(Case size($time1) when 1 then "00:0"+$time1 when 2 then "00:"+$time1 when 3 then "0"+Left($time1,1)+":"+Right($time1,2) Else Left($time1,2)+":"+Right($time1,2)end) , Localtime(Case size($time2) when 1 then "00:0"+$time2 when 2 then "00:"+$time2 when 3 then "0"+Left($time2,1)+":"+Right($time2,2) Else Left($time2,2)+":"+Right($time2,2)end)).seconds end as Flight Duration
That is a lot of work for what should be a simple parsing. You may want to explore alternate approaches and more efficient date formats to make parsing and calculation simpler.
I agree with Andy that the time difference between 210 and 30 is 22.2 hrs as you are following military time (24 hr format).
Since this 24 hr format here is a formula to get the correct 22.2 hrs answer:
If end date < start date:
RETURN (24 - 210 * 0.01 + 30 * 0.01) as hrs
Result: 22.2
If end date > start date: (end date - start date)
Implement this formula in your Cypher query to calculate the flight time.
Thanks for your effort. but your logic will not work in general calculation as you are taking difference in decimal number system and my requirement is different