date filter not work when I choosed 'hour'

[jovi-Li]

date filter not work when I choosed 'hour' ,error like that: Cannot convert string 2022-04-06 10:00:00 to type Date: while executing 'FUNCTION greaterOrEquals(toDate(Longdate) : 5, '2022-04-06 10:00:00' : 2) -> greaterOrEquals(toDate(Longdate), '2022-04-06 10:00:00') UInt8 : 7' (version 21.8.4.51 (official build)) but it worked when it I choosed day

my config: { "browser-info": { "language": "zh-CN", "platform": "Win32", "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "UTF-8", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "11.0.14.1+1", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "11.0.14.1", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "11.0.14.1+1", "os.name": "Linux", "os.version": "3.10.0-1062.18.1.el7.x86_64", "user.language": "en", "user.timezone": "Asia/Shanghai" }, "metabase-info": { "databases": [ "clickhouse", "h2" ], "hosting-env": "unknown", "application-database": "h2", "application-database-details": { "database": { "name": "H2", "version": "1.4.197 (2018-03-18)" }, "jdbc-driver": { "name": "H2 JDBC Driver", "version": "1.4.197 (2018-03-18)" } }, "run-mode": "prod", "version": { "date": "2022-03-23", "tag": "v0.42.3", "branch": "release-x.42.x", "hash": "33fb268" }, "settings": { "report-timezone": "Asia/Shanghai" } } }

[tema-popov]

Hi! Same problem here

Looks like toDate should not be in resulted query if we comparing datetimes, not dates. That is happening when I'm trying to use variable filter for minutes and hours

[Tozapid]

Looks like we have the same error

This is the query that generates Metabase UI (I removed some trash from the query)

SELECT *
FROM table
WHERE (toDate(created_at) >= toDate(now() + INTERVAL -7 day)
   AND toDate(created_at) < toDate(now()))
LIMIT 1048575

And this is how it must work

SELECT *
FROM 
table
WHERE (
created_at >= toDate((CAST(now() AS timestamp) + INTERVAL -7 day))
   AND 
created_at < toDate(now()))
LIMIT 1048575

If I filter by the exact date it also works well

WHERE (created_at >= parseDateTimeBestEffort('2022-12-12 00:00:00.000Z')
   AND created_at < parseDateTimeBestEffort('2022-12-13 00:00:00.000Z'))

I hope this will help

[slvrtrn]

Thanks for the report @Tozapid This definitely helps.

[slvrtrn]

An update for this issue: we finally found the root cause after we had a look at a similar report in #196 (see the comments there).

[slvrtrn]

It is fixed in 1.3.0.