How to Add 2 numbers together in Cypher

[BuilderGuy1]

I'm trying to make 12 hour time to be in a 24 hour format.
The problem I'm having is that I don't know how to ADD two numbers together in Cypher.
I end up concatenating them together.

I'm using this line in a CASE statement:
WHEN 'PM' THEN toInteger(dt4[0]) + 12
This results in the 8 and 12 being concatinated:
"2019-09-29T812:51"

I have been scouring the Cypher manual but I can't find a way to do this.
Any thoughts?

J

[Btibert3]

I suspect it comes down to how you may be storing the property on your node. But at the simplest level, we can do math with cypher. For example:

RETURN 2 + 2

or

RETURN 4 % 3

Extending this (and knowing that date/times are just numbers under the hood), I suspect you may want to explore leveraging date/time functionality with the database.

http://neo4j-contrib.github.io/neo4j-apoc-procedures/3.5/utilities/datetime-conversions/

Without seeing data, I suspect it is a matter of data formats and some APOC goodness.

Hope that helps.

[BuilderGuy1]

I have tried this line. Which works...sort of.
WHEN 'PM' THEN toInteger(dt4[0]) + toInteger(12)

Adding "12" gives me the error:
"Invalid value for HourOfDay (valid values 0 - 23): 24"

I don't know how it adds up to 24. It's adding 8 to 12.
If I change 12 to 11 and add it again I get this:
"2019-09-29T19:51:00Z"

Thoughts?

I'm converting a date/time string that apoc does not appear to handle.
I have everything working except this one thing.

[Btibert3]

Here is a different way to think about it. While below doesn't solve your problem explicitly, perhaps it will help in terms of processing the data in whatever format it resides, and how you might be able to parse it into a format that you can manipulate as a date/time.

WITH apoc.date.parse('2019-09-29T19:51:00Z', 'ms', "yyyy-MM-dd'T'HH:mm:ss'Z'") as date
WITH apoc.date.format(date, 'ms', 'yyyy/MM/dd HH:mm:ss') as date
return date

If above doesn't help directionally, might it be possible to provide a sample of the data you are looking to convert?

[BuilderGuy1]

This is an example of the string that I have to covert to a DateTime:

"29 September 2019, 8:50 PM"

Below is the code that I have written to do that conversion:

LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line
WITH line, SPLIT(line.Time, ',') AS dt1
WITH dt1, SPLIT(dt1[0], ' ') AS dt2, SPLIT (trim(dt1[1]), ' ') AS dt3
WITH dt2, dt3, SPLIT(dt3[0], ':') AS dt4
WITH dt2, dt3, dt4, toInteger(dt4[0]) AS myhour
//
RETURN datetime(dt2[2] + '-' + 
CASE dt2[1]
	WHEN 'January' THEN '01'
	WHEN 'February' THEN '02'
	WHEN 'March' THEN '03'
	WHEN 'April' THEN '04'
	WHEN 'May' THEN '05'
	WHEN 'June' THEN '06'
	WHEN 'July' THEN '07'
	WHEN 'August' THEN '08'
	WHEN 'September' THEN '09'
	WHEN 'October' THEN '10'
	WHEN 'November' THEN '11'
	ELSE '12' END
+ '-' + dt2[0] + 'T' + 
CASE dt3[1]
	WHEN 'AM' AND myhour = 12 THEN toInteger(0)
	WHEN 'PM' AND myhour < 12 THEN myhour + toInteger(12)
	ELSE myhour
	END
+ ':' + dt4[1]) AS MyDateTime

The two WHEN lines in the last CASE statement do not work.
I'm trying to convert the 12 hour time to 24 hour time based on the AM or PM value.
I'm not sure how to have multiple conditions in a WHEN / CASE statement.

J

[andrew_bowman · Accepted solution]

I think I see the problem.

In your first CASE you use: CASE dt2[1], and that works because each of your WHENs only has the value that you are comparing against dt2[1].

In your second CASE you use: CASE dt3[1], but your WHENs consist of multipart boolean expressions. You aren't just comparing the value, you have something more complex there.

Since you're not doing a simple comparison of the original expression, you need to include it into your full boolean expression:

CASE 
	WHEN dt3[1] = 'AM' AND myhour = 12 THEN toInteger(0)
	WHEN dt3[1] = 'PM' AND myhour < 12 THEN myhour + toInteger(12)
	ELSE myhour
	END

[BuilderGuy1]

I have tried this as well.
There are no errors and the script does run...
BUT
None of the code after the THENs run.

For example:
An input of "29 September 2019, 8:50 PM" results in:
"2019-09-29T08:50:00Z"

This input has PM and myhour is Less Than 12 so the hour should be calculated to be 20 but it remains 8.

Either the conditions are not being seen as true or the code at the end is not doing what I think it should.

J

[BuilderGuy1]

EUREKA !!
I still had "dt3[1]" beside CASE. Like this: "CASE dt3[1]"
Once I removed the variable from that line it all started working !!

It would be a great addition to the Cypher manual to have an example of CASE with multiple conditions.
It appears that having the variable beside the CASE statement, essentially nullified the multiple conditions and thus the resulting code would never get executed.

Thanks everyone !

J

[andrew_bowman]

Glad you got it working!

We do have documentation for this, check it out here:
https://neo4j.com/docs/cypher-manual/current/syntax/expressions/#syntax-generic-case

[BuilderGuy1]

Yes, I did read that.

What I didn't get from it though was that I could use multiple conditionals on the same WITH line. I think it is clear though, that multiple WITH statements can be used (section 2.3.3.2).

I think it would be even clearer if the docs had an example that uses multiple conditionals in one WITH line as well as multiple WITH lines.
Just my 2 cents

Thanks again!
J