Marketplace seller software workflow: Amazon timestamp export errors

A timestamp mismatch in a marketplace seller software operating workflow can begin in the report, not in the order itself. An Amazon API response, a reporting script and a spreadsheet may represent the same instant differently. This example follows Amazon order timestamps into a seller-built Python CSV export and investigates how their interpretation can change.

The source fields, a dropped-offset example and checks for distinguishing a formatting error from an unresolved timestamp are set out below. The task is to correct the interpretation without changing the underlying order history, while keeping enough evidence to locate where the representation may have changed. The focus remains on the timestamp values and how each component handles them.

Locate the Amazon field before interpreting the time

Start with the source response: Amazon Selling Partner API (SP-API), Orders API v0, using the getOrder operation. Its response includes AmazonOrderId, PurchaseDate and LastUpdateDate, the fields needed to identify the order and the two order timestamps under review. Confirm the operation and response fields before interpreting the export.

PurchaseDate describes when the order was created, while LastUpdateDate describes when it was last updated. Neither field means when your reporting script downloaded the response. Keep the retrieval time distinct from the timestamps that describe the order itself, because they refer to different events and should not be compared as if they described the same moment.

In a successful getOrder response, inspect the JSON values under payload.PurchaseDate and payload.LastUpdateDate. Use payload.AmazonOrderId to connect the response to the order being investigated, and confirm the response structure against the API version your integration actually uses. Amazon's official Selling Partner API models are the starting point for checking the operation and Order model. A column heading created by your export is not a substitute for that definition.

Keep the following evidence together:

A retrieval timestamp belongs to the retrieval operation, not to the order's creation. The reporting-script log can establish when the response was obtained and handled, so do not compare that retrieval time with PurchaseDate and call the difference a clock error. First establish which event each timestamp describes, using the saved response and log as separate evidence.

Preserve offsets and distinguish them from time zones

Keep the complete source value before parsing it. ISO 8601 date-time representations can include a UTC offset, and a Z suffix identifies UTC.

An offset-free value does not become UTC merely because its column is labelled "timestamp". Retaining the full value keeps its original interpretation visible during investigation and prevents the export from hiding what was supplied.

An offset describes the relationship to UTC at that instant, while a time zone supplies rules that can change that relationship over time. For UK local display, UK clock-change rules are relevant when interpreting the Europe/London identifier, which provides rules for Greenwich Mean Time and British Summer Time. Keep the zone identifier distinct from a single offset.

A fixed offset is therefore not a year-round replacement for Europe/London. If the export uses UK local time, check whether it applies the zone's rules to the timestamp's date, rather than applying the computer's current offset to every historical record. The date matters when interpreting the local clock reading, so preserve it with the time and its offset during the comparison.

At the autumn clock change, some local clock readings occur twice, and at the spring change, some do not occur. An offset-free local timestamp around a clock change may therefore be ambiguous or invalid, even though it looks like an ordinary date and time. Inspect browser, spreadsheet and account display settings separately, because a correct source value can look different on two screens without either underlying record being wrong.

Build an export that keeps the original interpretation visible

The reporting component here is a seller-built Python script using the standard-library datetime and csv modules. This is an illustrative diagnostic route, not a claim about any supplier's connector behaviour.

Keep the original source interpretation visible alongside any normalised comparison value, so the conversion can be checked against the retained input.

Keep a raw column alongside a normalised comparison column, and add the source field name so that PurchaseDate cannot silently become a generic "order time" column. A useful diagnostic row records the order identifier, source field, raw timestamp, any known UTC timestamp, retrieval time, transformation and uncertainty. Keeping these details together makes the row available for review:

This parsing fragment uses Python's standard-library datetime module to interpret an explicit offset and reject an offset-free value rather than assuming one. The distinction matters because the input must have an established time meaning before it can be converted into a UTC comparison value. It is a focused check, not a complete export routine, and it leaves the input interpretation visible.

from datetime import datetime, timezone

raw = payload["PurchaseDate"]
parsed = datetime.fromisoformat(raw.replace("Z", "+00:00"))

if parsed.utcoffset() is None:
    raise ValueError("Offset missing: do not assume UTC")

