Align Postgres API timestamp grammar and detail-selector precision

Postgres timestamp parameters reject documented RFC 3339 values and response round-trips

Blocked on: Cloud API/OpenAPI owners aligning timestamp grammar and precision, and defining the detail timestamp selector.

Problem

The Postgres metrics and slow-query APIs document their timestamp inputs as RFC 3339 date-time strings, but the live API rejects conforming whole-second timestamps and equivalent numeric-offset timestamps. The slow-query detail API also rejects an execution timestamp returned by that same API when the response contains more than three fractional-second digits.

This blocks the CLI from faithfully passing through the documented API contract. The current PR stack can work around some cases by rewriting inputs to UTC with exactly three fractional digits, but doing that for the detail endpoint would discard response precision before the meaning of the timestamp selector is established.

Fresh read-only probes were run against an existing ClickHouse Cloud Postgres service on 2026-09-08 from 20:52 to 20:55 UTC. No service or configuration was changed, and no query text is included below.

Current published contract

The live OpenAPI document was retrieved from https://api.clickhouse.cloud/v1 at 2026-09-08T19:50:35.348403+00:00 (response Date: Tue, 08 Sep 2026 19:49:37 GMT).

• OpenAPI version: 3.1.2
• API info version: 1.0
• SHA-256: c4811d18b26701aa61794b1d6ea1fa1b375493776bdac2c2033383849ba047d9
• The retrieved document is structurally identical to the spec vendored at clickhousectl commit 4c17bc55.

Relevant JSON pointers and declarations:

The same declarations can be seen at slow-query list lines 14727–14743, slow-query detail lines 15001–15006, the response timestamp at lines 33264–33269, and metrics lines 14581–14596.

The OpenAPI format registry defines date-time by RFC 3339. RFC 3339 section 5.6 permits an optional fractional part of one or more digits and either Z or a numeric UTC offset in the timezone production. Therefore all of these inputs conform to the documented syntax:

• 2026-09-08T18:30:00Z
• 2026-09-08T18:30:00.000Z
• 2026-09-08T19:30:00.000+01:00
• 2026-09-08T19:53:58.9183Z

The spec supplies no pattern, example, fixed precision, or UTC-only restriction. OpenAPI formats can be treated as annotations by tooling, but the endpoint descriptions also explicitly promise RFC 3339, and the detail endpoint cannot accept its own documented response value.

Fresh direct-HTTP results

The paired list and metrics requests used identical time windows; only timestamp spelling changed.

The detail result shows two problems at the contract boundary:

1. The response serializer emits four fractional digits, while the request parser rejects that exact value.
2. A millisecond-shortened value is accepted, but the response contains ten recent executions. It is therefore unclear whether timestamp is an exact execution selector, as its description suggests, or a cursor/reference time.

Reproduction

Choose an existing Postgres service with slow-query data. The dates below are the exact captured window; for a later rerun, replace them with a recent window while preserving the equivalent instants across Z, .000Z, and +01:00 forms. All calls are read-only.

This script keeps credentials out of command-line arguments and files. It builds curl's Basic authorization header from environment variables in memory, passes the curl config over stdin, URL-encodes query values, stores response bodies in a mode-0600 temporary file, and emits only sanitized fields.

set -eu
set +x

: "${CLICKHOUSE_CLOUD_API_KEY:?set CLICKHOUSE_CLOUD_API_KEY}"
: "${CLICKHOUSE_CLOUD_API_SECRET:?set CLICKHOUSE_CLOUD_API_SECRET}"
: "${ORGANIZATION_ID:?set ORGANIZATION_ID}"
: "${POSTGRES_ID:?set POSTGRES_ID}"

API_ROOT="${CLICKHOUSE_CLOUD_API_URL:-https://api.clickhouse.cloud/v1}"
POSTGRES_URL="${API_ROOT}/organizations/${ORGANIZATION_ID}/postgres/${POSTGRES_ID}"
umask 077
BODY="$(mktemp)"
trap 'rm -f "$BODY"' EXIT

auth_config() {
  python3 - <<'PY'
import base64
import os

raw = (os.environ["CLICKHOUSE_CLOUD_API_KEY"] + ":" +
       os.environ["CLICKHOUSE_CLOUD_API_SECRET"]).encode()
token = base64.b64encode(raw).decode("ascii")
print(f'header = "Authorization: Basic {token}"')
print("silent")
print("show-error")
PY
}

request() {
  auth_config | curl --config - --get --output "$BODY" \
    --write-out '%{http_code}' "$@"
}

# Documented whole-second RFC 3339 form: observed HTTP 400.
CODE="$(request "${POSTGRES_URL}/slowQueryPatterns" \
  --data-urlencode 'from_date=2026-09-08T18:30:00Z' \
  --data-urlencode 'to_date=2026-09-08T19:30:00Z' \
  --data-urlencode 'limit=1')"
printf 'HTTP %s\n' "$CODE"
jq '{status,error}' "$BODY"

# Identical window with exactly milliseconds: observed HTTP 200.
CODE="$(request "${POSTGRES_URL}/slowQueryPatterns" \
  --data-urlencode 'from_date=2026-09-08T18:30:00.000Z' \
  --data-urlencode 'to_date=2026-09-08T19:30:00.000Z' \
  --data-urlencode 'limit=1')"
