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!