Is there a better way to use apoc.date.format or apoc.date.parse to fix inconsistent date fields?


(Paul Drangeid) #1

I have a data source that has some inconsistent date formatting.

One time it will return it like this:"2016-11-22T21:54:25-05:00" and another it will add another field for the ms:
"2016-11-17T15:26:29.723-05:00", and sometimes the timezone is left off the datefield.

Ultimately I want to convert this to epoch in ms, but if I specify a format that doesn't match the source date string, I get a syntax error, so I wrote 3 lines of string manipulation using replace() to convert... Is there another (better) way to accomplish this? Here's the cypher:

unwind value.value as mwpdevice
WITH *, replace(mwpdevice.TimeCreated,'.'+split(mwpdevice.TimeCreated,'.')[1],'') as mwpdtc2 // This removes the ms field if present
WITH *, replace(mwpdtc2,'-'+split(mwpdevice.TimeCreated,'-')[3],'') as mwpdtc1 // This removes the timezone
WITH *,apoc.date.parse(mwpdtc1,'ms',"yyyy-MM-dd'T'HH:mm:ss") AS mwpdtc // Now convert into ms
return mwpdevice.DeviceGuid,mwpdtc

Thanks for reading!


(Michael Hunger) #2

We could add a parsing function in apoc that takes several formats and tries them in term.
And would only return null if none of them matched.
Can you create an issue there?

Otherwise you could do a

CASE WHEN date CONTAINS "." THEN "format-with-ms" 
           WHEN date ~= "-\d{2}:\d{2}$" THEN "format-with-tz" 
ELSE "regular format" END as format