# Conversion DateTime

**URL:** <https://community.neo4j.com/t/conversion-datetime/7172>\
**Category:** Cypher\
**Tags:** cypher\
**Created:** [May 6, 2019, 6:14pm UTC](https://community.neo4j.com/t/conversion-datetime/7172 "2019-05-06T18:14:56Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 6, 2019, 6:14pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/1 "2019-05-06T18:14:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 6, 2019, 7:41pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/2 "2019-05-06T19:41:06Z")

</div>

You need to give us units to work with here, a bit more information please.

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 6, 2019, 8:08pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/3 "2019-05-06T20:08:06Z")

</div>

Thanks for your prompt reply  
input is coming as String

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 6, 2019, 10:26pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/4 "2019-05-06T22:26:58Z")

</div>

I meant what time units are you using for start\_time and end\_time for this to result in 10 hours and 20 min

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 7, 2019, 1:41am UTC](https://community.neo4j.com/t/conversion-datetime/7172/5 "2019-05-07T01:41:50Z")

</div>

Sorry I did not understand your question but I am using 24HR

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 7, 2019, 8:15am UTC](https://community.neo4j.com/t/conversion-datetime/7172/6 "2019-05-07T08:15:08Z")

</div>

You have

```auto
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.

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 7, 2019, 8:26am UTC](https://community.neo4j.com/t/conversion-datetime/7172/7 "2019-05-07T08:26:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 7, 2019, 10:29am UTC](https://community.neo4j.com/t/conversion-datetime/7172/8 "2019-05-07T10:29:15Z")

</div>

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](https://neo4j.com/docs/cypher-manual/current/syntax/temporal/#cypher-temporal-specifying-durations) which does include ways to calculate duration between given dates / datetimes.

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 7, 2019, 10:39am UTC](https://community.neo4j.com/t/conversion-datetime/7172/9 "2019-05-07T10:39:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 7, 2019, 11:11am UTC](https://community.neo4j.com/t/conversion-datetime/7172/10 "2019-05-07T11:11:29Z")

</div>

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:

```auto
return apoc.date.parse("0210", 's', "hhmm") as secondsSinceEpoch

```

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 8, 2019, 10:45am UTC](https://community.neo4j.com/t/conversion-datetime/7172/11 "2019-05-08T10:45:39Z")

</div>

> [@andrew\_bowman](#):
>
> return apoc.date.parse("0210", 's', "hhmm")

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`

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 9, 2019, 11:31pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/12 "2019-05-09T23:31:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [May 14, 2019, 10:32pm UTC](https://community.neo4j.com/t/conversion-datetime/7172/13 "2019-05-14T22:32:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![intouch\_vivek](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/intouch_vivek/32/4097_2.png) [@intouch\_vivek](https://community.neo4j.com/u/intouch_vivek)\
**Post date:** [May 16, 2019, 7:46am UTC](https://community.neo4j.com/t/conversion-datetime/7172/14 "2019-05-16T07:46:47Z")

</div>

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