Date, Time, and Time-Zone Test Data for SQL

Date, Time, and Time‑Zone Test Data for SQL Applications

Working with temporal data is one of the most error‑prone parts of any relational system. A single off‑by‑one day, an ambiguous timestamp, or a hidden daylight‑saving transition can turn a passing test suite into a production incident. This guide walks through the decisions you need to make, a repeatable workflow for generating realistic date‑time data, a worked example you can copy into a CI pipeline, common pitfalls, and concrete next steps for your team.

1. Why Temporal Test Data Is Different

Characteristic	Typical scalar data	Temporal data
Domain size	Often bounded (e.g., 0‑100)	Practically unbounded (years, centuries)
Semantic rules	Simple constraints (NOT NULL, CHECK)	Calendar rules, leap years, DST, offset changes
Storage variety	One or two types (INT, VARCHAR)	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`, `TIMESTAMPTZ`, `INTERVAL`
Cross‑system behavior	Mostly identical	Drivers, ORMs, and DB engines each have their own parsing/serialization quirks

Because of these differences, a “generate 100 random rows” approach rarely surfaces the bugs that matter: boundary‑of‑month calculations, offset‑aware comparisons, and migration scripts that assume a single time‑zone.

2. Decision Criteria Before You Generate

Before you fire up a generator, answer the following questions. The answers shape the data shape, volume, and validation strategy.

Question	Why it matters	Typical answer for a SaaS product
Which SQL dialect(s) are targeted?	`TIMESTAMP WITH TIME ZONE` exists in PostgreSQL, Oracle, SQL Server (as `DATETIMEOFFSET`), but not in MySQL 5.7.	PostgreSQL 14+, SQL Server 2019, MySQL 8.0
What is the application’s canonical time‑zone?	All business logic should normalize to a single zone (usually UTC).	UTC
*Which columns are business* dates vs. technical timestamps?**	Business dates (e.g., `invoice_date`) often ignore time‑of‑day; technical columns (`created_at`) need full precision.	`invoice_date` → `DATE`; `created_at` → `TIMESTAMPTZ`
Do you need historical DST transitions?	Tests that cross a DST boundary expose bugs in `AT TIME ZONE` logic.	Yes – at least one spring‑forward and one fall‑back per year.
What is the acceptable data volume for CI?	Large data sets slow pipelines; small sets miss edge cases.	5 k rows per table for unit tests, 200 k for integration.
Are there regulatory constraints (e.g., GDPR, PCI)?	Synthetic data must not resemble real PII.	No real emails, names, or credit‑card numbers.

Document the answers in a Temporal Data Profile (a one‑page markdown file) and version it with the schema. It becomes the contract between QA, developers, and DBAs.

3. Generation Workflow

Below is a repeatable, CI‑friendly workflow. Each step can be scripted; the only manual artifact is the Temporal Data Profile.

3.1. Define the generation spec

Create a JSON/YAML file that the generator can consume. Example (temporal-spec.yaml):

tables:
  - name: orders
    columns:
      - name: order_id
        type: serial
      - name: customer_id
        type: int
        fk: customers.customer_id
      - name: order_date          # business date, no time
        type: date
        range:
          start: "2018-01-01"
          end:   "2024-12-31"
        distribution: uniform
      - name: placed_at           # technical timestamp, UTC
        type: timestamptz
        range:
          start: "2018-01-01T00:00:00Z"
          end:   "2024-12-31T23:59:59Z"
        distribution: weighted   # more recent orders
        weight_curve: exponential
      - name: shipped_at          # nullable, may be in future
        type: timestamptz
        nullable: true
        range:
          start: "2018-01-01T00:00:00Z"
          end:   "2025-06-30T23:59:59Z"
        distribution: uniform
    row_count: 50000

Why a spec file?

It lives next to migrations, so schema changes force a spec update.
It can be validated by a tiny script before generation runs.

3.2. Choose a generation engine

Engine	Strengths	Weaknesses	Good fit for
QA3 free test data generator (`/tools/test-data-generator`)	Web UI + API, supports custom distributions, DST‑aware timestamps, exports SQL/CSV/Parquet	No native DB‑specific type mapping (you map in spec)	Teams that want a zero‑install, shareable spec
pg_generate_data (PostgreSQL extension)	Runs inside the DB, knows native types, can use `generate_series`	PostgreSQL only	Pure‑Postgres shops
dbt‑seed + Python scripts	Version‑controlled, integrates with dbt tests	Requires Python runtime	dbt‑centric analytics pipelines
Custom Go/Java utility	Full control, can embed business rules	Maintenance burden	Highly regulated environments

Pick one and lock the version in your CI container image. Switching engines mid‑project re‑introduces drift.

3.3. Run generation in CI



# .github/workflows/temporal-data.yml


name: Generate temporal test data
on:
  push:
    paths:
      - 'db/migrations/**'
      - 'qa/temporal-spec.yaml'
jobs:
  generate:
    runs-on: ubuntu-latest
    container:
      image: qa3io/test-data-generator:1.4.0   # pinned version
    steps:
      - uses: actions/checkout@v4
      - name: Generate SQL seed file
        run: |
          qa3-gen \
            --spec qa/temporal-spec.yaml \
            --dialect postgresql \
            --output qa/seeds/orders_seed.sql
      - name: Upload seed as artifact
        uses: actions/upload-artifact@v4
        with:
          name: orders-seed
          path: qa/seeds/orders_seed.sql

The generated orders_seed.sql is then consumed by the integration‑test job:

  integration:
    needs: generate
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_PASSWORD: pwd
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      - name: Download seed
        uses: actions/download-artifact@v4
        with:
          name: orders-seed
          path: qa/seeds
      - name: Load schema + seed
        run: |
          psql -h localhost -U postgres -d testdb -f db/schema.sql
          psql -h localhost -U postgres -d testdb -f qa/seeds/orders_seed.sql
      - name: Run integration tests
        run: ./gradlew integrationTest

3.4. Validate the generated data

Automated validation catches spec‑drift early. Run a validation suite immediately after load:

-- qa/validation/temporal_checks.sql
-- 1. No NULL where NOT NULL declared
SELECT 'order_date nulls' AS check, COUNT(*) FROM orders WHERE order_date IS NULL;


-- 2. All placed_at values are UTC (offset = +00)
SELECT 'placed_at offset' AS check, COUNT(*)
FROM orders
WHERE placed_at AT TIME ZONE 'UTC' <> placed_at;


-- 3. DST boundary coverage (at least one row per transition year)
WITH transitions AS (
  SELECT generate_series('2018-03-01'::date, '2024-11-01'::date, '1 year')::date AS yr
)
SELECT 'spring_forward_missing' AS check, COUNT(*)
FROM transitions t
LEFT JOIN orders o
  ON date_trunc('day', o.placed_at AT TIME ZONE 'America/New_York') = t.yr + interval '2 weeks'  -- 2nd Sunday Mar
WHERE o.order_id IS NULL;

Fail the pipeline if any check returns a non‑zero count.

4. Worked Example: Orders Table Across Three Time‑Zones

Assume a global e‑commerce platform that stores business dates in the merchant’s local zone but records technical timestamps in UTC. The test must verify:

order_date = placed_at converted to merchant zone, truncated to day.
shipped_at may be null or a future UTC timestamp.
Reports that group by order_date are correct for merchants in America/Los_Angeles, Europe/Berlin, Asia/Tokyo.

4.1. Schema (PostgreSQL)

CREATE TABLE merchants (
  merchant_id   SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  tz            TEXT NOT NULL CHECK (tz ~ '^[A-Za-z]+/[A-Za-z_]+$')
);


CREATE TABLE orders (
  order_id      BIGSERIAL PRIMARY KEY,
  merchant_id   INT NOT NULL REFERENCES merchants(merchant_id),
  order_date    DATE NOT NULL,                -- business date in merchant tz
  placed_at     TIMESTAMPTZ NOT NULL,         -- UTC
  shipped_at    TIMESTAMPTZ                   -- nullable, UTC
);

4.2. Spec snippet for the three merchants

tables:
  - name: merchants
    columns:
      - name: merchant_id
        type: serial
      - name: name
        type: varchar(50)
        values: ["WestCoast Shop", "Euro Goods", "Tokyo Traders"]
      - name: tz
        type: varchar(50)
        values: ["America/Los_Angeles", "Europe/Berlin", "Asia/Tokyo"]
    row_count: 3
  - name: orders
    columns:
      - name: order_id
        type: bigserial
      - name: merchant_id
        type: int
        fk: merchants.merchant_id
      - name: order_date
        type: date
        range:
          start: "2022-01-01"
          end:   "2023-12-31"
        distribution: uniform
      - name: placed_at
        type: timestamptz
        # Derived from order_date + random hour/minute in merchant tz, then cast to UTC
        derived_from: order_date
        derivation: |
          SELECT (order_date + (random() * interval '24 hours')) AT TIME ZONE m.tz AT TIME ZONE 'UTC'
          FROM merchants m WHERE m.merchant_id = orders.merchant_id
      - name: shipped_at
        type: timestamptz
        nullable: true
        range:
          start: "2022-01-01T00:00:00Z"
          end:   "2024-06-30T23:59:59Z"
        distribution: uniform
    row_count: 15000

Note – The derived_from block is pseudo‑code; the QA3 generator evaluates it per row using the referenced merchants table, guaranteeing the business‑date ↔ UTC relationship holds automatically.

4.3. Validation queries for the example

-- 1. Business date matches UTC conversion
SELECT COUNT(*) AS mismatches
FROM orders o
JOIN merchants m ON m.merchant_id = o.merchant_id
WHERE o.order_date <> (o.placed_at AT TIME ZONE m.tz)::date;


-- 2. At least one order per DST transition per merchant per year
WITH dst_dates AS (
  SELECT
    m.merchant_id,
    generate_series('2022-01-01'::date, '2023-12-31'::date, '1 year')::date AS yr
  FROM merchants m
),
spring AS (
  SELECT merchant_id, yr + interval '2 weeks' + interval '1 day' AS dt  -- 2nd Sunday Mar (approx)
  FROM dst_dates
),
fall AS (
  SELECT merchant_id, yr + interval '44 weeks' + interval '1 day' AS dt  -- 1st Sunday Nov (approx)
  FROM dst_dates
)
SELECT 'spring_missing' AS check, COUNT(*)
FROM spring s
LEFT JOIN orders o
  ON o.merchant_id = s.merchant_id
 AND (o.placed_at AT TIME ZONE (SELECT tz FROM merchants WHERE merchant_id = s.merchant_id))::date = s.dt::date
WHERE o.order_id IS NULL
UNION ALL
SELECT 'fall_missing', COUNT(*)
FROM fall f
LEFT JOIN orders o
  ON o.merchant_id = f.merchant_id
 AND (o.placed_at AT TIME ZONE (SELECT tz FROM merchants WHERE merchant_id = f.merchant_id))::date = f.dt::date
WHERE o.order_id IS NULL;

If the pipeline passes, you have high confidence that any query grouping by order_date will behave identically for all three zones.

4.4. Sample test case (JUnit / TestNG style)

@Test
void orderDateMatchesPlacedAtInMerchantZone() {
    try (Connection c = dataSource.getConnection();
         PreparedStatement ps = c.prepareStatement(
             "SELECT order_id FROM orders o " +
             "JOIN merchants m ON m.merchant_id = o.merchant_id " +
             "WHERE o.order_date <> (o.placed_at AT TIME ZONE m.tz)::date")) {
        ResultSet rs = ps.executeQuery();
        Assert.assertFalse(rs.next(), "Found mismatched order_date / placed_at");
    }
}

The test runs against the seeded CI database; no mocking required.

5. Common Pitfalls & Mitigations

Pitfall	Symptom	Root Cause	Mitigation
Assuming `TIMESTAMP` = UTC	`SELECT * FROM events WHERE created_at > now()` returns rows from the future.	`TIMESTAMP` without time zone stores the literal wall‑clock value; the driver may apply the session zone on read.	Use `TIMESTAMPTZ` (or `DATETIMEOFFSET`) for all technical columns. Document the convention in the Temporal Data Profile.
Generating only “nice” dates (e.g., 1st of month)	Month‑end billing jobs never exercised.	Uniform distribution over a range often clusters on boundaries if the range is small.	Explicitly add edge‑case rows: last day of February (leap & non‑leap), DST transition days, year‑end.
Ignoring `NULL` semantics for future timestamps	`shipped_at` always populated → shipping‑logic tests never hit the “not yet shipped” branch.	Generator defaulted `nullable: false`.	Set `nullable: true` and give a realistic `null_ratio` (e.g., 0.15).
Time‑zone data out of date	Tests pass locally but fail on a server that has a newer `tzdata` package.	IANA tz database updates (e.g., 2023‑c) change historic offsets.	Pin the `tzdata` version in the CI container (`apt-get install -y tzdata=2024a-1`). Run a nightly job that regenerates data against the latest tzdata and compares checksums.
Cross‑dialect drift	PostgreSQL seed works; SQL Server integration test fails on `AT TIME ZONE`.	`AT TIME ZONE` syntax differs; SQL Server requires `SWITCHOFFSET`.	Keep a dialect‑specific validation script per target DB. The spec file stays dialect‑agnostic; the generator emits dialect; the generator emits dialect‑specific SQL.
Data volume explosion in CI	Pipeline exceeds 30 min due to 2 M rows.	Row count set for production‑scale load test, not unit test.	Define profiles: `ci-unit` (5 k rows), `ci-integration` (20

Date, Time, and Time-Zone Test Data for SQL Applications