utc_text = parsed.astimezone(timezone.utc).isoformat()

Write both raw and utc_text into separately named CSV columns. The fragment does not define the whole reporting workflow; it shows how to reject an undocumented offset-free input rather than quietly treating it as UTC. Keep the original source value available alongside the transformation being investigated, so the output can be checked against the evidence.

Use the workflow ownership map to identify who can confirm the source definition and who can change the export. This remains an interpretation problem until evidence shows otherwise, so the investigator should not rewrite the order to match the report. Keep the source record unchanged while the export is examined.

Worked example: a dropped offset creates an apparent difference

Consider a synthetic example during British Summer Time. The table shows actual time-and-offset portions, with the calendar date omitted rather than invented.

In a real investigation, preserve and compare the complete date-time values, including the date that is not shown here, because it is still needed to interpret the complete record.

The intended local value and Amazon value represent the same instant: subtracting the supplied offset from the local clock reading gives the UTC reading. The error occurs when the export drops +01:00 and a later process assumes the remaining value is UTC, changing the interpretation of the timestamp. The date is needed for the complete record, even though this illustration omits it.

Open the CSV as plain text before blaming the spreadsheet. If the CSV contains 10:15:00 but the export code was meant to preserve 10:15:00+01:00, inspect the formatting instruction that wrote the column. A format containing only the date and clock time can discard the offset, so the text in the file helps locate the component responsible for the change.

If the CSV still contains the offset but the spreadsheet displays only 10:15:00, the visible cell alone does not establish data loss. Inspect its underlying value and import behaviour to find out whether the offset was retained, converted or ignored. The display and the text written to the file are separate evidence locations, so compare both before deciding whether the export changed the value.

The supported finding is limited to the export or its interpretation changing the timestamp representation. That does not show that Amazon created the order late, processed it twice or changed its commercial status. Keep the conclusion within what the saved response, export and display evidence can establish, and do not extend it to events the evidence does not show.

Diagnose the remaining mismatch in a fixed order

Work through these checks before applying a correction. Each check should either establish an explanation or identify the evidence still missing, so the diagnosis does not begin with an assumption about which component is at fault.

Compare the full values and their meanings before deciding whether the issue is in the source, export or display.

If an internal export joins order and stock data, establish each timestamp's meaning independently. The inventory software checks help identify the source stock event; they do not make a stock movement timestamp equivalent to an order's PurchaseDate. Keep the event definitions separate during the comparison, because the timestamps describe different events.

Do not use timestamp equality as the record-matching rule. Different orders can share the same displayed time, and one order can have several timestamps with different meanings. Where the raw value or its interpretation is unavailable, leave the conclusion unresolved rather than choosing the conversion that makes the report look tidy. Record what remains unknown.

Correct the export, then state what the evidence proves

Correct the component responsible for the problem: if the CSV writer drops offsets, correct the writer; if the CSV is intact but the spreadsheet imports it incorrectly, correct the import process or keep the timestamp column as text for inspection.

Base the correction on evidence showing where the representation changed, rather than on the appearance of the final display alone.

Preserve the original source value and document the replacement transformation. Regenerate the diagnostic export from the source evidence where available, rather than editing individual cells until their order looks plausible. This keeps the corrected output connected to the values and transformation under investigation, and maintains a basis for reviewing the correction.

Check the correction against offset-bearing inputs, UK seasonal clock changes and values with fractional seconds. An offset-free input should remain explicitly unresolved unless its time-zone meaning is established by reliable documentation. Record any remaining uncertainty rather than presenting an assumed conversion as established, and keep the raw value available for review.

Close the investigation with a short finding containing the order identifier, field, affected component, demonstrated transformation and remaining uncertainty. For the worked example, the finding would be: the export removed an explicit offset and downstream interpretation treated local time as UTC; the source order timestamp was not shown to be wrong. State only what the evidence demonstrates.

That conclusion delivers something useful to operations: a specific reporting defect and a reproducible correction, without turning a timestamp discrepancy into permission to alter or repeat the underlying marketplace action. It keeps the decision tied to what the saved response, export and display evidence actually establish, rather than extending the finding beyond the available records.