ClickHouse / ClickHouse/clickhouse-js
`Date` objects with non-zero milliseconds produce fractional timestamps rejected by `{param:DateTime}`
- Dominant language
- TypeScript
- Stars
- 331
- Forks
- 74
- PR merge metrics
- No merged PRs in 30d
Description
### Describe the bug
When `Date` objects' milliseconds is not zero, its serialization (https://github.com/ClickHouse/clickhouse-js/blob/22e8610f8731bc7dfd24eeb9c08de1b2a17b0c94/packages/client-common/src/data_formatter/format_query_params.ts#L85) produces fractional timestamps (e.g `1773935682.595`), which are rejected by clickhouse service if the query param is of type `DateTime`, with the following error: `Error: Value 1773935682.595 cannot be parsed as DateTime for query parameter 'startDate' because it isn't parsed completely: only 10 of 14 bytes was parsed: 1773935682`.
The workaround is to use parameter type `DateTime64(3)` in the query.
The official example at `examples/query_with_parameter_binding.ts` comments `// or a Date object` on the `{var_datetime: DateTime}` param, but this only works when `date.getUTCMilliseconds() === 0`. The client's `formatQueryParams` appends `.XXX` for non-zero ms, which `DateTime` rejects.
**Note**: using `date_time_input_format: "best_effort"` does not fix this.
### Steps to reproduce
```typescript
import { createClient } from '@clickhouse/client';
const client = createClient({ url: 'http://localhost:8123' });
const date = new Date('2025-03-19T10:30:00.500Z');
// FAILS: "Value 1742553000.500 cannot be parsed as DateTime"
await client.query({
query: `SELECT {d:DateTime} AS dt`,
query_params: { d: date },
});
// Works — DateTime64(3) accepts fractional timestamps
await client.query({
query: `SELECT {d:DateTime64(3)} AS dt`,
query_params: { d: date },
});
```
#### ClickHouse server
- ClickHouse Server version: 26.2.4.23
Contributor guide
Assessment
This issue has not been assessed yet.