Month Year Filter Malfunction

Hi,

When I add a Month and Year date filter and choose let’s say June 2020, it shows me data for July 2020 and so on and not showing the correct month and year data.

My date column values are stored in this format 2020-07-01

I am using Redshift database. I tried changing the Reporting Time setting from Metabase admin panel but it did not solve the problem. Please advice.

Best,
Zee

Hi @zeehasham
Post “Diagnostic Info” from Admin > Troubleshooting.
Are you using Native/SQL or Simple/Custom question?
What does the query generated look like? Check your browser developer Network-tab to see the request and the query is in the response-data.

Here is the requested information.

I am using Native/SQL

Response Query

  1. {data: {rows: [,…], cols: [,…], native_form: {,…}, results_timezone: “GMT”,…}, database_id: 2,…}

  2. average_execution_time: null

  3. context: “question”

  4. data: {rows: [,…], cols: [,…], native_form: {,…}, results_timezone: “GMT”,…}

  5. cols: [,…]

  6. insights: null

  7. native_form: {,…}

  8. results_metadata: {checksum: “jFD9lm5U5yc0Cy5uY2shHw==”, columns: [,…]}

  9. results_timezone: “GMT”

  10. rows: [,…]

  11. database_id: 2

  12. json_query: {constraints: {max-results: 10000, max-results-bare-rows: 2000}, type: “query”, middleware: null,…}

  13. async?: true

  14. cache-ttl: null

  15. constraints: {max-results: 10000, max-results-bare-rows: 2000}

  16. database: 2

  17. middleware: null

  18. parameters: [{type: “date/month-year”, target: [“dimension”, [“field-id”, 29252]], value: “2020-06”}]

  19. query: {source-table: 965,…}

  20. type: “query”

  21. row_count: 108

  22. running_time: 115

  23. started_at: “2020-10-02T19:00:56.801384Z”

  24. status: “completed”

Diagnostic Info:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.8+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.8”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.8+10”,
“os.name”: “Linux”,
“os.version”: “3.10.0-1127.10.1.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“redshift”,
“h2”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.5”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-08-04”,
“tag”: “v0.36.3”,
“branch”: “release-0.36.x”,
“hash”: “a792f14”
},
“settings”: {
“report-timezone”: null
}
}
}

@zeehasham
Post your SQL query.
And I don’t know which browser you’re using, but expand the collapsed result-data so you can see the actual query, and then post that too.

Sorry, I am using Custom/Question.

here is the query response

json_query: {constraints: {max-results: 10000, max-results-bare-rows: 2000}, type: “query”, middleware: null,…}
async?: true
cache-ttl: null
constraints: {max-results: 10000, max-results-bare-rows: 2000}
database: 2
middleware: null
parameters: [{type: “date/month-year”, target: [“dimension”, [“field-id”, 29252]], value: “2020-07”}]
query: {source-table: 965,…}
aggregation: [[“sum”, [“field-id”, 27806]], [“distinct”, [“field-id”, 27839]], [“sum”, [“field-id”, 27794]],…]
breakout: [[“field-id”, 27823], [“joined-field”, “nasp_contract”, [“field-id”, 29249]]]
expressions: {telco_cost: ["+", [“field-id”, 27800], [“field-id”, 27849]],…}
joins: [{fields: [[“joined-field”, “nasp_contract”, [“field-id”, 29249]]], source-table: 988,…}]
source-table: 965
type: “query”

@zeehasham
So this is a card on a dashboard? There should be a native_query array in the JSON object.
What is timezone of your database?
And what column type is the date-column in the database?
You might want to read this:
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html

@flamber

The column is of “date” type in redshift.

Database timezone set to EDT

I found the native query

