Help, the date filters in question don't work for me

HI

Please help, I’m new to metabase. I ran the metabase from the jar file, swapped the database from H2 to Postgresql, connected the metabase to the data also on postgresql (another server), everything is on ubuntu.
Everything works fine for me, the data is visible in the metabase. All reports work, but if I use the date filter in the question (defined in the query or set with the filter option when viewing the question) I get the message “No results”.

If I turn off the date filter, the results are. If I start digging through the data question, the dates work.

The database is the sale of goods /

The problem occurs when, when creating a question, I select the table with the items of orders (1) and join the table with orders where, which includes the creation date (2). I set the filter, e.g. the last 30 days (3), the date filter type does not matter.

SELECT “public”.“mag_pozycje”.“jm” AS “jm”, sum(“public”.“mag_pozycje”.“wartosc_netto”) AS “sum”
FROM “public”.“mag_pozycje” FULL JOIN “public”.“mag_dokum” “Dokumenty - Mag Do Kum” ON “public”.“mag_pozycje”.“mag_dokum_id” = “Dokumenty - Mag Do Kum”.“id”
WHERE CAST(“Dokumenty - Mag Do Kum”.“data” AS date) BETWEEN CAST((CAST(now() AS timestamp) + (INTERVAL ‘-30 day’)) AS date)
AND CAST((CAST(now() AS timestamp) + (INTERVAL ‘-1 day’)) AS date)
GROUP BY “public”.“mag_pozycje”.“jm”
ORDER BY “public”.“mag_pozycje”.“jm” ASC

if I do a question only on the order table, the date filter works.

The problem occurs when I have a question for another table and I join the date from another.

With other types of data, I have no problem.

PS. Of course, in the settings, I set all table relationships in accordance with the database model, although metabase did it itself :slight_smile:

PS. II I am sure I have data from every day since last year.

I am begging for help, I have wasted a lot of time to solve the problem :frowning:

System info:

{
“browser-info”: {
“language”: “pl-PL”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.82 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9-Ubuntu-0ubuntu1.20.04”,
“java.vendor”: “Ubuntu”,
“java.vendor.url”: “https://ubuntu.com/”,
“java.version”: “11.0.10”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9-Ubuntu-0ubuntu1.20.04”,
“os.name”: “Linux”,
“os.version”: “5.8.0-43-generic”,
“user.language”: “pl”,
“user.timezone”: “Europe/Warsaw”
},
“metabase-info”: {
“databases”: [
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.18”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2021-03-03”,
“tag”: “v1.38.1”,
“branch”: “release-x.38.x”,
“hash”: “79ef63a”
},
“settings”: {
“report-timezone”: “Europe/Warsaw”
}
}
}

Hi @Krzysztof

  1. Is there any data returned from the “Dokumenty” table in the full-outer-join, when you don’t have any filters?
  2. Is there even any dates the past 30 days in "public"."mag_dokum"."data"?
  3. What is the database column type of "public"."mag_dokum"."data"?
  4. What is the Field Type of "public"."mag_dokum"."data" in Metabase > Admin > Data Model?

HI @flamber

Thank you for your answer :smiley:

Ad:

  1. Yes, without a date filter, the data shows correctly
  2. I have 2 years of history in the Documents database and the newest is from yesterday. Every record in the database has a date, I checked it :).
  3. type of data in database postrgersql is “type/Date”
  4. Filed Type is:

@Krzysztof I cannot reproduce. If you run the SQL query directly on your Postgres, do you then get the results as you expect?

Hi @flamber
Problem solved: D
Thanks for the tip to copy the SQL and run it in the database.
I did not come up with this idea: D

The script didn’t work on the database either, so it wasn’t metabase’s fault.
It turned out that several dozen invoice items were missing. So the related invoice items were not complete against the invoice list.

I corrected the database update, after the new synchronization the metabase works perfectly: D

Thanks for your involvement with my problem.

1 Like