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

Abierto
  • ¿Algo no está bien?
#661 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
68/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
clickhouse, typescript
Área
data-visualization, databases

Línea de trabajo

Read packages/core/src/modules/report/src/chart-dates.ts and packages/core/src/modules/chart/src/sql.ts, focusing on normalizeBoundary(), dateRangeWhere(), and intervalBucket(). Trace how custom date boundaries reach the WHERE filters and WITH FILL bounds, then inspect nearby tests for coverage. Done when supported inputs produce consistently normalized bounds and regression coverage checks the generated fill parameters.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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"
}
  1. 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.

Lenguaje dominante
TypeScript
Estrellas
7.3k
Forks
539
Merge medio
1 d 20 h
PR fusionados (30 d)
80

Preparar el entorno

  • Incluye un Dockerfile o un archivo de Docker Compose
  • Sin plantilla de pull request
  • Sin guía de contribución

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de Openpanel-dev/openpanel

Todos los issues de Openpanel-dev/openpanel

Issues similares

Más issues de TypeScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.