We are using Amazon Athena as our database. You can see below what kind of query I am making using the date filter query.
I am trying to set up a native sql query on a table from our logs database with a field filter mapped to a datetime column, so that I can display records from the past few hours.
Expected result:
We have data from the past 3 hours so it should return only the last 3 hours on data.
What's happening
Anything less than 16 hours, I get no results. As soon as it's 16 hours I get all the results I want. When I don't use the sql editor but the main dashboard within the same time range, I get results. Here are some screenshots.
^ Using Today as the filter I get the results I want, the results you see there should have been contained in the last 2 hours query.
^ Query trying to get last 2 hour of results showing nothing
Here is my query:
SELECT "logs"."fuse-request-logs-prod"."type" AS "type", "logs"."fuse-request-logs-prod"."organization_id" AS "organization_id", "logs"."fuse-request-logs-prod"."latency" AS "latency", "logs"."fuse-request-logs-prod"."entity_id" AS "entity_id", "logs"."fuse-request-logs-prod"."financial_connection_id" AS "financial_connection_id", "logs"."fuse-request-logs-prod"."financial_institution_id" AS "financial_institution_id", "logs"."fuse-request-logs-prod"."aggregator" AS "aggregator", "logs"."fuse-request-logs-prod"."financial_institution_name" AS "financial_institution_name", "logs"."fuse-request-logs-prod"."request_id" AS "request_id", "logs"."fuse-request-logs-prod"."request_path" AS "request_path", "logs"."fuse-request-logs-prod"."request_method" AS "request_method", "logs"."fuse-request-logs-prod"."response_status_code" AS "response_status_code", "logs"."fuse-request-logs-prod"."response_details" AS "response_details", "logs"."fuse-request-logs-prod"."response_error_code" AS "response_error_code", "logs"."fuse-request-logs-prod"."response_error_details" AS "response_error_details", "logs"."fuse-request-logs-prod"."created_at" AS "created_at"
FROM "logs"."fuse-request-logs-prod"
WHERE {{code}} AND {{request_id}} AND {{aggregator}} AND {{type}} AND {{date_range}} AND {{organization_id}} AND {{api_route}} AND {{entity_id}} AND {{Institution_name}}
LIMIT 1048575
I've checked github and there is a similar open issue - Metabase use field type with native query not working correctly with previous x hours · Issue #18315 · metabase/metabase · GitHub
Thanks for any help in advance.