ClickHouse / ClickHouse/clickhouse-js

`Date` objects with non-zero milliseconds produce fractional timestamps rejected by `{param:DateTime}`

Open
#628 3 comments 1 reaction 2 assignees Claimed by @peter-leonov-ch View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.