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