printf 'HTTP %s\n' "$CODE"
jq '{status,result_type:(.result|type),result_count:(.result|length)}' "$BODY"

# Equivalent instants with an RFC 3339 numeric offset: observed HTTP 400.
CODE="$(request "${POSTGRES_URL}/slowQueryPatterns" \
  --data-urlencode 'from_date=2026-09-08T19:30:00.000+01:00' \
  --data-urlencode 'to_date=2026-09-08T20:30:00.000+01:00' \
  --data-urlencode 'limit=1')"
printf 'HTTP %s\n' "$CODE"
jq '{status,error}' "$BODY"

# Metrics shows the same whole-second rejection.
CODE="$(request "${POSTGRES_URL}/metrics" \
  --data-urlencode 'from_date=2026-09-08T18:30:00Z' \
  --data-urlencode 'to_date=2026-09-08T19:30:00Z' \
  --data-urlencode 'bucket_size_seconds=300')"
printf 'HTTP %s\n' "$CODE"
jq '{status,error}' "$BODY"

# Metrics comparison: identical interval with milliseconds, observed HTTP 200.
CODE="$(request "${POSTGRES_URL}/metrics" \
  --data-urlencode 'from_date=2026-09-08T18:30:00.000Z' \
  --data-urlencode 'to_date=2026-09-08T19:30:00.000Z' \
  --data-urlencode 'bucket_size_seconds=300')"
printf 'HTTP %s\n' "$CODE"
jq '{status,metric_count:(.result.metrics|length)}' "$BODY"

For the response round-trip, append the following block to the same script; it reuses the helper and temporary file above. Supply a pattern ID and its required database/user/operation filters from a populated list result. Keep those identifiers local and do not print queryText; extract only the first response timestamp:

: "${QUERY_PATTERN_ID:?set QUERY_PATTERN_ID}"
: "${DB_NAME:?set DB_NAME}"
: "${DB_USER:?set DB_USER}"
: "${DB_OPERATION:?set DB_OPERATION}"

DETAIL_URL="${POSTGRES_URL}/slowQueryPatterns/${QUERY_PATTERN_ID}"
CODE="$(request "$DETAIL_URL" \
  --data-urlencode "db_name=${DB_NAME}" \
  --data-urlencode "db_user=${DB_USER}" \
  --data-urlencode "db_operation=${DB_OPERATION}")"
printf 'HTTP %s\n' "$CODE"
EXECUTION_TIMESTAMP="$(jq -r '.result.recentExecutions[0].timestamp' "$BODY")"
printf 'timestamp=%s\n' "$EXECUTION_TIMESTAMP"

# Pass the response timestamp back without modification.
CODE="$(request "$DETAIL_URL" \
  --data-urlencode "db_name=${DB_NAME}" \
  --data-urlencode "db_user=${DB_USER}" \
  --data-urlencode "db_operation=${DB_OPERATION}" \
  --data-urlencode "timestamp=${EXECUTION_TIMESTAMP}")"
printf 'HTTP %s\n' "$CODE"
jq '{status,error}' "$BODY"

In the fresh probe, the first detail call returned timestamp=2026-09-08T19:53:58.9183Z; passing it back unchanged returned HTTP 400 with the body shown in the table.

Expected contract

One of these contract-level resolutions is needed:

1. Accept every timestamp form currently documented as RFC 3339, including whole seconds, fractional seconds of arbitrary precision, numeric offsets, and timestamps emitted by the API itself; or
2. If the API intentionally accepts only UTC with exactly millisecond precision, document and encode that narrower grammar explicitly, return response timestamps in the same grammar, and define how clients should handle precision loss.

For the detail endpoint, also define whether timestamp selects one exact execution or establishes a reference point/window. If it is exact, an accepted request should identify the selected execution and the server must define matching behavior when stored precision exceeds accepted request precision.

Upstream questions and unblock criteria

• Is the fixed yyyy-MM-dd'T'HH:mm:ss.SSS'Z' parser intentional for all Postgres date parameters?
• Should whole-second and numeric-offset RFC 3339 inputs be accepted, or should the OpenAPI contract be narrowed?
• What is the supported fractional precision, and why can detail responses currently emit precision that detail requests reject?
• Does detail timestamp select an exact execution, act as a cursor, or only influence the recent-execution window?

The CLI is unblocked from pass-through behavior when the live implementation and OpenAPI document agree on accepted timestamp grammar and precision, response timestamps round-trip into the corresponding request, and detail selector semantics are documented and covered by API contract tests.

Relationship to existing clickhousectl issues

• clickhousectl #761 tracks the CLI-facing metrics failure. The PR stack works around it by normalizing metrics input to UTC with exactly three fractional digits and rejecting nonzero sub-millisecond precision. The fresh HTTP pair above confirms the upstream behavior still exists; this issue tracks the remaining API/spec contract rather than relabeling #761.
• clickhousectl #758 tracks fractional duration values being deserialized as integers. That client fix and the remaining OpenAPI duration-schema mismatch are separate from timestamp parsing and should not be conflated with this issue.

The remaining upstream duration-schema correction is tracked separately in #796.