Part 014 — Time-Series and Temporal Data

Time is one of the easiest things to underestimate in backend systems.

It looks simple:

created_at timestamptz not null default now()

Then production reality arrives:

• customers operate in different time zones;
• prices have effective dates;
• product catalogs are versioned;
• quotes must preserve historical values;
• orders move through lifecycle states;
• audits must be legally defensible;
• events arrive late;
• CDC emits changes after commit;
• reporting groups data by local business day;
• retention deletes old data;
• large history tables grow forever;
• tests fail around daylight saving transitions;
• Java maps timestamps differently than expected;
• APIs expose ambiguous dates;
• support teams ask what happened “yesterday” in a tenant's region.

Temporal modelling is not only about choosing a timestamp column.

It is about modelling time as part of correctness.

In CPQ/order management systems, time influences:

• pricing validity;
• catalog version selection;
• quote expiry;
• order submission time;
• approval deadline;
• fulfilment SLA;
• contract term;
• audit trail;
• event ordering;
• reconciliation windows;
• reporting periods;
• retention policy.

If time is modelled casually, the system becomes hard to debug and unsafe for enterprise operations.

1. Core mental model

There are multiple kinds of time.

They must not be mixed blindly.

A production data model often needs more than one timestamp because these meanings are different.

Bad model:

created_at timestamptz not null default now()

used for everything.

Better model:

created_at timestamptz not null default now(),
submitted_at timestamptz,
effective_from date not null,
effective_to date,
expires_at timestamptz,
last_status_changed_at timestamptz

Each column should answer a specific business question.

2. PostgreSQL timestamp types

PostgreSQL commonly uses:

The name timestamp with time zone is slightly misleading.

It does not store the original time zone label per row.

It stores an absolute instant and renders according to session time zone.

That means if you need to preserve the customer's original local time zone, store it separately.

Example:

submitted_at timestamptz not null,
submitted_timezone text not null

or store a business region/tenant timezone reference.

3. Default posture: use timestamptz for instants

For backend systems, use timestamptz for instants:

• row creation;
• row update;
• event occurrence;
• audit entry;
• order submission;
• approval decision;
• outbox creation;
• ingestion time;
• processing time;
• job execution time.

Example:

create table order_status_history (
    order_status_history_id uuid primary key,
    order_id uuid not null,
    old_status text,
    new_status text not null,
    changed_at timestamptz not null default now(),
    changed_by text not null,
    change_reason text
);

This supports ordering, comparison, audit, and cross-region debugging.

In Java, map to Instant when possible.

Avoid mapping true instants to LocalDateTime unless the semantics are intentionally local and not global.

4. When to use date

Use date when the concept is a calendar date, not an instant.

Good examples:

effective_from date not null,
effective_to date,
contract_start_date date,
contract_end_date date,
billing_cycle_date date

Pricing effective dates often use calendar dates because business rules may say:

This price is valid from 2026-07-01.

That is not necessarily a precise instant unless the business defines timezone boundary.

But this creates an important question:

Which timezone defines the business date?

A date-only rule still needs a business calendar context.

Possible contexts:

• tenant timezone;
• market timezone;
• customer timezone;
• product catalog timezone;
• UTC date;
• contractual region;
• billing system calendar.

Do not assume.

Document it.

5. When timestamp without time zone is dangerous

timestamp without time zone stores a local date-time without timezone semantics.

Example:

'2026-07-11 09:00:00'

But 09:00 where?

Jakarta? UTC? New York? Customer site? Database server timezone?

This is dangerous for global enterprise systems when the value represents an instant.

Valid uses exist:

• local store opening time;
• recurring local schedule;
• a form that explicitly captures local time plus separate timezone;
• legacy integration payload where timezone is handled elsewhere.

If using timestamp without time zone, be explicit:

local_start_time timestamp without time zone not null,
timezone_id text not null

Never let timestamp without time zone silently represent an instant.

6. UTC policy

A practical backend rule:

