# Filtering using where statement in datetime format

**URL:** <https://community.neo4j.com/t/filtering-using-where-statement-in-datetime-format/50408>\
**Category:** Procedures & APOC\
**Tags:** cypher\
**Created:** [January 13, 2022, 4:22am UTC](https://community.neo4j.com/t/filtering-using-where-statement-in-datetime-format/50408 "2022-01-13T04:22:43Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![DMF194](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dmf194/32/20924_2.png) [@DMF194](https://community.neo4j.com/u/DMF194)\
**Post date:** [January 13, 2022, 4:22am UTC](https://community.neo4j.com/t/filtering-using-where-statement-in-datetime-format/50408/1 "2022-01-13T04:22:43Z")

</div>

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.

```auto
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

```auto
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

 ![image](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/3X/e/5/e58507654a2b515095fd9c0e0a0e50242af857d6.png)

After much investigation, the cypher below is used

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

```

![image](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/3X/6/b/6b14f16d78feffcea077dac1575e13a474623c5a.png)

Any suggestion will be welcomed

---

<div class="post-metadata">

**Author:** ![giuseppe\_villan](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/giuseppe_villan/32/11871_2.png) [@giuseppe\_villan](https://community.neo4j.com/u/giuseppe_villan)\
**Post date:** [January 13, 2022, 9:48am UTC](https://community.neo4j.com/t/filtering-using-where-statement-in-datetime-format/50408/2 "2022-01-13T09:48:07Z")

</div>

> [@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'`):

```auto
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});

```

---

<div class="post-metadata">

**Author:** ![DMF194](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dmf194/32/20924_2.png) [@DMF194](https://community.neo4j.com/u/DMF194)\
**Post date:** [January 13, 2022, 10:19am UTC](https://community.neo4j.com/t/filtering-using-where-statement-in-datetime-format/50408/3 "2022-01-13T10:19:07Z")

</div>

Hi @giuseppe_villan,

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

It turns out that after running below cypher:

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

```

It showed STRING and ZoneDateTime.