Time and Decimal Semantics in Data Reports

A timestamp and an amount can look precise while leaving essential meaning unstated. A timestamp may represent an instant, a local wall-clock reading or a reporting date. An amount may represent minor units, a decimal quantity or a rounded line result. Choosing a database type does not settle those interpretation questions.

Define the meaning at ingestion and retain enough context for later reporting. Reconstructing a missing time zone or an unstated rounding policy from displayed values is often impossible. The ambiguity can stay hidden until two reports use different, equally plausible assumptions.

Separate an instant from a calendar label

An instant identifies a point on the time line. A local date identifies a calendar day under a particular regional rule. A business reporting date can add another rule, such as assigning work completed after a cutoff to the following operating day.

Store these meanings separately when they are independently useful. A producer event instant, ingestion instant and assigned reporting date answer different questions. Replacing all three with one timestamp makes it difficult to explain late arrivals and historical corrections.

In PostgreSQL, timestamp with time zone stores an instant and displays it according to the session time zone. It does not retain the original named zone as part of the value. If the source zone matters to later interpretation, keep that zone name in a separate validated field. See the upstream date and time type documentation for the exact type behavior.

Give intervals a consistent boundary

Half-open intervals include the start and exclude the end. Consecutive intervals then meet at one boundary without including the same instant twice. For an explicitly UTC reporting interval, the condition can be written as:

SELECT event_id, occurred_at
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-10-03 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-10-04 00:00:00+00';

This fragment assumes an events table with the named columns. It expresses a UTC day, not every region's local October 3. A local-day report needs the appropriate zone-based start and next-day boundary converted to instants.

Do not assume that every local calendar day is exactly twenty-four elapsed hours. Clock changes can alter that duration. Similarly, a local wall-clock time can be ambiguous or absent around a transition. Require the producer to provide enough offset or zone context to resolve the intended instant rather than silently guessing.

Keep late arrival policy visible

An event can occur within yesterday's reporting day and arrive today. Decide whether yesterday's published report is revised, whether a correction is published separately, or whether the event is assigned under a different documented rule.

Carry the report revision and completeness state with the output. A consumer comparing a frozen report with a recomputed current report should be able to see that the information boundary changed. The timestamp format alone cannot convey that decision.

Avoid relying on whichever session time zone happens to be active when a query runs. Set the intended reporting rules explicitly and test with events near both boundaries. A fixture containing only midday timestamps will not reveal most calendar mistakes.

Define the numeric unit

Record whether an integer amount is in major units, minor units or another scale. An integer value of 1250 might mean 1250 whole units or 12.50 major units. A decimal column can retain exact base-ten values within its chosen precision, but it still needs a unit and an allowed range.

For values that require exact decimal arithmetic, PostgreSQL's numeric type provides decimal storage and arithmetic semantics. Declared scale affects stored values, so choose it according to the contract. Floating-point types have a different representation and may be appropriate for approximate measurements. The numeric type documentation describes those distinctions.

Do not combine different units simply because their columns share a type. An amount in one currency and an amount in another remain different measures. Conversion requires an explicit rate identity, timing rule and resulting unit, beyond the scope of choosing a decimal type.

State where rounding happens

Rounding each line and then summing can differ from summing unrounded lines and rounding once. With two illustrative values of 0.005, rounding each to two decimal places under a stated half-up rule produces a total of 0.02. Summing first gives 0.010, which rounds to 0.01 under the same rule.

Neither sequence is universally correct. The report contract must specify the sequence and rounding mode. Retain the calculation inputs or rule revision needed to reproduce the accepted output. Display formatting should not silently change the stored calculation boundary.

Test meanings at the edges

Use fixtures containing an event exactly at the interval end, a late arrival, a regional clock transition and decimal values near a rounding boundary. Verify both record inclusion and derived amounts. Include a missing zone and an unknown unit as controlled failures.

These fixtures make interpretation inspectable. A trustworthy report is precise about what its timestamps and numbers mean, which population they describe, and which rules converted them into the displayed result.