Store instants as timestamptz, treat application instants as UTC, render local time only at boundaries.

Boundaries include:

• API request/response;
• UI display;
• external integration;
• reporting period calculation;
• business calendar logic.

Inside the service and database:

• compare instants as instants;
• use Instant or equivalent in Java;
• avoid server-local timezone assumptions;
• keep database/session timezone predictable;
• log timestamps in UTC or with offset;
• include timezone in user-facing rendering.

Do not solve timezone by stripping it.

That only hides the bug.

7. Java type mapping

Common Java mapping guidance:

For Java/JAX-RS APIs:

• expose instants with offset or ISO-8601 UTC format;
• avoid ambiguous date-time strings;
• define whether request fields are instants or business dates;
• validate timezone-aware inputs;
• do not let JSON serialization default silently decide semantics.

Bad API field:

{
  "submittedAt": "2026-07-11 09:00:00"
}

Better:

{
  "submittedAt": "2026-07-11T02:00:00Z"
}

or if business local time matters:

{
  "appointmentLocalTime": "2026-07-11T09:00:00",
  "timezoneId": "Asia/Jakarta"
}

8. Effective dating

Effective dating models business validity.

Common table:

create table price_version (
    price_version_id uuid primary key,
    product_offering_id uuid not null,
    currency_code char(3) not null,
    recurring_amount numeric(19, 4) not null,
    effective_from date not null,
    effective_to date,
    created_at timestamptz not null default now(),
    check (effective_to is null or effective_to > effective_from)
);

Query current price:

