How to avoid DATE_TRUNC that is generated by date filter

We have an Athena table with column 'partition_date_hour' string datatype. Data for that column looks like this in YYYY-MM-DD HH:00 format

partition_date_hour
2024-01-14 23:00
2024-01-14 23:00
2024-01-14 23:00
2024-01-14 23:00
2024-01-14 23:00
2024-01-14 23:00

We did casting at column level to Field Type -> Creation Timestamp and Cast to a specific data type -> Coercion/ISO8601 ->DateTime.

We created a query with variable p_date with Variable type -> Field Filter

As you can see we get 0 results and following the preview of the query

SELECT
COUNT(*) AS "count"
FROM
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"
WHERE
"ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."account_id" = '183385486431'
AND "ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."action" = 'ALLOW'
AND "ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."httpsourcename" in ('ALB','APIGW','APPRUNNER','APPSYNC','CF','COGNITOIDP')
AND DATE_TRUNC('day', CAST("ca_secdatalakenp_waf_logs_fh_test"."waf_logs_fh_test_raw_h_v13"."partition_date_hour" AS timestamp)) BETWEEN timestamp '2024-01-15 18:00:00.000' AND timestamp '2024-01-15 19:00:00.000'

If we take DATE_TRUNC generated by metabase, it returns results. What settings are needed to get rid of DATE_TRUNC ?

Attached is the diagnostic info:

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.21+9-LTS",
"java.vendor": "Red Hat, Inc.",
"java.vendor.url": "https://www.redhat.com/",
"java.version": "11.0.21",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.21+9-LTS",
"os.name": "Linux",
"os.version": "4.14.273-207.502.amzn2.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"h2",
"athena"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.12"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.6"
}
},
"run-mode": "prod",
"version": {
"date": "2024-01-05",
"tag": "v0.47.11",
"branch": "?",
"hash": "51935b1"
},
"settings": {
"report-timezone": null
}
}
}

1 Like