Bug: ISO timestamps are normalized in chart WHERE filters but not WITH FILL bounds #661

Summary

Time-series chart queries handle the same custom date range differently in two parts of the generated SQL. The WHERE filters normalize ISO timestamps to ClickHouse date strings, while WITH FILL passes the original ISO strings to toDateTime().

For an input such as 2025-01-15T00:00:00.000Z, this leaves a fractional ISO timestamp in a toDateTime(String) expression and can cause ClickHouse to reject the chart query with Code 6 (CANNOT_PARSE_TEXT).

All dates and identifiers below are synthetic. No application data is required for the SQL example.

Version checked

• Repository: Openpanel-dev/openpanel
• Branch: main
• Commit: a5a6eb7d62c9579620ec5e3fbfd4ce760f3d8073
• Verification: source inspection and execution of the current date-normalization and SQL-fragment helpers in memory. A full application/database test was not run for this commit.

Steps to reproduce

1. Use a valid chart request for a project in a local development environment.
2. Set these custom range fields, keeping the remaining request fields valid:

{
  "range": "custom",
  "interval": "minute",
  "startDate": "2025-01-15T00:00:00.000Z",
  "endDate": "2025-01-15T01:00:00.000Z"
}

3. Inspect the generated SQL and bound parameters, then execute the query against ClickHouse.

The relevant generated expressions are equivalent to:

-- WHERE: normalized
created_at >= toDateTime('2025-01-15 00:00:00')

-- WITH FILL: original ISO strings
WITH FILL
FROM toStartOfMinute(toDateTime('2025-01-15T00:00:00.000Z'))
TO toStartOfMinute(toDateTime('2025-01-15T01:00:00.000Z'))
STEP toIntervalMinute(1)

In the current implementation these values are bound as String parameters; parameter binding does not normalize their date format.

Minimal SQL example

This isolates the generated fill expression without accessing application tables:

SELECT
  toStartOfMinute(toDateTime('2025-01-15 00:00:00')) AS date,
  count() AS count
FROM numbers(1)
GROUP BY date
ORDER BY date WITH FILL
  FROM toStartOfMinute(toDateTime('2025-01-15T00:00:00.000Z'))
  TO toStartOfMinute(toDateTime('2025-01-15T01:00:00.000Z'))
  STEP toIntervalMinute(1);

Replacing only the fill bounds with 2025-01-15 00:00:00 and 2025-01-15 01:00:00 provides a comparison with consistent formatting. The example is provided for database reproduction; the verification performed on the commit above was limited to helper execution and generated parameters.

Expected behavior

A supported custom date range should be normalized consistently for filtering and gap filling, and the chart query should succeed. Unsupported date formats should be rejected before issuing a database query.

Actual behavior

The generated WHERE parameter is 2025-01-15 00:00:00, but the generated fill parameter remains 2025-01-15T00:00:00.000Z. The latter can produce a ClickHouse date-parsing error instead of a chart result.

Local helper execution confirmed the same unnormalized String bounds for minute, hour, day, week, and month. This confirms SQL generation across those intervals, not five end-to-end database tests.

Relevant source

• Custom date normalization: normalizeBoundary() expands date-only inputs but returns full timestamp strings unchanged.
• WHERE date normalization: dateRangeWhere() applies formatClickhouseDate().
• Fill bounds: intervalBucket() passes the original bounds through sql.string() into toDateTime().
• Fill construction: chartSeriesQuery() forwards startDate and endDate to the bucket's fill function.

Suggested fix and regression coverage

Normalize custom date boundaries consistently before building both WHERE and WITH FILL, preserving the intended timezone and range semantics. Alternatively, use a consistent typed date-binding/parsing approach for both clauses.

Add regression coverage for ISO timestamps with milliseconds and Z, explicit timezone offsets, date-only inputs, and existing ClickHouse-style date strings. Include a database-backed test for the generated WITH FILL query, in addition to SQL-parameter assertions.