0.39.1 Date Filter not working anymore in BigQuery

Bug reports
1

Hi, I'm stuck on a strange error when native-querying a table that has been working (still is) fine in other queries. It appears to be a known bug:

No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY)

The strange thing is that I use the same filter on the same field in some previous queries and it still runs smooth.

[e6f8c727-b0fb-4329-ad31-497d03a3e754] 2021-05-04T14:49:52+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 18,
 :started_at #t "2021-05-04T14:49:51.549749+02:00[Europe/Berlin]",
 :via
 [{:status :failed,
   :class clojure.lang.ExceptionInfo,
   :error "Error executing query",
   :stacktrace
   ["--> driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:224)"
    "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
    "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:205)"
    "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
    "driver.bigquery$process_native_STAR_$thunk__1576.invoke(bigquery.clj:277)"
    "driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:279)"
    "driver.bigquery$process_native_STAR_.invoke(bigquery.clj:272)"
    "driver.bigquery$eval1580$fn__1582.invoke(bigquery.clj:299)"
    "query_processor.context$executef.invokeStatic(context.clj:59)"
    "query_processor.context$executef.invoke(context.clj:48)"
    "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
    "query_processor.context.default$default_runf.invoke(default.clj:66)"
    "query_processor.context$runf.invokeStatic(context.clj:45)"
    "query_processor.context$runf.invoke(context.clj:39)"
    "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
    "query_processor.reducible$pivot.invoke(reducible.clj:31)"
    "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47242.invoke(mbql_to_native.clj:25)"
    "query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:39)"
    "query_processor.middleware.limit$limit$fn__47228.invoke(limit.clj:37)"
    "query_processor.middleware.cache$maybe_return_cached_results$fn__45936.invoke(cache.clj:211)"
    "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47488.invoke(optimize_temporal_filters.clj:204)"
    "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49418.invoke(validate_temporal_bucketing.clj:50)"
    "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45055.invoke(auto_parse_filter_values.clj:43)"
    "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41400.invoke(wrap_value_literals.clj:161)"
    "query_processor.middleware.annotate$add_column_info$fn__41275.invoke(annotate.clj:598)"
    "query_processor.middleware.permissions$check_query_permissions$fn__46356.invoke(permissions.clj:81)"
    "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48346.invoke(pre_alias_aggregations.clj:40)"
    "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
    "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48645.invoke(resolve_joined_fields.clj:102)"
    "query_processor.middleware.resolve_joins$resolve_joins$fn__48958.invoke(resolve_joins.clj:171)"
    "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44631.invoke(add_implicit_joins.clj:190)"
    "query_processor.middleware.large_int_id$convert_id_to_string$fn__47192.invoke(large_int_id.clj:59)"
    "query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
    "query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
    "query_processor.middleware.binning$update_binning_strategy$fn__45442.invoke(binning.clj:227)"
    "query_processor.middleware.resolve_fields$resolve_fields$fn__46159.invoke(resolve_fields.clj:34)"
    "query_processor.middleware.add_dimension_projections$add_remapping$fn__44280.invoke(add_dimension_projections.clj:312)"
    "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44509.invoke(add_implicit_clauses.clj:147)"
    "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49367.invoke(upgrade_field_literals.clj:40)"
    "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44794.invoke(add_source_metadata.clj:123)"
    "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48520.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
    "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45002.invoke(auto_bucket_datetimes.clj:147)"
    "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46206.invoke(resolve_source_table.clj:45)"
    "query_processor.middleware.parameters$substitute_parameters$fn__48328.invoke(parameters.clj:111)"
    "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46258.invoke(resolve_referenced.clj:79)"
    "query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
    "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44803.invoke(add_timezone_info.clj:15)"
    "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49320.invoke(splice_params_in_response.clj:32)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531$fn__48535.invoke(resolve_database_and_driver.clj:31)"
    "driver$do_with_driver.invokeStatic(driver.clj:60)"
    "driver$do_with_driver.invoke(driver.clj:56)"
    "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531.invoke(resolve_database_and_driver.clj:25)"
    "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
    "query_processor.middleware.store$initialize_store$fn__49329$fn__49330.invoke(store.clj:11)"
    "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
    "query_processor.store$do_with_store.invoke(store.clj:38)"
    "query_processor.middleware.store$initialize_store$fn__49329.invoke(store.clj:10)"
    "query_processor.middleware.validate$validate_query$fn__49374.invoke(validate.clj:10)"
    "query_processor.middleware.normalize_query$normalize$fn__47255.invoke(normalize_query.clj:22)"
    "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44649.invoke(add_rows_truncated.clj:35)"
    "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49305.invoke(results_metadata.clj:147)"
    "query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
    "query_processor.middleware.process_userland_query$process_userland_query$fn__48417.invoke(process_userland_query.clj:135)"
    "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46440.invoke(catch_exceptions.clj:173)"
    "query_processor.reducible$async_qp$qp_STAR___37978$thunk__37979.invoke(reducible.clj:103)"
    "query_processor.reducible$async_qp$qp_STAR___37978.invoke(reducible.clj:109)"
    "query_processor.reducible$sync_qp$qp_STAR___37987$fn__37990.invoke(reducible.clj:135)"
    "query_processor.reducible$sync_qp$qp_STAR___37987.invoke(reducible.clj:134)"
    "query_processor$process_userland_query.invokeStatic(query_processor.clj:239)"
    "query_processor$process_userland_query.doInvoke(query_processor.clj:235)"
    "query_processor$fn__49464$process_query_and_save_execution_BANG___49473$fn__49476.invoke(query_processor.clj:251)"
    "query_processor$fn__49464$process_query_and_save_execution_BANG___49473.invoke(query_processor.clj:243)"
    "query_processor$fn__49508$process_query_and_save_with_max_results_constraints_BANG___49517$fn__49520.invoke(query_processor.clj:263)"
    "query_processor$fn__49508$process_query_and_save_with_max_results_constraints_BANG___49517.invoke(query_processor.clj:256)"
    "api.dataset$run_query_async$fn__55721.invoke(dataset.clj:56)"
    "query_processor.streaming$streaming_response_STAR_$fn__55700$fn__55701.invoke(streaming.clj:72)"
    "query_processor.streaming$streaming_response_STAR_$fn__55700.invoke(streaming.clj:71)"
    "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
    "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
    "async.streaming_response$do_f_async$fn__16071.invoke(streaming_response.clj:84)"],
   :error_type :invalid-query,
   :ex-data
   {:type :invalid-query,
    :sql
    "-- Metabase:: userID: 1 queryType: native queryHash: ca55017a5d058bd03e5413191b7b38d1424c531f3bf128a4ef9c69eca27a67ec\nselect \n    count(*)\nfrom `pro.h_clubs_ltv`\nwhere 1=1\n    and date_trunc(`pro.h_clubs_ltv`.`month`, day) BETWEEN ? AND ?",
    :parameters [#t "2021-02-01" #t "2021-05-31"]}}],
 :error_type :invalid-query,
 :json_query
 {:database 18,
  :native
  {:template-tags
   {:month
    {:id "7cade1ac-4c3e-a798-6d3f-0c019f3f60e7",
     :name "month",
     :display-name "Month",
     :type "dimension",
     :dimension ["field" 117562 nil],
     :widget-type "date/all-options",
     :default nil}},
   :query "select \n    count(*)\nfrom `pro.h_clubs_ltv`\nwhere 1=1\n    [[and {{month}}]]"},
  :type "native",
  :parameters [{:type "date/all-options", :value "past3months~", :target ["dimension" ["template-tag" "month"]]}],
  :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
 :status :failed,
 :class clojure.lang.ExceptionInfo,
 :stacktrace
 ["--> driver.google$execute_no_auto_retry.invokeStatic(google.clj:41)"
  "driver.google$execute_no_auto_retry.invoke(google.clj:33)"
  "driver.google$execute.invokeStatic(google.clj:55)"
  "driver.google$execute.invoke(google.clj:45)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:216)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:205)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:203)"
  "driver.bigquery$process_native_STAR_$thunk__1576.invoke(bigquery.clj:277)"
  "driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:279)"
  "driver.bigquery$process_native_STAR_.invoke(bigquery.clj:272)"
  "driver.bigquery$eval1580$fn__1582.invoke(bigquery.clj:299)"
  "query_processor.context$executef.invokeStatic(context.clj:59)"
  "query_processor.context$executef.invoke(context.clj:48)"
  "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
  "query_processor.context.default$default_runf.invoke(default.clj:66)"
  "query_processor.context$runf.invokeStatic(context.clj:45)"
  "query_processor.context$runf.invoke(context.clj:39)"
  "query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
  "query_processor.reducible$pivot.invoke(reducible.clj:31)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__47242.invoke(mbql_to_native.clj:25)"
  "query_processor.middleware.check_features$check_features$fn__46484.invoke(check_features.clj:39)"
  "query_processor.middleware.limit$limit$fn__47228.invoke(limit.clj:37)"
  "query_processor.middleware.cache$maybe_return_cached_results$fn__45936.invoke(cache.clj:211)"
  "query_processor.middleware.optimize_temporal_filters$optimize_temporal_filters$fn__47488.invoke(optimize_temporal_filters.clj:204)"
  "query_processor.middleware.validate_temporal_bucketing$validate_temporal_bucketing$fn__49418.invoke(validate_temporal_bucketing.clj:50)"
  "query_processor.middleware.auto_parse_filter_values$auto_parse_filter_values$fn__45055.invoke(auto_parse_filter_values.clj:43)"
  "query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__41400.invoke(wrap_value_literals.clj:161)"
  "query_processor.middleware.annotate$add_column_info$fn__41275.invoke(annotate.clj:598)"
  "query_processor.middleware.permissions$check_query_permissions$fn__46356.invoke(permissions.clj:81)"
  "query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__48346.invoke(pre_alias_aggregations.clj:40)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__46557.invoke(cumulative_aggregations.clj:60)"
  "query_processor.middleware.resolve_joined_fields$resolve_joined_fields$fn__48645.invoke(resolve_joined_fields.clj:102)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__48958.invoke(resolve_joins.clj:171)"
  "query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__44631.invoke(add_implicit_joins.clj:190)"
  "query_processor.middleware.large_int_id$convert_id_to_string$fn__47192.invoke(large_int_id.clj:59)"
  "query_processor.middleware.format_rows$format_rows$fn__47173.invoke(format_rows.clj:74)"
  "query_processor.middleware.desugar$desugar$fn__46623.invoke(desugar.clj:21)"
  "query_processor.middleware.binning$update_binning_strategy$fn__45442.invoke(binning.clj:227)"
  "query_processor.middleware.resolve_fields$resolve_fields$fn__46159.invoke(resolve_fields.clj:34)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__44280.invoke(add_dimension_projections.clj:312)"
  "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__44509.invoke(add_implicit_clauses.clj:147)"
  "query_processor.middleware.upgrade_field_literals$upgrade_field_literals$fn__49367.invoke(upgrade_field_literals.clj:40)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__44794.invoke(add_source_metadata.clj:123)"
  "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48520.invoke(reconcile_breakout_and_order_by_bucketing.clj:100)"
  "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45002.invoke(auto_bucket_datetimes.clj:147)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46206.invoke(resolve_source_table.clj:45)"
  "query_processor.middleware.parameters$substitute_parameters$fn__48328.invoke(parameters.clj:111)"
  "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46258.invoke(resolve_referenced.clj:79)"
  "query_processor.middleware.expand_macros$expand_macros$fn__46879.invoke(expand_macros.clj:155)"
  "query_processor.middleware.add_timezone_info$add_timezone_info$fn__44803.invoke(add_timezone_info.clj:15)"
  "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49320.invoke(splice_params_in_response.clj:32)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531$fn__48535.invoke(resolve_database_and_driver.clj:31)"
  "driver$do_with_driver.invokeStatic(driver.clj:60)"
  "driver$do_with_driver.invoke(driver.clj:56)"
  "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48531.invoke(resolve_database_and_driver.clj:25)"
  "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47119.invoke(fetch_source_query.clj:274)"
  "query_processor.middleware.store$initialize_store$fn__49329$fn__49330.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:44)"
  "query_processor.store$do_with_store.invoke(store.clj:38)"
  "query_processor.middleware.store$initialize_store$fn__49329.invoke(store.clj:10)"
  "query_processor.middleware.validate$validate_query$fn__49374.invoke(validate.clj:10)"
  "query_processor.middleware.normalize_query$normalize$fn__47255.invoke(normalize_query.clj:22)"
  "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__44649.invoke(add_rows_truncated.clj:35)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49305.invoke(results_metadata.clj:147)"
  "query_processor.middleware.constraints$add_default_userland_constraints$fn__46500.invoke(constraints.clj:42)"
  "query_processor.middleware.process_userland_query$process_userland_query$fn__48417.invoke(process_userland_query.clj:135)"
  "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46440.invoke(catch_exceptions.clj:173)"
  "query_processor.reducible$async_qp$qp_STAR___37978$thunk__37979.invoke(reducible.clj:103)"
  "query_processor.reducible$async_qp$qp_STAR___37978.invoke(reducible.clj:109)"
  "query_processor.reducible$sync_qp$qp_STAR___37987$fn__37990.invoke(reducible.clj:135)"
  "query_processor.reducible$sync_qp$qp_STAR___37987.invoke(reducible.clj:134)"
  "query_processor$process_userland_query.invokeStatic(query_processor.clj:239)"
  "query_processor$process_userland_query.doInvoke(query_processor.clj:235)"
  "query_processor$fn__49464$process_query_and_save_execution_BANG___49473$fn__49476.invoke(query_processor.clj:251)"
  "query_processor$fn__49464$process_query_and_save_execution_BANG___49473.invoke(query_processor.clj:243)"
  "query_processor$fn__49508$process_query_and_save_with_max_results_constraints_BANG___49517$fn__49520.invoke(query_processor.clj:263)"
  "query_processor$fn__49508$process_query_and_save_with_max_results_constraints_BANG___49517.invoke(query_processor.clj:256)"
  "api.dataset$run_query_async$fn__55721.invoke(dataset.clj:56)"
  "query_processor.streaming$streaming_response_STAR_$fn__55700$fn__55701.invoke(streaming.clj:72)"
  "query_processor.streaming$streaming_response_STAR_$fn__55700.invoke(streaming.clj:71)"
  "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:65)"
  "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:63)"
  "async.streaming_response$do_f_async$fn__16071.invoke(streaming_response.clj:84)"],
 :context :ad-hoc,
 :error
 "No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:52]",
 :row_count 0,
 :running_time 0,
 :ex-data
 {"code" 400,
  "errors"
  [{"domain" "global", "location" "q", "locationType" "parameter", "message" "No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:52]", "reason" "invalidQuery"}],
  "message"
  "No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:52]",
  "status" "INVALID_ARGUMENT"},
 :data {:rows [], :cols []}}

49
491910×1546 240 KB

2

Hi @marco
Please post "Diagnostic Info" from Admin > Troubleshooting.
And can you post the output of /api/field/117562 ?
I don't think I fully understand if you're having problems with existing questions, new question or ...

3

Ooops! I forgot about the diagnostic info, sorry.

I'm having troubles with a new question in native sql, whereas already existing questions are running fine.

{
  "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/90.0.4430.93 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.11+9",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.11",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.11+9",
    "os.name": "Linux",
    "os.version": "4.9.0-12-amd64",
    "user.language": "en",
    "user.timezone": "Europe/Berlin"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "mysql",
      "bigquery"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "9.6.17"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-04-27",
      "tag": "v0.39.1",
      "branch": "release-x.39.x",
      "hash": "6beba48"
    },
    "settings": {
      "report-timezone": "Europe/Rome"
    }
  }
}

[e6f8c727-b0fb-4329-ad31-497d03a3e754] 2021-05-04T14:49:39+02:00 DEBUG metabase.server.middleware.log GET /api/field/117562/values 200 11.4 ms (4 DB calls) App DB connections: 1/13 Jetty threads: 6/50 (10 idle, 0 queued) (68 total active threads) Queries in flight: 0 (0 queued)

[e6f8c727-b0fb-4329-ad31-497d03a3e754] 2021-05-04T14:49:39+02:00 DEBUG metabase.server.middleware.log GET /api/field/117562 200 16.1 ms (6 DB calls) App DB connections: 0/13 Jetty threads: 5/50 (10 idle, 0 queued) (68 total active threads) Queries in flight: 0 (0 queued)

[e6f8c727-b0fb-4329-ad31-497d03a3e754] 2021-05-04T14:49:52+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null

4

@marco I need you to go to the address /api/field/117562 and post the output, since that contains a lot of information about the field being used as the Field Filter, and since Metabase generates the query for Field Filters, those details are important for me figuring out how to reproduce.

It kinda looks like the opposite of this issue, which was fixed in 0.39.0 before release, but I'm sure we missed something.
https://github.com/metabase/metabase/issues/15376

5

ah! I didn't know I could go to that address... here is the result:

{"description":null,"database_type":"TIMESTAMP","semantic_type":"type/CreationTimestamp","table_id":6521,"coercion_strategy":null,"table":{"description":null,"entity_type":"entity/GenericTable","schema":null,"db":{"description":null,"features":["full-join","basic-aggregations","standard-deviation-aggregations","expression-aggregations","foreign-keys","right-join","left-join","native-parameters","nested-queries","regex","case-sensitivity-string-filter-options","binning","inner-join","advanced-math-expressions"],"cache_field_values_schedule":"0 0 3 * * ? *","timezone":"UTC","auto_run_queries":false,"metadata_sync_schedule":"0 44 * * * ? *","name":"BigQuery Playtomic","caveats":null,"is_full_sync":true,"updated_at":"2021-05-04T13:16:10.005916Z","details":{"use-service-account":null,"dataset-id":"pro","service-account-json":"**MetabasePass**","use-jvm-timezone":false,"let-user-control-scheduling":false,"project-id":"playtomic-bi"},"is_sample":false,"id":18,"is_on_demand":false,"options":null,"engine":"bigquery","refingerprint":true,"created_at":"2020-09-22T10:58:01.210779Z","points_of_interest":null},"show_in_getting_started":false,"name":"h_clubs_ltv","caveats":null,"updated_at":"2021-04-12T16:44:15.992454Z","entity_name":null,"active":true,"id":6521,"db_id":18,"visibility_type":null,"field_order":"database","display_name":"h_clubs_ltv","created_at":"2021-03-26T18:06:31.630295Z","points_of_interest":null},"name":"month","fingerprint_version":5,"has_field_values":"none","settings":null,"caveats":null,"fk_target_field_id":null,"dimensions":[],"updated_at":"2021-04-28T13:44:49.099563Z","custom_position":0,"effective_type":"type/Date","active":true,"parent_id":null,"id":117562,"last_analyzed":"2021-03-26T18:45:54.44923Z","position":0,"visibility_type":"normal","preview_display":true,"display_name":"month","database_position":0,"name_field":null,"fingerprint":{"global":{"distinct-count":15,"nil%":0.0},"type":{"type/DateTime":{"earliest":"2017-07-01T00:00:00Z","latest":"2021-03-01T00:00:00Z"}}},"created_at":"2021-03-26T18:07:38.796356Z","base_type":"type/DateTimeWithLocalTZ","points_of_interest":null}

6

@marco Okay, so I think I know what's wrong, but I don't know your data, so I cannot say for sure.
If you check one of your other fields, which are working, then notice effective_type

From the fingerprint, it looks like the database column is a timestamp, but all the values are just dates.

I would think if you go to your application database (make a backup first!) and run this, then the question will work with that field (would likely require a browser refresh).

update metabase_field
set effective_type='type/DateTimeWithLocalTZ'
where id=117562 and effective_type='type/Date'

I would love to figure out what's going on, since I have now seen two related issues to this, which I have not been able to reproduce during upgrades, and I think that the Field Type might have been configured incorrectly and during upgrade, then it messes things up.
https://github.com/metabase/metabase/issues/15901
https://github.com/metabase/metabase/issues/15743

7

It worked perfectly!
It was exactly set as effective_type='type/Date' and updating it to effective_type='type/DateTimeWithLocalTZ' made it work.

Thanks for the prompt help, very much appreciated!!

8

@marco Perfect. Annoying that I don't know how to reproduce it, but I have created an issue you can follow:
https://github.com/metabase/metabase/issues/15913