One report across time zones: getting the clocks to agree

A logistics company with a warehouse outside Atlanta, another outside Dallas and a third outside Los Angeles has three different ideas of what today means. When the Georgia site closes its day, California is still shipping for three more hours. When head office asks for the day's numbers, every site answers about a slightly different day.

This is usually handled by not handling it. Each site runs an export at its own midnight, someone at head office stitches the spreadsheets together the next morning, and the definition of yesterday is whatever the stitching produced. It works until someone asks a question the stitched sheet was not built to answer.

Where the clocks disagree

Three separate problems hide inside the phrase time zones, and they need separating.

• Each warehouse database stamps its rows in local time, or in universal time (the single worldwide clock that ignores zones), or in whatever the system's vendor chose years ago; the three sites rarely match.
• Exports run at different local moments, so the daily file from each site covers a different window of universal time.
• Head office adds its own zone on top when it reads the files, so the same shipment can appear on two different days depending on who is looking.

None of these are solved by asking the sites to change their systems. The warehouse management system's clock behavior is baked in, and nobody wants to touch the system that runs the floor to fix a reporting problem.

One copy, every row stamped with its site

The approach we take leaves each site's system exactly as it is. A small service beside each warehouse database sends every added, changed or deleted row outward to one central store in your cloud account. Every row that lands centrally is stamped with the site it came from, and each site keeps its own space in the store.

That stamp is what makes the time zones tractable. Once you know a row came from the Texas site, you know its local zone, and you can convert its timestamps to a single reference consistently, every time, in the report rather than at the warehouse. Three inconsistent clocks become three known offsets from one clock.

The central copy is current to within a couple of minutes, end to end. There is no nightly export, and so there is no export window to reconcile. The question of what shipped in the last hour across all three sites has an answer at ten past the hour, not tomorrow morning.

A day, a shift or a peak

With every row stamped and current, head office can define its own windows and apply them uniformly.

1. A calendar day in one reference zone, so a day is the same twenty-four hours at every site.
2. A shift, defined in local time at each site, so the night shift in Georgia and the night shift in California are compared like for like.
3. A peak event, from a chosen start moment to a chosen end moment, measured against all three sites together as it unfolds.

The first is the finance view; the second is the operations view; the third is the one everybody wants at nine in the morning on Cyber Monday and could never get before. They are three reports on the same data, not three data pipelines.

What head office gives up, and what it does not

The central copy is not the warehouse database, and it lags it by a couple of minutes. For a report that is a rounding error. For a floor supervisor who needs to know what the next pick is, it is the wrong tool, and that supervisor should stay on the warehouse system, which is untouched.

What head office no longer does is wait. It does not wait for a site to run an export, does not wait for a spreadsheet to arrive, and does not spend the morning working out why two sheets disagree. Regional managers and a customer portal read the same central copy, so there is one set of numbers rather than one per person who asked.

Keeping it honest across bad links

Sites are not always connected. If the link at one site drops, its changes queue on site, compressed and encrypted, and resume from where they stopped when the link returns. Nothing is lost, but during the outage that site's rows stop arriving, and a cross-site report for that window will be missing them until they catch up.

A useful report shows this rather than hiding it: the time of the newest row received from each site, right on the dashboard. A regional manager who sees that the Texas feed is forty minutes behind knows to read the Texas column accordingly. That is one of the first things we recommend building, and it costs almost nothing once the site stamp is there.

Three warehouses, three clocks, one copy stamped by site; the arithmetic becomes a report, not a morning.

redfly keeps the central copy current from every site; your team builds the day, shift and peak views on top of it, with our support.