x0
Date transform (Xero → ISO / Excel)
Roadmap: option-date-julian (priority 26). Xero’s /Date(milliseconds)/ strings
are a major pain for Excel, Power Query, and most downstream tools.
| Piece | Where |
|---|---|
| Service | portal/services/date_transform.py |
| Option code | date_format_iso in options_available (serve format / mode) |
| Applied on | Ingest — as JSON arrives from Xero, before CustomerTableCache write. Every row, every nested date. |
| Leftover cache | Serve path (get_table_read) still rewrites any remaining /Date(ms)/ |
| Header | X-X0-Dates: iso\|excel\|date_only\|julian |
Cache storage is ISO (yyyy-mm-ddThh:mm:ss UTC). The Microsoft JSON form is not kept.
What /Date(ms)/ actually is
Xero uses the ASP.NET / Microsoft JSON Date encoding, not a Unix-seconds string.
/Date(1717469163173+0000)/
^^^^^^^^^^^^^ ^^^^
milliseconds optional ±HHmm offset (ignored; ms are already UTC)
The number is milliseconds since 1970-01-01 00:00:00 UTC (Unix epoch). Divide by 1000 to get seconds. 1717469163173 → 2024-06-04T02:46:03 UTC.
Empty Xero sentinels near DateTime.MinValue (/Date(-62135596800000)/) become JSON null.
Formats
| Value | Output example | Where |
|---|---|---|
iso (default) |
2020-01-01T00:00:00 |
Stored in cache; OData, Power Query, APIs |
excel |
2020-01-01 00:00:00 |
Serve-only; Excel cells / CSV that hate the T |
date_only |
2020-01-01 |
Serve-only; day-level reporting |
julian |
43831.0 (number) |
Serve-only; Excel serial datetime (day + fraction). Noon UTC → 43831.5 |
Julian is not stored: Excel serials would break incremental watermarks. ISO in JSON is date-format-safe and sorts correctly; Power Query / Excel parse it as datetime.
UTC is used for conversion.
Serve modes (non-ISO)
When format is excel, date_only, or julian:
mode |
Behaviour |
|---|---|
postfix (default) |
Leave base fields as ISO; add siblings e.g. UpdatedDateUTC_julian |
replace |
Overwrite base date fields with the chosen format |
Postfix keeps existing spreadsheets / apps stable when someone opts into julian for Excel serial maths.
{"format": "julian", "mode": "postfix"}
{"format": "julian", "mode": "replace"}
Who gets it
ISO ingest is always on (every plan). Queries no longer return /Date(ms)/.
Explicit CustomerOption for date_format_iso only changes the serve shape:
is_enabled=True+value={"format":"julian"}→ ISO base +*_juliansiblings (postfix).is_enabled=True+value={"format":"julian","mode":"replace"}→ overwrite base fields.is_enabled=False→ still ISO (cannot opt back into Microsoft JSON dates).
python manage.py seed_options
Admin → Customer options → add date_format_iso with value e.g. {"format": "julian", "mode": "postfix"}.
Existing snapshots keep /Date(ms)/ until the next sync (full or incremental); that write rewrites the whole table, and serve already converts leftovers.
Examples
Xero (never stored):
{"UpdatedDateUTC": "/Date(1577836800000+0000)/", "Name": "Sales"}
Cache + default OData (iso):
{"UpdatedDateUTC": "2020-01-01T00:00:00", "Name": "Sales"}
Serve julian + postfix (default when format ≠ iso):
{"UpdatedDateUTC": "2020-01-01T00:00:00", "UpdatedDateUTC_julian": 43831.0, "Name": "Sales"}
Serve julian + replace:
{"UpdatedDateUTC": 43831.0, "Name": "Sales"}
Nested line items and child objects are walked recursively.
jq alternative
Free-form jq pipelines can also rewrite dates (xero-dates-to-iso template). Prefer this
built-in ingest transform — it runs before storage so every consumer sees ISO.