DB::Exception: Key expression contains comparison between inconvertible types: DateTime64(3) and DateTime inside entry_time >= parseDateTimeBestEffort('2021-01-15 00:00:00') (version 20.8.4.11 (official build))

[jjtjiang]

when use the DateTime(64) field to filter,it's say between time ,it will trans error . eg 'entry_time ' is DateTime(64) , it will trans to 'entry_time >= parseDateTimeBestEffort('2021-01-15 00:00:00') '. if it can trans to 'entry_time >= '2021-01-15 00:00:00',no need the function, it will work. thanks

[enqueue]

Unfortunately, we need to use parseDateTimeBestEffort for some queries. Have you asked ClickHouse team if they plan to support comparison between these data types (even for key fields)?

The manual says:

Unlike DateTime, DateTime64 values are not converted from String automatically

Notes using ClickHouse client:

CREATE TABLE test.dt64test
(
    `dt64column` DateTime64(3),
    `dtcolumn` DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(dt64column)
ORDER BY dt64column
SETTINGS index_granularity = 8192

INSERT INTO dt64test (dt64column, dtcolumn) VALUES ('2020-01-13 13:37:00', '2020-01-13 13:37:00');

SELECT 1
FROM dt64test
WHERE dt64column = dtcolumn

Query id: 402f0f02-7026-4945-8856-7f2f43748fb8

Ok.

0 rows in set. Elapsed: 0.005 sec.

SELECT 1
FROM dt64test
WHERE dt64column = '2020-01-13 13:37:00'

Query id: 45207bab-b813-4491-b7c6-7f05c9f0acbe

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.007 sec.

SELECT 1
FROM dt64test
WHERE dt64column = parseDateTimeBestEffort('2020-01-13 13:37:00')

Query id: 66915cb4-7b96-4d62-97dc-0a35bdc950f9


Received exception from server (version 20.12.4):
Code: 169. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Key expression contains comparison between inconvertible types: DateTime64(3) and DateTime inside dt64column = parseDateTimeBestEffort('2020-01-13 13:37:00'). 

0 rows in set. Elapsed: 0.029 sec.

@den-crane do you know if this is the expected behavior?

[den-crane]

You should use parseDateTime64BestEffort

SELECT 1　FROM dt64test　WHERE dt64column = parseDateTime64BestEffort('2020-01-13 13:37:00',3)

[enqueue]

Thanks @den-crane ! However, we don't know the data type of the column up front. Comparing with DateTime just worked fine for Date and DateTime columns, that's why I used parseDateTimeBestEffort. Replacing this globally with parseDateTime64BestEffort we would lose compatibility; at least equality check with DateTime column does not seem to work:

SELECT *
FROM dt64test
WHERE dtcolumn = parseDateTime64BestEffort('2020-01-13 13:37:00')

Query id: 7685906d-bf1d-4f46-ba9e-fbe357bd1844

Ok.

0 rows in set. Elapsed: 0.006 sec. 

[den-crane]

column does not seem to work:

works for me

SELECT 1
FROM dt64test
WHERE dt64column = parseDateTime64BestEffort('2020-01-13 13:37:00', 3)

Query id: abeb4fc5-3f58-467f-8894-ff2e50fb1df3

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.001 sec.

Or you can convert constant to a column - materialize

SELECT 1
FROM dt64test
WHERE dt64column = materialize(parseDateTimeBestEffort('2020-01-13 13:37:00'))

Query id: 90ef1e45-25a8-4c9b-84f1-ea0b22256ba8

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.001 sec.

[enqueue]

Yes, comparing DateTime64 values against DateTime64 columns works fine, but comparison with Date or DateTime (my example from above) columns does not work.

I had never heard about the materialize function, but looking at the docs it looks like a little bit of a technical thing. If you compare my first query (dt64 column = dt column) with the third one (dt64 column = dt value), I see that the results are different and that materialize might lead to a different result, but I don't think I want to go down that road.

We could just sit this one out and wait until DateTime64 and DateTime data types become one, but I don't know if or when this will ever happen...

[den-crane]

https://github.com/ClickHouse/ClickHouse/issues/19078

[enqueue]

Good news, @jjtjiang the new driver version should play better with DateTime64 columns.

CREATE TABLE felix.dt64test
(
    `myTime` DateTime64(3) DEFAULT now(),
    `otherCol` String
)
ENGINE = Memory

INSERT INTO dt64test (myTime, otherCol)
VALUES 
('2021-01-01', 'foo'),
('2021-02-02', 'baz'),
('2021-12-04 13:37:00', 'qux'), 
('2021-12-01 00:00:00.420', 'quux');

[enqueue]

Should be fixed

[phuntik]

Greetings, data masters! Im not so sure if this is a right topic for my problem, but seems like the cause of it might be the same to discussed one. Table with timestamp column in DateTime64(3) format is not being properly filtered by date filter when using relative dates. Video attached, please, take a look.

https://user-images.githubusercontent.com/15222265/229078643-8655fd8a-74c2-46c3-973f-aecc7c77444f.mp4

Dont know where to check Clickhouse driver version, but on March 29 the last one has been installed from https://api.github.com/repos/enqueue/metabase-clickhouse-driver/releases/latest reference, so i believe it is v1.1.3.

{
  "browser-info": {
    "language": "en-US",
    "platform": "Linux x86_64",
    "userAgent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:107.0) Gecko/20100101 Firefox/107.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.18+10-LTS",
    "java.vendor": "Red Hat, Inc.",
    "java.vendor.url": "https://www.redhat.com/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10-LTS",
    "os.name": "Linux",
    "os.version": "3.10.0-1160.el7.x86_64",
    "user.language": "en",
    "user.timezone": "Europe/Moscow"
  },
  "metabase-info": {
    "databases": [
      "clickhouse"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MariaDB",
        "version": "10.4.28-MariaDB-log"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.7.6"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-01-17",
      "tag": "v0.45.2",
      "branch": "release-x.45.x",
      "hash": "819ce29"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Thanks!

[phuntik]

Hey guys! Any update pls? Or it's better to create a separate issue for my question above?

[slvrtrn]

@phuntik, it's better to create a new issue. I did not receive a notification about the last comment; sorry about that.