Comparison of datetime with offset

[anders_liane]

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 🙂

[cheezly]

I have encountered the same issue in Neo 4.3.2.

This looks like a bug to me.

[clandestino_bgd]

I am having this issue with 4.4.12.

Seems really critical to me, since many of my traversals are comparing datetimes.

I posted the same question on discord yesterday, no answer.

I would expect that internally 2 Datetimes are compared using epochMillis, which is the same in this example.

WITH datetime("2022-11-01T08:00:00+01:00") AS d1, datetime("2022-11-01T07:00") AS d2

RETURN d1.year, d2.year, d1.month, d2.month, d1.day, d2.day, d1.hour, d2.hour, d1.minute, d2.minute, d1.second, d2.second, d1.millisecond, d2.millisecond, d1.timeZone, d2.timeZone, d1.offset, d2.offset, d1.epochMillis, d2.epochMillis


The hours, offset and timezone is different for d1 and d2. Everything else is the same, even the duration between those 2 datetimes is 0s.
Hope someone from Neo4j will be able to help.

Thanks