how to insert/update date/datetime field

[flyly0755]

CREATE TABLE default.test_tb
(
test_fd1 DateTime,
id Int8
)
ENGINE = MergeTree
ORDER BY test_fd1
SETTINGS index_granularity = 8192

with table as above, insert one data record as below:

insert into TABLE test_tb(id, test_fd1) values(1, toDateTime('2022-03-25 22:35:47.000'))

but result is:
1970-01-01 08:00:00.000 1

without toDateTime function:
insert into TABLE test_tb(id, test_fd1) values(2, '2022-03-25 22:35:47.000')
insert failed with error:
DB::Exception: Cannot parse string '2022-03-25 22:35:47.000' as DateTime: syntax error at position 19 (parsed just '2022-03-25 22:35:47'): while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('DateTime') :: 2, _CAST(_dummy_0, 'DateTime') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('DateTime'), _CAST(_dummy_0, 'DateTime')) DateTime : 1': While executing ValuesBlockInputFormat. (CANNOT_PARSE_TEXT) (version 21.12.3.32 (official build))

how to correctly insert/update date/datetime field with sql language?

[den-crane]

https://clickhouse.com/docs/en/sql-reference/data-types/datetime64/

https://clickhouse.com/docs/en/operations/settings/settings/#date_time_input_format

CREATE TABLE default.test_tb
(
test_fd1 DateTime,
id Int8
)
ENGINE = MergeTree
ORDER BY test_fd1
SETTINGS index_granularity = 8192;

set date_time_input_format='best_effort';

insert into TABLE test_tb(id, test_fd1) values(1, '2022-03-25 22:35:47.000');

insert into TABLE test_tb(id, test_fd1) values(2, cast('2022-03-25 22:35:47.000','DateTime64(3)'));

select * from test_tb;

┌────────────test_fd1─┬─id─┐
│ 2022-03-25 22:35:47 │  1 │
└─────────────────────┴────┘
┌────────────test_fd1─┬─id─┐
│ 2022-03-25 22:35:47 │  2 │
└─────────────────────┴────┘

CREATE TABLE default.test_tb1
(
test_fd1 DateTime64(3),
id Int8
)
ENGINE = MergeTree
ORDER BY test_fd1
SETTINGS index_granularity = 8192;

insert into TABLE test_tb1(id, test_fd1) values(1, '2022-03-25 22:35:47.000');

select * from test_tb1;
┌────────────────test_fd1─┬─id─┐
│ 2022-03-25 22:35:47.000 │  1 │
└─────────────────────────┴────┘

[flyly0755]

with clickhouse-client, it's ok.

[den-crane]

insert into TABLE test_tb(id, test_fd1) settings date_time_input_format='best_effort' values(1, '2022-03-25 22:35:47.000');

[den-crane]

Or in the DBeaver connection settings.

[flyly0755]

seems not ok, try both methods:(
maybe version related issue?
clickhouse: 21.12.3.32
DBeavaer: 21.2.2.202110030714

[den-crane]

Why do you need it with DBeaver ?