Date Filter Only Going Back 2months

Get Help
1

Hello!
I am trying to filter the following query by month. It will only work for November 2021 and December 2021 - the dataset goes back to January 2019 (and has plenty of data from then). Could someone please advise if I am missing something / provide guidance?

SELECT
date_diff('days', members.timestampregistered, store.date) account_age_days,
avg(daily_signups::real), count(members.memberid) as "Active Users"
FROM store
JOIN members
ON store.memberid = members.memberid
AND store.date <= trunc(members.timestampregistered) + 31
WHERE date_trunc('month', store.date) = {{MonthStarting}}
and store.date >= {{MonthStarting}}
GROUP BY account_age_days
ORDER BY account_age_days ASC

2

Hi @isabella.springis

Post "Diagnostic Info" from Admin > Troubleshooting.

Have you tried using hardcoded values instead of a variable to figure out if the problem is with your query/data instead of with the variable?

So which variable type is {{MonthStarting}}? simple Date, or a Field Filter?
If it is a Date, then you should probably wrap it like date_trunc('month', {{MonthStarting}}) if you actually only want to use it as a "month" selector.

3

When I use date_trunc('month', {{MonthStarting}}) I get an error message:

ERROR: function date_trunc("unknown", "unknown") is not unique Hint: Could not choose a best candidate function. You may need to add explicit type casts.

If I filter either November or December (2019, 2020 and 2021) it will work - just not for the other months... this is the issue.

4

@isabella.springis

  1. Post "Diagnostic Info" from Admin > Troubleshooting.
  2. Have you tried using hardcoded values instead of a variable to figure out if the problem is with your query/data instead of with the variable?
  3. So which variable type is {{MonthStarting}} ? simple Date, or a Field Filter?
5

[4ab3266a-208b-4e3c-a813-b42e88d32fc3] 2022-01-17T13:16:47+01:00 DEBUG metabase.server.middleware.log POST /api/card/3290/query 202 [ASYNC: completed] 23.6 s (14 DB calls) App DB connections: 4/15 Jetty threads: 3/50 (32 idle, 0 queued) (164 total active threads) Queries in flight: 6 (0 queued); redshift DB 6 connections: 3/3 (2 threads blocked)

6
  • Have you tried using hardcoded values instead of a variable to figure out if the problem is with your query/data instead of with the variable?

What do you mean by hard coded values?

  • So which variable type is {{MonthStarting}} ? simple Date, or a Field Filter?

Date

7

@isabella.springis
I'm asking for "Diagnostic Info" from Admin > Troubleshooting, not your logs.
Hardcoding means that you remove the variable and input a value directly in SQL instead.

8

{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.13+8-post-Debian-1deb10u1",
"java.vendor": "Debian",
"java.vendor.url": "https://tracker.debian.org/openjdk-11",
"java.version": "11.0.13",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.13+8-post-Debian-1deb10u1",
"os.name": "Linux",
"os.version": "4.19.0-18-cloud-amd64",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"redshift",
"mysql"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.17"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"date": "2021-12-16",
"tag": "v0.41.5",
"branch": "release-x.41.x",
"hash": "fbfffc6"
},
"settings": {
"report-timezone": "Europe/London"
}
}
}