is "the same instant with a +02:00 offset is stored as 03:04:05 from a String and as 01:04:05 from an
OffsetDateTime"; #10498 (CLOSED) is the bulk path rebasing onto the database zone. Both are about the
chain that has NO format named - DateUtils.parseDateTime, the one that carries the OffsetPolicy.
Neither issue names asDateTime(format), asDate(format) or date(value, format), the three sites that
parse with a caller-named pattern and never reach that chain. #8090 (CLOSED) unified WHICH FORMATS the
no-format chain accepts, not what an explicit one does with an offset.

Summary

The rule #10498 installed and wrote into DateUtils's javadoc is: "One rule for an offset-bearing
input, on every write path: the instant it names is stored as the UTC wall clock ... so a client writing
over HTTP or SQL, one writing over Bolt or Java, and the bulk load store one value for one instant."

The rule lives in DateUtils.parseDateTime(database, string) / OffsetPolicy.CONVERT_TO_UTC /
dropZone. A caller that names a pattern does not go there. DateUtils.parse(text, format) is a
one-line LocalDateTime.parse(text, getFormatter(format)): LocalDateTime.from takes the LOCAL fields
of the parse and discards the offset the pattern just read, so the wall clock written is the wall clock
of the text, not of the instant.

The tell is that the SAME pattern obeys the rule when the schema applies it. A schema
dateTimeFormat that carries XXX reaches parseSchemaOrSqlTimestamp, which returns an
OffsetDateTime, which parseDateTime runs through dropZone to UTC. Written out by name in
asDateTime('<the same pattern>'), the same text yields a different instant into the same column.

Secondary: the timezone argument of date(value, format, timezone) is inert on this path too. It is
applied as formatter.withZone(...), which LocalDateTime.parse ignores whenever the pattern supplies
the local fields - which it always does here. date(s, p, 'UTC') and date(s, p) answer the same thing
(shown below), so the option looks like it controls the conversion and does not.

Where (HEAD 8b93bb7)

• engine/src/main/java/com/arcadedb/query/sql/method/conversion/SQLMethodAsDateTime.java:76-79
  the params[0] != null branch calls DateUtils.parse; the else branch, changed by GraphBatch bulk path rebases an offset-bearing DATETIME string onto the database zone, while Type.convert stores the UTC wall clock #10498 in this
  same window, calls DateUtils.parseDateTime. The two branches of one ternary disagree.
• engine/src/main/java/com/arcadedb/function/sql/time/SQLFunctionDate.java:94-96 the same shape: the
  format == null branch was changed to DateUtils.parseDateTime by GraphBatch bulk path rebases an offset-bearing DATETIME string onto the database zone, while Type.convert stores the UTC wall clock #10498, the else branch is
  LocalDateTime.parse(dateAsString, formatterFor(format, timezone, context)).
• engine/src/main/java/com/arcadedb/utility/DateUtils.java:1095-1097 - parse(text, format), the one
  parse in DateUtils that takes no OffsetPolicy and the only one whose javadoc does not state the
  rule. Two callers (SQLMethodAsDate, SQLMethodAsDateTime); SQLFunctionDate holds a third copy of
  the same expression.
• engine/src/main/java/com/arcadedb/query/sql/method/conversion/SQLMethodAsDate.java:68-71 - same
  split, but its target is a LocalDate, for which dropping the offset is the documented
  parseDateTimeKeepingWallClock rule. Listed for the census, not claimed as a defect.

Repro

arcadedb.dateTimeImplementation = java.time.LocalDateTime (the default), database zone
America/New_York, alter database dateTimeFormat "yyyy/MM/dd HH:mm:ss XXX", a DATETIME property
T.ts. Input '2020/01/02 03:04:05 +02:00', whose instant is 2020-01-02T01:04:05Z. The pattern below
is BOTH the schema's dateTimeFormat and the one named in the call:

With the ISO spelling and an explicit ISO-offset pattern, independent of the schema's format and of the
database zone (identical under zone UTC and America/New_York):

Census of what the rest of the family answers for the same input, all 2020-01-02T01:04:05, i.e. the
rule holds everywhere a pattern is not named: INSERT ... SET ts = <string> (SQL), a bound ?
parameter, an OffsetDateTime/ZonedDateTime/Instant/java.util.Date parameter, openCypher
datetime('<string>'), openCypher SET n.ts = '<string>', DateUtils.parseDateTime called directly,
and the schema-dateTimeFormat parse.

Refutation attempted: that an explicitly named pattern means "read this text literally, offset and all"

• no, the same pattern via the schema converts, so the pattern is not what decides; that this is the
  KEEP_WALL_CLOCK policy applied on purpose - no, parseDateTimeKeepingWallClock exists, is reachable,
  and its javadoc says a DATETIME does NOT read its strings that way ("parseDateTime stores the instant
  the text names, as an OffsetDateTime value is stored (issues The same instant with a +02:00 offset is stored as 03:04:05 from a String and as 01:04:05 from an OffsetDateTime, so HTTP and Bolt write different DATETIME values #10489, GraphBatch bulk path rebases an offset-bearing DATETIME string onto the database zone, while Type.convert stores the UTC wall clock #10498)"); DateUtils.parse does
  not call it either, it bypasses the policy enum entirely; that the database zone explains the 2-hour gap
• no, the gap is the input's own +02:00 and it is the same under a UTC database.

Suggested fix

Give DateUtils.parse(text, format) the same OffsetPolicy the rest of DateUtils carries, and have
it run an offset-bearing parse through dropZone exactly as parseDateTime does: parse to a
TemporalAccessor, and if it yielded an offset, convert. SQLMethodAsDate then asks for
KEEP_WALL_CLOCK (its documented rule), SQLMethodAsDateTime and SQLFunctionDate for
CONVERT_TO_UTC. SQLFunctionDate's inline LocalDateTime.parse(...) should call the shared one
rather than hold a third copy.

The guarantee that stops it coming back: make LocalDateTime.parse(CharSequence, DateTimeFormatter)
unreachable outside DateUtils (a source-scan test over the modules, the same shape as the existing
census tests) so that every text-to-DATETIME conversion has to pass through a method that takes an
OffsetPolicy and therefore has to answer the question. Today the rule is a property of one method and
nothing stops a second parse being written next to it - which is how these three got there.

Scope / confidence

Reproduced against HEAD 8b93bb7ee2. Affects any asDateTime(format) / date(value, format) whose
format carries an offset field (X, XXX, Z, ZZZZ, O, VV) and whose input carries one, on
every surface that runs SQL. Not affected: inputs without an offset (no instant to convert), and
asDate(format) whose target has no zone to keep.

Swept clean

Each item below was checked empirically against HEAD 8b93bb7ee2, not only read.

Still present at the current main revision: every code point named above was re-checked there before this was filed.