Use Unix timestamp conversion for dates from ArcGIS endpoints

[Ben-Hodgkiss]

Background

DateDataType.normalise() handles Unix timestamps by inspecting the digit length of the value and deciding whether to treat it as seconds or milliseconds. This works reasonably well for most cases, but breaks down for edge cases - most notably a value of 0, which represents 1970-01-01 as a Unix timestamp but has no digits once stripped and is therefore not recognised as a timestamp at all.

This issue was reported by an LPA whose conservation area was designated on 1970-01-01. Their data is published via ArcGIS, which always outputs dates as Unix timestamps regardless of the original date format. The checker flagged their value of 0 as an invalid date.

Problem

The current digit-length heuristic is a best-effort guess at whether a value is a Unix timestamp. It is inherently fragile for edge cases close to the epoch, and cannot distinguish between a Unix timestamp and other numeric strings without context.

However, when we know a dataset is sourced from an ArcGIS endpoint, we have that context - ArcGIS always outputs dates as Unix millisecond timestamps. We should use this information to handle date conversion deterministically rather than guessing.

Proposed fix

When processing a fact that originates from an endpoint that uses the ArcGIS plugin (as indicated in the endpoint CSV), the date conversion should:

1. Treat all numeric date values as Unix timestamps unconditionally
2. Apply millisecond conversion (divide by 1000) as ArcGIS outputs in milliseconds
3. Handle 0 and negative values correctly, including pre-epoch dates
   For endpoints that do not use the ArcGIS plugin, the existing heuristic behaviour should be preserved.

Why we can't just enable Unix conversion for all numeric values

It might seem simpler to treat any numeric string as a Unix timestamp, but short numeric strings are ambiguous with other date formats we legitimately need to support:

• 4 digits - would collide with %Y (e.g. 2020 is a year, not a timestamp for 1970-01-01 00:33:40)
• 8 digits - would collide with %Y%m%d (e.g. 20200102 is 2020-01-02, not a timestamp for 1970-08-20)
• 6-7 digits - fall in a similar ambiguous zone close to the epoch
  The ArcGIS-aware approach is preferable precisely because it removes the ambiguity - we are not guessing whether a value is a timestamp, we know it is.

Implementation notes

• The endpoint CSV already records which plugin is used for each endpoint
• This information needs to be threaded through to the date normalisation step so DateDataType (or its caller) can apply ArcGIS-aware conversion
• Consider whether this is best implemented as a flag on DateDataType (e.g. DateDataType(source="arcgis")), a subclass, or handled upstream before normalisation is called
• The ArcGIS millisecond timestamp range includes 0 (1970-01-01) and negative values (pre-epoch), both of which should be handled correctly

Acceptance Criteria

• Dates from ArcGIS endpoints are always treated as Unix millisecond timestamps
• A value of 0 from an ArcGIS endpoint normalises to 1970-01-01
• Negative millisecond timestamps from ArcGIS endpoints normalise to the correct pre-epoch date
• Non-ArcGIS endpoints continue to use the existing heuristic digit-length behaviour
• The fix is covered by unit tests with ArcGIS-sourced timestamp values including 0, positive, and negative values
• The reported case (0 → 1970-01-01) is confirmed to pass through the check tool without an invalid date issue

Related

• Existing Unix timestamp normalisation work in DateDataType.normalise() %s branch
• Known limitations document covering unhandled timestamp ranges around the epoch