Filtering using where statement in datetime format

[DMF194]

Hi everyone,

I tried to use the cypher below to convert datetime string into datetime format, which resulted only 1 batch failed but the rest completed successfully.

call apoc.periodic.iterate(
"match(p:POSITION) return p",
"set p.devicetime=datetime({epochMillis:apoc.datetime(p.devicetime, 'ms', 'yyyy-MM-dd HH:MM:ss") })"
{batchSize:100000,parallel:true});

The string that I wish to convert is in this format "2021-09-08 15:34:59"

Tried the following cypher to filter nodes based on datetime

match (p:POSITION)
where datetime(n:devicetime)>'2021-09-07 15:04:03' or
datetime(n:devicetime)<'2021-09-07 15:04:03'
return n limit 10

The following error was shown below

After much investigation, the cypher below is used

match (p:POSITION)
return apoc.meta .type(n.devicetim) order by n.device limit 10

Any suggestion will be welcomed

[giuseppe_villan · Accepted solution]

DMF194:

datetime format

Maybe some n.devicetime are not strings?
You can try executing match (p:POSITION) return distinct apoc.meta.type(p.devicetime) to retrieve all distinct types.

Anyway, can you try with this query (in this way I filter only position with apoc.meta.type = 'string'😞

CALL apoc.periodic.iterate(
"match(p:POSITION) where apoc.meta.type(p.devicetime) = 'STRING' return p",
"with p set p.devicetime = datetime({epochmillis: apoc.date.parse(p.devicetime, 'ms', 'yyyy-MM-dd HH:MM:ss')})",
{batchSize:100000,parallel:true});

[DMF194]

Hi @giuseppe.villani,

Appreciate the advice, now all my Position nodes are updated with the correct datetime format.

It turns out that after running below cypher:

match (p:POSITION) return distinct apoc.meta.type(p.devicetime)

It showed STRING and ZoneDateTime.