native_form: {,…}
params: null
query: “SELECT “source”.“nasp_name” AS “nasp_name”, “source”.“contract_status” AS “contract_status”, sum(“source”.“total_revenue”) AS “sum”, count(distinct “source”.“circuit_id”) AS “count”, sum(“source”.“telco_margin”) AS “sum_2”, sum(“source”.“telco_cost”) AS “sum_3”, sum(CASE WHEN “source”.“margin_type” = ‘NEGATIVE’ THEN “source”.“telco_margin” ELSE 0.0 END) AS “sum-where”, sum(CASE WHEN “source”.“margin_type” = ‘POSITIVE’ THEN “source”.“telco_margin” ELSE 0.0 END) AS “sum-where_2”, sum(CASE WHEN “source”.“margin_type” = ‘NEGATIVE’ THEN 1 ELSE 0.0 END) AS “count-where”, sum(CASE WHEN “source”.“margin_type” = ‘POSITIVE’ THEN 1 ELSE 0.0 END) AS “count-where_2”, sum(CASE WHEN “source”.“margin_type” = ‘NEGATIVE’ THEN “source”.“telco_cost” ELSE 0.0 END) AS “sum-where_3”, sum(CASE WHEN “source”.“margin_type” = ‘POSITIVE’ THEN “source”.“telco_cost” ELSE 0.0 END) AS “sum-where_4”, sum(CASE WHEN “source”.“margin_type” = ‘POSITIVE’ THEN “source”.“total_revenue” ELSE 0.0 END) AS “sum-where_5”, sum(CASE WHEN “source”.“margin_type” = ‘NEGATIVE’ THEN “source”.“total_revenue” ELSE 0.0 END) AS “sum-where_6” FROM (SELECT (“rev_vol_snd”.“single_row”.“total_cost_nonaflt” + “rev_vol_snd”.“single_row”.“occ_cost_nonaflt_m6364”) AS “telco_cost”, concat(concat(substring(“rev_vol_snd”.“single_row”.“prod_yr_mth”, 1, 4), ‘-’), substring(“rev_vol_snd”.“single_row”.“prod_yr_mth”, 5, 2)) AS “date_format”, “rev_vol_snd”.“single_row”.“nasp_id” AS “nasp_id”, “nasp_contract”.“nasp_id” AS “nasp_id_2”, “rev_vol_snd”.“single_row”.“total_revenue” AS “total_revenue”, “rev_vol_snd”.“single_row”.“circuit_id” AS “circuit_id”, “rev_vol_snd”.“single_row”.“telco_margin” AS “telco_margin”, “rev_vol_snd”.“single_row”.“margin_type” AS “margin_type”, “rev_vol_snd”.“single_row”.“nasp_name” AS “nasp_name”, “nasp_contract”.“contract_status” AS “contract_status”, “rev_vol_snd”.“single_row”.“total_cost_nonaflt” AS “total_cost_nonaflt”, “rev_vol_snd”.“single_row”.“occ_cost_nonaflt_m6364” AS “occ_cost_nonaflt_m6364”, “rev_vol_snd”.“single_row”.“prod_yr_mth” AS “prod_yr_mth”, “rev_vol_snd”.“single_row”.“date_format” AS “date_format_2” FROM “rev_vol_snd”.“single_row” INNER JOIN “rev_vol_snd”.“nasp_contract” “nasp_contract” ON “rev_vol_snd”.“single_row”.“nasp_id” = “nasp_contract”.“nasp_id”) “source” WHERE (“source”.“date_format” >= timestamp with time zone ‘2020-07-01 00:00:00.000Z’ AND “source”.“date_format” < timestamp with time zone ‘2020-08-01 00:00:00.000Z’) GROUP BY “source”.“nasp_name”, “source”.“contract_status” ORDER BY “source”.“nasp_name” ASC, “source”.“contract_status” ASC”
results_metadata: {checksum: “aLyaey1JE8rqsWduH50vkA==”, columns: [,…]}
results_timezone: “GMT”

@zeehasham You’re results timezone is GMT because your server is set to that. Either change the timezone of Java as described in the troubleshooting guide I linked to, or set the Reporting Timezone in Admin > Settings > General.