select *
from price_version
where product_offering_id = #{productOfferingId}
  and currency_code = #{currencyCode}
  and effective_from <= #{businessDate}
  and (effective_to is null or effective_to > #{businessDate})
order by effective_from desc
limit 1;

Common convention:

valid interval = [effective_from, effective_to)

That means:

• inclusive start;
• exclusive end;
• effective_to is first date/time when row is no longer valid.

This avoids overlap ambiguity at boundaries.

9. Preventing overlapping effective ranges

A key correctness issue:

For the same product/currency/market, two active price versions must not overlap.

Application validation alone may fail under concurrency.

In PostgreSQL, this can be modelled using exclusion constraints with range types, if the schema is designed for it.

Example concept:

create extension if not exists btree_gist;

create table price_version (
    price_version_id uuid primary key,
    product_offering_id uuid not null,
    currency_code char(3) not null,
    valid_period daterange not null,
    recurring_amount numeric(19, 4) not null,
    exclude using gist (
        product_offering_id with =,
        currency_code with =,
        valid_period with &&
    )
);

This says overlapping valid periods are not allowed for the same product/currency.

Whether your team uses this pattern depends on internal standards and extension availability.

If not using exclusion constraints, you still need a concurrency-safe mechanism:

• locking;
• serializable transaction;
• constraint-backed design;
• controlled migration scripts;
• validation queries;
• review discipline.

10. Temporal validity vs audit history

Do not confuse temporal validity with audit history.

Temporal validity answers:

During which business period was this row valid?

Audit history answers:

Who changed what, when, and why?

Example temporal table:

create table catalog_price_version (
    price_version_id uuid primary key,
    product_offering_id uuid not null,
    valid_from date not null,
    valid_to date,
    amount numeric(19, 4) not null
);

Example audit table:

create table catalog_price_audit (
    audit_id uuid primary key,
    price_version_id uuid not null,
    changed_at timestamptz not null default now(),
    changed_by text not null,
    operation text not null,
    old_row jsonb,
    new_row jsonb
);

A price can be valid from January, but inserted into the system in December.

Both facts matter.

11. Event time vs processing time

In event-driven systems, event time and processing time differ.

Example:

create table order_event_inbox (
    event_id uuid primary key,
    event_type text not null,
    aggregate_id uuid not null,
    event_occurred_at timestamptz not null,
    received_at timestamptz not null default now(),
    processed_at timestamptz,
    processing_status text not null,
    payload jsonb not null
);

event_occurred_at means when the upstream domain event happened.

received_at means when this service received it.

processed_at means when this service finished processing it.

These answer different debugging questions.

If you only store one timestamp, incident analysis becomes harder:

• Was the upstream late?
• Was Kafka lagging?
• Was this consumer delayed?
• Was the database transaction blocked?
• Was the event replayed?

Temporal observability starts at schema design.

12. Commit time and outbox time

In transactional outbox, timestamps need careful semantics.

Example:

create table outbox_event (
    outbox_event_id uuid primary key,
    aggregate_type text not null,
    aggregate_id uuid not null,
    event_type text not null,
    event_occurred_at timestamptz not null,
    created_at timestamptz not null default now(),
    published_at timestamptz,
    status text not null,
    payload jsonb not null
);

event_occurred_at may represent domain event time.

created_at represents when the outbox row was inserted.

published_at represents when publisher delivered or attempted delivery.

For ordering, the database commit order may matter more than application-generated timestamps.

If using CDC, logical decoding order follows WAL/commit order, not necessarily event timestamp order.

Do not assume timestamps alone define strict ordering in distributed systems.

13. Audit timestamp design

Audit tables should answer:

• what changed;
• when it changed;
• who changed it;
• why it changed;
• from where it changed;
• which request/correlation caused it;
• what old/new values were;
• whether the change was user/system/migration/integration driven.

Example:

create table quote_state_history (
    quote_state_history_id uuid primary key,
    quote_id uuid not null,
    from_state text,
    to_state text not null,
    changed_at timestamptz not null default now(),
    changed_by text not null,
    change_source text not null,
    correlation_id text,
    reason_code text,
    notes text
);

For lifecycle systems, audit timestamp is not optional decoration.

It is part of operational defensibility.

14. Time-series table pattern

A time-series table stores many time-ordered facts.

Examples:

• status history;
• event processing log;
• metric samples;
• pricing history;
• audit trail;
• job execution log;
• integration call log;
• CDC checkpoint;
• order state transition history.

Basic pattern:

create table integration_call_log (
    integration_call_log_id uuid primary key,
    integration_name text not null,
    operation_name text not null,
    started_at timestamptz not null,
    completed_at timestamptz,
    status text not null,
    duration_ms integer,
    correlation_id text,
    error_code text,
    request_summary jsonb,
    response_summary jsonb
);

Common indexes:

create index idx_integration_call_log_started_at
on integration_call_log (started_at desc);

create index idx_integration_call_log_name_started
on integration_call_log (integration_name, started_at desc);

create index idx_integration_call_log_status_started
on integration_call_log (status, started_at desc);

Index choice depends on query pattern.

15. BRIN index for append-heavy time-series

BRIN indexes can be useful for large append-heavy tables where physical row order correlates with time.

Example:

create index idx_order_event_log_created_at_brin
on order_event_log using brin (created_at);

BRIN is compact and useful for large ranges.

It is not a replacement for B-tree indexes on highly selective point lookups.

Use BRIN when:

• table is huge;
• data is inserted roughly in time order;
• queries scan time ranges;
• storage overhead must be low;
• range filtering is common.

Use B-tree when:

• queries need exact lookup;
• ordering by time with small limit;
• composite predicates are selective;
• latest N per entity is frequent.

Example B-tree for latest status history per order:

create index idx_order_status_history_order_changed
on order_status_history (order_id, changed_at desc);

16. Partitioning by time

Large temporal tables often need partitioning.

Example:

create table order_event_log (
    order_event_log_id uuid not null,
    order_id uuid not null,
    event_type text not null,
    created_at timestamptz not null,
    payload jsonb not null,
    primary key (order_event_log_id, created_at)
) partition by range (created_at);

Monthly partition:

create table order_event_log_2026_07
partition of order_event_log
for values from ('2026-07-01') to ('2026-08-01');

Benefits:

• partition pruning;
• easier retention via detach/drop partition;
• smaller indexes per partition;
• maintenance isolation;
• archival by partition.

Costs:

• more operational complexity;
• partition management needed;
• primary/unique constraints must include partition key in many designs;
• bad partition key gives little benefit;
• query predicates must include partition key for pruning;
• too many partitions can hurt planning/maintenance.

Partitioning is an operational design, not merely performance magic.

17. Retention and archival

Temporal tables grow forever unless retention is explicit.

Retention questions:

• How long must data remain online?
• What is the legal/compliance retention period?
• What can be archived?
• What can be deleted?
• What must remain queryable by support?
• What must remain restorable?
• Is archived data needed for reports?
• Does deletion affect audit defensibility?
• Is data subject to privacy deletion?

Retention via partitions:

alter table order_event_log detach partition order_event_log_2024_01;

Then archive/drop according to policy.

Do not create retention jobs that delete millions of rows daily without understanding:

• lock impact;
• WAL generation;
• vacuum impact;
• replication lag;
• backup impact;
• query plan impact;
• audit/compliance requirements.

18. Rollup tables

For analytics over time-series data, rollup tables reduce repeated heavy aggregation.

Example:

create table daily_order_status_rollup (
    business_date date not null,
    tenant_id uuid not null,
    status text not null,
    order_count bigint not null,
    calculated_at timestamptz not null default now(),
    primary key (business_date, tenant_id, status)
);

Rollups are useful for:

• dashboards;
• operational reporting;
• SLA monitoring;
• trend analysis;
• reducing load on raw history tables.

Risks:

• stale data;
• double counting;
• late event handling;
• timezone boundary mistakes;
• rebuild complexity;
• reconciliation gaps.

A rollup table needs a refresh/rebuild strategy.

19. Materialized views for temporal reporting

Materialized views can support reporting where exact freshness is not required.

Example:

create materialized view order_daily_summary_mv as
select
    date_trunc('day', submitted_at) as utc_day,
    status,
    count(*) as order_count
from orders
group by 1, 2;

Potential index:

create unique index idx_order_daily_summary_mv
on order_daily_summary_mv (utc_day, status);

Refresh:

refresh materialized view concurrently order_daily_summary_mv;

Cautions:

• concurrent refresh has requirements;
• refresh may still be expensive;
• data is stale between refreshes;
• timezone-local reporting needs explicit conversion;
• operational schedule must avoid peak load.

20. Timezone bugs

Common timezone failures:

20.1 Storing local time as instant

System stores 2026-07-11 09:00:00 without timezone, later interprets as UTC.

20.2 Reporting day mismatch

UTC day differs from tenant local business day.

Example:

2026-07-10T18:30:00Z

This is July 11 in Asia/Jakarta.

Daily reports must define which day they mean.

20.3 Daylight saving transition

Some local times do not exist or occur twice in DST zones.

Even if Indonesia does not use DST, global enterprise systems may serve regions that do.

20.4 Database session timezone surprise

The same timestamptz renders differently depending on session timezone.

20.5 Java serialization mismatch

LocalDateTime is serialized without offset, then interpreted differently by client/server.

20.6 Date-only effective rules with hidden timezone

A price effective on 2026-07-01 needs a market/business calendar definition.

21. Clock abstraction for testing

Application code should not call system time everywhere.

Bad:

Instant now = Instant.now();

scattered across code.

Better:

class QuoteExpiryService {
    private final Clock clock;

    QuoteExpiryService(Clock clock) {
        this.clock = clock;
    }

    Instant now() {
        return Instant.now(clock);
    }
}

Benefits:

• deterministic tests;
• expiry logic testable;
• effective-date logic testable;
• boundary cases testable;
• daylight saving scenarios testable;
• retry/backoff logic testable.

For database defaults like now(), decide carefully whether time should be generated by:

• application clock;
• database clock;
• external event timestamp;
• upstream system timestamp.

Do not mix without reason.

22. Database time vs application time

now() in PostgreSQL returns transaction start time, not wall-clock time for every row operation inside the same transaction.

This is often good for consistency.

But understand the semantic.

If a long transaction inserts many rows with default now(), they may share the same timestamp.

That may be acceptable for audit transaction time.

If you need actual clock time at statement execution, PostgreSQL has other time functions, but use them intentionally.

In most business tables, default now() is fine for created_at.

But for event occurrence time, prefer explicit application/domain timestamp if the event happened before persistence.

23. Temporal lifecycle modelling

Lifecycle systems should often store state transition history.

Bad:

create table orders (
    order_id uuid primary key,
    status text not null,
    updated_at timestamptz not null
);

This loses history.

Better:

create table orders (
    order_id uuid primary key,
    status text not null,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table order_state_transition (
    order_state_transition_id uuid primary key,
    order_id uuid not null references orders(order_id),
    from_state text,
    to_state text not null,
    transitioned_at timestamptz not null default now(),
    transitioned_by text not null,
    reason_code text,
    correlation_id text
);

Now you can answer:

• how long did order stay in each state?
• who moved it?
• what process moved it?
• when did fulfilment start?
• where did SLA breach occur?
• which transition caused downstream event?

Temporal data is often the backbone of production debugging.

24. Quote expiry modelling

Quote expiry is a temporal business rule.

Possible fields:

create table quote (
    quote_id uuid primary key,
    status text not null,
    created_at timestamptz not null default now(),
    submitted_at timestamptz,
    approved_at timestamptz,
    expires_at timestamptz,
    expiry_policy_code text
);

Questions:

• Is expiry based on creation, submission, approval, or customer acceptance?
• Is expiry calculated in UTC or customer timezone?
• Can expiry be extended?
• Is expiry immutable once quote is issued?
• What happens to in-flight approval?
• Does order creation check quote expiry transactionally?
• Are expired quotes updated by batch job or computed dynamically?

Do not treat expiry as a UI-only concern.

It affects correctness.

25. Catalog and price versioning

CPQ systems often need versioned catalog/pricing.

A quote should usually reference or snapshot the version used at calculation time.

Example:

create table quote_item (
    quote_item_id uuid primary key,
    quote_id uuid not null,
    product_offering_id uuid not null,
    catalog_version_id uuid not null,
    price_version_id uuid not null,
    quantity integer not null,
    calculated_amount numeric(19, 4) not null,
    calculated_at timestamptz not null default now()
);

If quote calculation reads “current price” each time, historical quote correctness may break after price updates.

Temporal versioning is not optional in pricing systems.

It is core business defensibility.

26. Late-arriving data

Events and integrations may arrive late.

Example:

• order submitted at 10:00;
• event consumed at 10:15;
• reporting job ran at 10:05;
• dashboard shows wrong count until next correction.

Design questions:

• Are reports based on event time or processing time?
• Can rollups be corrected?
• Is there a lateness window?
• Are late events marked?
• Can materialized views be rebuilt?
• Is reconciliation available?

Late-arriving data matters in CDC/Kafka architectures.

Do not assume arrival order equals business order.

27. Temporal query patterns

27.1 Current valid record

select *
from price_version
where product_offering_id = #{productOfferingId}
  and effective_from <= #{businessDate}
  and (effective_to is null or effective_to > #{businessDate})
order by effective_from desc
limit 1;

27.2 History for entity

select *
from order_state_transition
where order_id = #{orderId}
order by transitioned_at asc;

27.3 Latest row per entity

PostgreSQL distinct on can be useful:

select distinct on (order_id)
    order_id,
    to_state,
    transitioned_at
from order_state_transition
order by order_id, transitioned_at desc;

Index:

create index idx_order_state_transition_latest
on order_state_transition (order_id, transitioned_at desc);

27.4 Time window query

select *
from integration_call_log
where started_at >= #{fromInstant}
  and started_at < #{toInstant}
order by started_at desc
limit 500;

Prefer half-open intervals:

[from, to)

Avoid between for timestamp ranges unless you really want inclusive endpoints.

28. Half-open interval discipline

Use half-open intervals for time windows.

where created_at >= :from
  and created_at < :to

This avoids boundary overlap between adjacent windows.

Bad:

where created_at between :startOfDay and :endOfDay

Problems:

• inclusive end;
• microsecond precision issues;
• duplicate rows across windows;
• missing rows if endOfDay is approximated as 23:59:59.

Better:

where created_at >= '2026-07-11T00:00:00Z'
  and created_at <  '2026-07-12T00:00:00Z'

For local business day, compute the UTC instants that bound that local day.

29. MyBatis temporal query concerns

MyBatis mappers should make time semantics explicit.

Bad mapper parameter:

String date

Better:

Instant fromInclusive;
Instant toExclusive;
LocalDate businessDate;
ZoneId businessZone;

Avoid formatting timestamp strings manually in SQL.

Use typed parameters.

Mapper example:

<select id="findEventsInWindow" resultMap="EventMap">
  select event_id, aggregate_id, event_type, occurred_at, payload
  from event_log
  where occurred_at >= #{fromInclusive}
    and occurred_at < #{toExclusive}
  order by occurred_at desc
  limit #{limit}
</select>

Do not hide timezone conversion inside arbitrary SQL snippets unless it is documented and tested.

30. Temporal indexes

Common index patterns:

Entity history

create index idx_quote_state_history_quote_time
on quote_state_history (quote_id, changed_at desc);

Recent records globally

create index idx_outbox_event_created_at
on outbox_event (created_at);

Status by time

create index idx_outbox_status_created
on outbox_event (status, created_at);

Tenant time window

create index idx_order_tenant_submitted
on orders (tenant_id, submitted_at desc);

Effective-dated lookup

create index idx_price_version_lookup
on price_version (product_offering_id, currency_code, effective_from desc);

Index order must follow query shape.

Do not create created_at indexes blindly on every table.

Ask what query they support.

31. Temporal observability

Temporal issues show up in observability as:

• event lag;
• processing delay;
• outbox publish delay;
• batch job duration;
• old unprocessed rows;
• late-arriving events;
• partition growth;
• retention backlog;
• long-running transactions;
• clock skew;
• report freshness delay.

Useful metrics:

now - oldest unprocessed outbox created_at
now - oldest pending job created_at
now - max(processed_at) for consumer
row count by day/partition
rows older than retention cutoff
materialized view last refresh time
replication lag

Temporal columns are often directly used in alerting.

Design them with operations in mind.

32. Temporal data and CDC/Kafka

When PostgreSQL changes are streamed through CDC, temporal semantics multiply.

Important timestamps:

• row business timestamp;
• row created_at/updated_at;
• transaction commit time;
• CDC connector processing time;
• Kafka broker append time;
• consumer processing time.

A downstream system may see an event late even if the original business event happened earlier.

For reconciliation, include enough timestamps in event payloads.

But do not pretend they all mean the same thing.

Event contracts should define timestamp semantics explicitly.

33. Data retention with CDC and replicas

Retention is not only a table concern.

Deleting old rows affects:

• logical replication;
• CDC stream volume;
• WAL generation;
• read replicas;
• downstream consumers;
• audit requirements;
• backups;
• materialized views;
• reports.

Large deletes can cause:

• WAL spikes;
• replication lag;
• vacuum pressure;
• index bloat;
• lock contention;
• downstream event floods.

Partition drop/detach is often cleaner for large time-based retention.

But this requires partitioning from the design stage.

34. Testing temporal logic

Test cases should cover:

• boundary start/end date;
• exclusive end;
• quote expiry boundary;
• effective price lookup;
• future-dated price;
• overlapping validity prevention;
• timezone conversion;
• daylight saving transition if applicable;
• late event handling;
• replayed event;
• retention cutoff;
• materialized view refresh;
• batch job idempotency;
• database default timestamp behaviour.

Testing only “today's date” is weak.

Temporal bugs live at boundaries.

35. Security and privacy concerns

Temporal data affects security and compliance.

Examples:

• retention policies depend on timestamps;
• audit evidence depends on trustworthy changed_at;
• privacy deletion may need time-based eligibility;
• access logs need accurate time;
• token/session expiry depends on clock correctness;
• exported reports may include timestamped sensitive events;
• legal hold may override retention deletion.

Do not allow application code to overwrite audit timestamps casually.

For sensitive audit data, control who can modify temporal columns.

36. Common anti-patterns

36.1 One timestamp for all meanings

updated_at is not a substitute for submitted, approved, expired, processed, or published time.

36.2 Local time stored without timezone

Ambiguous and dangerous for global systems.

36.3 Inclusive end timestamps

Causes overlapping windows and edge bugs.

36.4 Effective date without business calendar definition

A date needs a timezone/market/tenant interpretation.

36.5 No historical state table

You cannot debug lifecycle duration after the fact.

36.6 No retention plan

History tables grow until performance and storage become incidents.

36.7 Deleting history row-by-row at scale

Generates WAL/vacuum/replication pain.

36.8 Assuming event order from timestamps

Distributed systems do not guarantee strict timestamp ordering.

36.9 Using current price for historical quote

Breaks audit and customer defensibility.

36.10 Timezone conversion in random places

Make time conversion policy explicit and tested.

37. Production debugging queries

Oldest pending outbox event

select min(created_at) as oldest_pending,
       now() - min(created_at) as pending_age
from outbox_event
where status = 'PENDING';

Row count by day

select date_trunc('day', created_at) as day,
       count(*)
from order_event_log
group by 1
order by 1 desc;

State duration approximation

select
    order_id,
    from_state,
    to_state,
    transitioned_at,
    lead(transitioned_at) over (
        partition by order_id
        order by transitioned_at
    ) as next_transitioned_at
from order_state_transition
where order_id = #{orderId}
order by transitioned_at;

Potential expired active quotes

select quote_id, status, expires_at
from quote
where status in ('ISSUED', 'APPROVED')
  and expires_at < now()
order by expires_at asc
limit 100;

Effective-date overlaps concept check

select a.price_version_id, b.price_version_id
from price_version a
join price_version b
  on a.product_offering_id = b.product_offering_id
 and a.currency_code = b.currency_code
 and a.price_version_id <> b.price_version_id
 and a.effective_from < coalesce(b.effective_to, '9999-12-31'::date)
 and b.effective_from < coalesce(a.effective_to, '9999-12-31'::date);

This is a diagnostic query, not necessarily the best constraint design.

38. Temporal PR review checklist

When reviewing temporal changes, ask:

Semantics

• What kind of time is this column modelling?
• Is it an instant, business date, local time, duration, or validity range?
• Is timezone meaning explicit?
• Is date-only logic tied to a business calendar?

PostgreSQL type

• Should this be timestamptz, date, interval, or something else?
• Is timestamp without time zone justified?
• Are defaults appropriate?
• Is now() semantics acceptable?

Java/JAX-RS

• Is Java type correct: Instant, OffsetDateTime, LocalDate, or LocalDateTime?
• Is API serialization unambiguous?
• Is timezone conversion done at boundary?
• Is Clock injectable for tests?

MyBatis/JDBC

• Are typed parameters used instead of string formatting?
• Are range queries half-open?
• Are mapper tests covering boundaries?
• Is timestamp precision handled safely?

Correctness

• Are overlapping effective ranges prevented?
• Is expiry enforced transactionally where needed?
• Is historical snapshot preserved?
• Is audit history append-only enough?
• Are late events handled?

Performance

• Are time-window queries indexed?
• Is partitioning needed?
• Would BRIN help?
• Are retention jobs safe?
• Are materialized views/rollups needed?

Operations

• Are temporal metrics observable?
• Can support debug by correlation/time window?
• Is retention/archival defined?
• Are old partitions maintained?
• Are slow temporal queries monitored?

Security/privacy

• Does this timestamp drive retention/deletion?
• Can audit timestamps be tampered with?
• Are timestamped logs/exported data sensitive?
• Is legal hold/compliance impact considered?

39. Internal verification checklist

Do not assume CSG implementation details. Verify.

Schema

• Which tables use timestamptz, timestamp, date, and interval?
• Are any instant-like fields stored as timestamp without time zone?
• Are created_at and updated_at conventions consistent?
• Are audit/history tables present for quote/order lifecycle?
• Are effective-dated catalog/pricing tables present?

Java/JAX-RS

• Which Java types map temporal columns?
• Are APIs returning timestamps with timezone/offset?
• Are request timestamps validated clearly?
• Is there a centralized clock abstraction?
• Are timezone conversions centralized or scattered?

MyBatis/JDBC

• Are temporal parameters passed as typed values?
• Are mapper queries using half-open intervals?
• Are date/time casts embedded in SQL?
• Are temporal query indexes aligned with mapper predicates?
• Are tests covering temporal boundaries?

CPQ/order domain

• How is quote expiry modelled?
• How is price/catalog effective dating modelled?
• Are quote snapshots immutable?
• Are order state transitions stored historically?
• Are SLA/fulfilment deadlines stored and observable?

Event-driven/CDC

• Do events distinguish occurred_at, created_at, published_at, processed_at?
• Does CDC/outbox preserve ordering expectations?
• Are late events handled?
• Are replay/reconciliation windows defined?
• Are event timestamps documented in schema contracts?

Operations

• Which time-series/history tables grow fastest?
• Is partitioning used?
• Is BRIN used on append-heavy tables?
• Are retention/archival jobs defined?
• Are materialized views/rollups refreshed safely?
• Are dashboards tracking pending age, lag, partition growth, and retention backlog?

Cloud/Kubernetes/on-prem

• Is database timezone configured deliberately?
• Are application pods configured consistently for timezone?
• Are logs emitted in UTC or with offset?
• Are nodes synchronized via NTP/time sync?
• Are cloud database/session timezone differences documented?

40. Failure-mode table

41. Practical decision framework

Use this temporal modelling sequence:

1. Identify the time concept.
   Is it instant, business date, local time, validity range, duration, or processing timestamp?

2. Choose PostgreSQL type.
   Most instants -> timestamptz.
   Business dates -> date.
   Local date-times -> timestamp + timezone context.

3. Define timezone policy.
   UTC for instants; explicit business timezone for dates/reports.

4. Define lifecycle semantics.
   Is it mutable, immutable, append-only, effective-dated, or audit-only?

5. Define query pattern.
   Latest per entity, current valid row, history by entity, time-window scan, rollup.

6. Design indexes/partitions.
   Composite B-tree, BRIN, partitioning, materialized view, rollup.

7. Define retention.
   Online duration, archive, delete, compliance, restore.

8. Add observability.
   Lag, age, growth, stale views, old pending rows.

9. Test boundaries.
   Timezone, DST, exclusive end, expiry, late arrival, overlap.

Do not add timestamp columns mechanically.

Model temporal semantics deliberately.

42. Summary

Temporal data is not a helper detail.

It is a correctness dimension.

For enterprise PostgreSQL-backed Java/JAX-RS systems:

• use timestamptz for instants;
• use date for business calendar concepts;
• avoid ambiguous timestamp without time zone for instants;
• define UTC and local business timezone policy;
• model effective dates with explicit validity semantics;
• prefer half-open intervals;
• preserve lifecycle history;
• snapshot catalog/price data where historical correctness matters;
• distinguish event time, processing time, and commit/publish time;
• index temporal queries based on real access patterns;
• partition large time-series/history tables when retention and scale justify it;
• design retention and archival before the table becomes huge;
• test temporal boundaries with an injectable clock;
• make temporal observability part of the data model.

Time bugs are often invisible until audit, billing, expiry, reporting, reconciliation, or customer disputes expose them.

Senior engineers prevent those bugs by making time semantics explicit at schema, application, API, event, and operational layers.