Today filter not working when timesamp field is in datetime64 format
antoxa9898 opened this issue · 2 comments
Describe the bug
When applying 'today' filter in metabase i have no results, but if i use specific date filter - on specific date(for example 29.01.23), i receive results.
This issue appeared after i changed timestamp field in my clickhouse table from DateTime to DateTime64 type( i need to se MS).
Steps to reproduce
- open clickhouse datasource table in metabase
- apply 'today' filter
- you will reseive no results
Configuration
Environment
- metabase-clickhouse-driver version: 1.3.0
- Metabase version: v0.48.1
this is my create table:
CREATE TABLE vector.qa_nginx_logs
(
`ASN` String,
`bytes_sent` String,
`correlation_id` String,
`country` String,
`host` String,
`http_host` String,
`method` String,
`path` String,
`referer` String,
`remote_addr` String,
`request_full` String,
`request_length` UInt64,
`request_scheme` LowCardinality(String),
`request_time` String,
`response_content_type` LowCardinality(String),
`status` Int32,
`timestamp` DateTime64(3),
`trace_identifier` String,
`upstream_addr` String,
`upstream_cache_status` String,
`upstream_connect_time` String,
`upstream_header_time` String,
`upstream_response_time` String,
`user_agent` String,
`x_forwarded_for` String
)
ENGINE = ReplicatedMergeTree('/vector.qa_nginx_logs_new', '{replica}')
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp, host)
TTL toDateTime(timestamp) + toIntervalWeek(2)
SETTINGS index_granularity = 8192
this is sql query which is generated by metabase:
SELECT
`vector`.`qa_nginx_logs`.`ASN` AS `ASN`,
`vector`.`qa_nginx_logs`.`bytes_sent` AS `bytes_sent`,
`vector`.`qa_nginx_logs`.`correlation_id` AS `correlation_id`,
`vector`.`qa_nginx_logs`.`country` AS `country`,
`vector`.`qa_nginx_logs`.`host` AS `host`,
`vector`.`qa_nginx_logs`.`http_host` AS `http_host`,
`vector`.`qa_nginx_logs`.`method` AS `method`,
`vector`.`qa_nginx_logs`.`path` AS `path`,
`vector`.`qa_nginx_logs`.`referer` AS `referer`,
`vector`.`qa_nginx_logs`.`remote_addr` AS `remote_addr`,
`vector`.`qa_nginx_logs`.`request_full` AS `request_full`,
`vector`.`qa_nginx_logs`.`request_length` AS `request_length`,
`vector`.`qa_nginx_logs`.`request_scheme` AS `request_scheme`,
`vector`.`qa_nginx_logs`.`request_time` AS `request_time`,
`vector`.`qa_nginx_logs`.`response_content_type` AS `response_content_type`,
`vector`.`qa_nginx_logs`.`status` AS `status`,
`vector`.`qa_nginx_logs`.`timestamp` AS `timestamp`,
`vector`.`qa_nginx_logs`.`trace_identifier` AS `trace_identifier`,
`vector`.`qa_nginx_logs`.`upstream_addr` AS `upstream_addr`,
`vector`.`qa_nginx_logs`.`upstream_cache_status` AS `upstream_cache_status`,
`vector`.`qa_nginx_logs`.`upstream_connect_time` AS `upstream_connect_time`,
`vector`.`qa_nginx_logs`.`upstream_header_time` AS `upstream_header_time`,
`vector`.`qa_nginx_logs`.`upstream_response_time` AS `upstream_response_time`,
`vector`.`qa_nginx_logs`.`user_agent` AS `user_agent`,
`vector`.`qa_nginx_logs`.`x_forwarded_for` AS `x_forwarded_for`
FROM
`vector`.`qa_nginx_logs`
WHERE
(
`vector`.`qa_nginx_logs`.`timestamp` >= CAST(NOW() AS date)
)
AND (
`vector`.`qa_nginx_logs`.`timestamp` < CAST(NOW() + INTERVAL 1 day AS date)
)
LIMIT
1048575
I tested it via Metabase with the following:
CREATE TABLE uniq_test
(
`gettime` DateTime64(3),
`uid` String
)
ENGINE = MergeTree
ORDER BY tuple()
INSERT INTO uniq_test VALUES (now(), 'foo'), (toDateTime64(now() - interval 2 day, 3), 'bar')
┌─────────────────gettime─┬─uid─┐
│ 2024-01-31 16:25:21.000 │ foo │
│ 2024-01-29 16:25:21.000 │ bar │
└─────────────────────────┴─────┘
SELECT
default.uniq_test.gettime AS gettime,
default.uniq_test.uid AS uid
FROM default.uniq_test
WHERE (default.uniq_test.gettime >= CAST(NOW(), 'date')) AND (default.uniq_test.gettime < CAST(NOW() + toIntervalDay(1), 'date'))
LIMIT 2000
┌─────────────────gettime─┬─uid─┐
│ 2024-01-31 16:25:21.000 │ foo │
└─────────────────────────┴─────┘
Does your generated query also return no rows when executed via CH client?
thanks for your hint. I tested creation table with your query and it works. after some tests i determined that promlem appears when datetime64 type field is primary or partition key.
and after testing in different versions i determined that problem dissapeared in ClickHouse release 23.3.
So this is clickhouse bug, not click metabase plugin