convert timestamp to DataTime64 fail

meilin-shi commented 3 years ago

Need your help. Thank you. I create a table with sql:

CREATE TABLE dt
(
    `timestamp` DateTime64(3, 'Europe/Moscow'),
    `event_id` UInt8
)
ENGINE = TinyLog;

But when I insert data to the table with

const writableStream = ch.query(`INSERT INTO dt FORMAT TSV`, {format: "TSV"}, (err) => {
  if (err) {
    console.error(err)
  }
  console.log('Insert complete!')
})
writableStream.write(["1546300800012", 4]);
writableStream.end();

it returns an error:

{ Error: Cannot parse input: expected '\t' before: '012\t4\n': (at row 1): Row 1:Column 0,   name: timestamp, type: DateTime64(3, 'Europe/Moscow'), parsed text: "1546300800"ERROR: garbage after DateTime64(3, 'Europe/Moscow'): "012<TAB>4<LINE FEED>" (version 21.2.5.5 (official build))
    at parseError (/home/work/src/node_modules/@apla/clickhouse/src/parse-error.js:2:15)
    at errorHandler (/home/work/src/node_modules/@apla/clickhouse/src/clickhouse.js:29:13)
    at IncomingMessage.<anonymous> (/home/meilin/src/node_modules/@apla/clickhouse/src/clickhouse.js:97:11)
    at IncomingMessage.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at process._tickCallback (internal/process/next_tick.js:63:19) code: 27, scope: 'DB::ParsingException:' }

But if I modify the query statement to

writableStream.write(["1546300800", 4])

It will be OK. But in fact, the format of timestamp in my table is DataTime64(3, "xxx"), It could get a timestamp length of 13.

Anybody occured the problem?

meilin-shi commented 3 years ago

If I want to insert a timestamp format is DataTime64(3, "xx") to a table and use this method

ch.query(`INSERT INTO dt FORMAT TSV`, {format: "TSV"},
writableStream.write(["1546300800012", 4]);

What should I do? Thank You.

meilin-shi commented 3 years ago

I got it. I use

writableStream.write(["1546300800.012", 4]);

and it works fine.

apla / node-clickhouse

convert timestamp to DataTime64 fail #75