cancel
Showing results for 
Search instead for 
Did you mean: 

Join the community at Nodes 2022, our free virtual event on November 16 - 17.

Protip: cypher vs apoc weekdates

abk
Neo4j
Neo4j

A recent bit of Cypher query debugging reminded me that dates are complicated as a data value. The query was iterating through a collection of nodes with creation date timestamps, which were then being bucketed into weeks of the year.

The problem was that the buckets were somehow wrong. The reason was that APOC's date parsing and Cypher/ISO date parsing uses different week numbers.

Consider the following example:

WITH 2021 as year, 39 as week
WITH apoc.date.parse(year + " " + week, "ms", "YYYY w") as apocWeekDateMillis,
                dateTime({date:date(year + "-W" + week)}).epochMillis as cypherWeekDateMillis
RETURN date(dateTime({epochMillis: apocWeekDateMillis})) as apocWeekDate, apocWeekDateMillis,
       date(dateTime({epochMillis: cypherWeekDateMillis})) as cypherWeekDate, cypherWeekDateMillis

The apoc.date.parse() and the date() both parse a string formatted as a combination of year and week-number. The result is two different dates for the same week-number.

+---------------------------------------------------------------------------+
| apocWeekDate | apocWeekDateMillis | cypherWeekDate | cypherWeekDateMillis |
+---------------------------------------------------------------------------+
| 2021-09-19   | 1632009600000      | 2021-09-27     | 1632700800000        |
+---------------------------------------------------------------------------+

Which corresponds with expected reality Week Number 39 (Week 39) 2021

For insight about the differences, wikipedia is our friend: ISO week date - Wikipedia

For a great, in-depth exploration of dates check out @jennifer.reif 's excellent series "Cypher Sleuthing: Dealing with Dates" starting with...

Cheers,
ABK

1 REPLY 1

This is one I didn't know about or document in my series! Thanks for posting this, Andreas.

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit - November 16 - 17, 2022.


Free NODES Training Series


October 19th -

Intro to Neo4j


October 20th -

Healthcare Analytics Using Neo4j


October 25th -

Handling Neo4j data with Apache Hop


October 26th -

Blazing Fast Graphs: Hands-on with Apache Arrow and Neo4j


November 2nd -

Graph EDA Using the Neo4j GDS Client