Comparison of datetime with offset

Neo4j Graph Platform
1

I'm somewhat confused about datetime comparisons in neo4j. I have a data source with datetimeoffset (from SQL Server) It seems that datetime() with iso8601-dates with different offsets aren't comparable (at least not like in C#, JavaScript and T-SQL).

Consider the following:

with
datetime('2021-08-05T10:06:19.230+0100') as time1,
datetime('2021-08-05T11:06:19.230+0200') as time2,
datetime('2021-08-05T10:06:19.231+0100') as time1_plus_1ms
return
duration.between(time1,time2) as duration,
time1=time2,
time1<time2,
time1_plus_1ms<time2,
time1.epochMillis,
time2.epochMillis;

This yields

[
  {
    "duration": "P0M0DT0S",
    "time1=time2": false,
    "time1<time2": true,
    "time1_plus_1ms<time2": false,
    "time1.epochMillis": 1628154379230,
    "time2.epochMillis": 1628154379230
  }
]

Is this a bug? I would expect time1=time2 to be true, note the epochMillis equality. At first I thought that the comparison ignored the offset, but that's not what's happening according to the +1 ms comparison. This behavior makes no sense to me as the only meaningful way to compare two points in time is to see whether they represent the same point in time, or if not, which of them occurred earlier. C#, T-SQL and JavaScript agrees with me.

I would like to keep the offset value, avoid storing two different values, and being able to compare them in a meaningful way with indexing. If I need to format the output to create an ISO format that's fine, but searching for either time1 or time2 should match properties in both formats (it doesn't do so according to my testing). What's the best practice workaround?

Thanks :)

2

I have encountered the same issue in Neo 4.3.2.

This looks like a bug to me.

3

I've just observed this behaviour, and looking for an explanation, I found this post.

The closest explanation I've come across as to the reason is this answer on stack overflow, which states:

[...] to compare composite objects you need to compare all the key-value pairs within them.

So, it appears that the two datetimes are not compared by the underlying date and time they represent, but by each of their properties.

The stack overflow answer suggests comparing the epochMillis property of the datetimes in order to make a comparison independent of the time zone, which appears to work.