Allow direct usage of Date objects for inserts

[aimfeld]

I'm using clickhouse-js to insert rows into a table. For timestamps, I'm using DateTime64(3, 'UTC'). However, the client seems to have a problem with Date objects: Cannot parse input: expected '"' before: 'Z","message":"{\\"created_at\\":\\"2023-12-06T10:54:48.000Z\\",\\"modified_at\\":\\"2023-12-': (while reading the value of key created_at): While executing ParallelParsingBlockInputFormat: (at row 1) ... ,"error":{"code":"27","type":"CANNOT_PARSE_INPUT_ASSERTION_FAILED"}

When I convert the Date objects to strings and remove the timezone part, it works:

import { ClickHouseClient } from '@clickhouse/client';
import { InsertClient } from './InsertClient';
import moment from 'moment';

export class ClickHouseInsertClient implements InsertClient {
    public constructor(private clickHouseClient: ClickHouseClient) {}

    public async insertObjects(tableName: string, objects: object[]): Promise<void> {
        for (const object of objects) {
            for (const key of Object.keys(object)) {
                object[key] = this.sanitizeValue(object[key]);
            }
        }
        await this.clickHouseClient.insert({
            table: tableName,
            values: objects,
            format: 'JSONEachRow'
        });
    }

    private sanitizeValue(value: unknown): unknown {
        // ClickHouse can't handle the timezone part (Z or offset +00:00) in the date string
        if (value instanceof Date) {
            return moment(value).format('YYYY-MM-DD HH:mm:ss.SSS');
        }
        return value;
    }
}

Environment

• Client version: 0.2.6
• Language version:
• OS: Linux

ClickHouse server

• ClickHouse Server version: 23.9.1.1854

[mshustov]

consider using date_time_input_format= best_effort

[slvrtrn]

@mshustov, thanks! It works very well with DateTime fields (see the test). Date/Date32 are not parsed properly with this setting, though; I think we can revisit it in the scope of https://github.com/ClickHouse/clickhouse-js/issues/216

@aimfeld, I added an example that should work for your use-case. Feel free to re-open or DM in the community Slack if you encounter any issues.

[aimfeld]

@slvrtrn It works, thanks for the quick fix!