Put every daily total on the same clock

List the clocks before moving data

Record each source’s timezone setting, timestamp representation and day boundary. Check whether an export provides an absolute timestamp, a local time without timezone, or only a date. Those inputs do not support the same conversions.

Choose the reporting question. A global finance view may use one consistent timezone, while a staffing review may need each location’s local day. Neither choice is automatically superior. Document the reason and keep the source convention available so another analyst can reproduce the transformation.

Convert the instant before assigning the date

Where reliable timestamps exist, convert to the intended reporting timezone before deriving the day or hour. PostgreSQL documents distinct timestamp types and date-dependent timezone behavior. A time without its date and timezone may be insufficient to identify the correct instant around clock changes.

Use a named timezone with maintained rules instead of a permanently assumed offset where daylight saving applies. Do not claim an hourly correction from a daily aggregate that lacks timestamps. Such data may support a coarser reconciliation or an explicitly qualified comparison, rather than a precise redistribution.

Test the boundaries that matter

Create a small fixture with records just before and after local midnight, the month boundary and relevant daylight-saving transitions. Confirm that an event is counted once and assigned to the expected period. Test half-open intervals so the ending instant of one period becomes the beginning of the next without overlap.

Inspect repeated or missing local hours carefully. A day can have a different elapsed duration during a clock change. Keep absolute timestamps for ordering and duration calculations where appropriate, while using the agreed local date for the business grouping. Avoid repairing the chart by silently deleting a repeated hour.

Explain remaining differences honestly

After alignment, compare totals over a wider interval as well as individual days. A discrepancy that moves across neighboring dates suggests a boundary issue; a persistent total gap may have another cause. Do not use timezone as a convenient explanation for every mismatch.

Publish the timezone and cutoff in the report’s definition and export metadata. If a setting changes, version the report and assess the break in comparability. Obtain the appropriate owner’s approval before changing source settings, because those changes can affect more than the dashboard being repaired.

Make the reasoning concrete.

A hypothetical sale occurs at 00:30 UTC on June 2, which is 17:30 on June 1 in Los Angeles during that date’s daylight-saving period. A UTC order export and a Los Angeles media report place the same instant on different dates.

Use the chosen timezone consistently and disclose any source that cannot be aligned at the available granularity. The conversion fixes period membership, not attribution.

Take the questions into your next brief.

Use these prompts in your own notes. Nothing is submitted from this page.

1. Sources

   What clocks and timestamp types does each source use?

2. Question

   Which business day fits the decision?

3. Transformation

   Is conversion performed before date grouping?

4. Fixtures

   Which midnight and clock-change cases must pass?

5. Limits

   Which sources provide only aggregates?

Watch for the wrong conclusion.

• Joining equal date labels from different clocks.
• Using a fixed offset throughout the year.
• Inventing hourly detail from daily aggregates.
• Changing account settings without reviewing downstream effects.

Sources & scope

Frameworks, worksheets and hypothetical examples are original editorial synthesis. Linked primary sources support only the stated concepts. Source checking is not an independent audit of effectiveness. No named human author or reviewer is claimed.

• PostgreSQL: Date/time types →Primary technical documentation · Supports: Documents timestamp types, timezone conversion and date-dependent daylight-saving behavior. Reporting cutoff rules and reconciliation examples are original editorial analysis.