converting a value to datetime in load csv in cypher

Question

I'm trying to load csv file into neo4j using this query:

:auto

USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///test.csv' AS row
MERGE (news:newsID {newsID: row.id})
  ON CREATE SET 
  news.title = row.title, 
  news.creation_date = row.creation_date,
  news.content = row.content;

I want to convert creation_date to datetime
I can do that using something like:

WITH apoc.date.parse("2019-3-8 12:10:11", "ms", "yyyy-MM-dd HH:mm:ss") AS ms
RETURN datetime({epochmillis: ms}) as cdate

But I can not use it in the LOAD CSV set part.
How can I convert a field using apoc.date.parse and datetime in LOAD CSV ?

Answer 1

Neo4j can automatically parse a wide variety of string formats to temporal instants (like datetime). You just have to use a supported string format.

Although your example string ("2019-3-8 12:10:11") is not supported, replacing the space character between the date and time with a "T" would work.

For example, try this:

RETURN datetime("2019-3-8T12:10:11");

So, provided your creation_date property values had a supported format, this would work:

:auto
USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///test.csv' AS row
MERGE (news:newsID {newsID: row.id})
  ON CREATE SET 
  news.title = row.title, 
  news.creation_date = datetime(row.creation_date),
  news.content = row.content;

Or, if you want to keep your existing CSV file, and assuming its creation_date strings only have a single space character, you can do this:

:auto
USING PERIODIC COMMIT 
LOAD CSV WITH HEADERS FROM 'file:///test.csv' AS row
MERGE (news:newsID {newsID: row.id})
  ON CREATE SET 
  news.title = row.title, 
  news.creation_date = datetime(REPLACE(row.creation_date, ' ', 'T')),
  news.content = row.content;