To follow up, It seems it’s not setting anymore the timezone:
`Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.log ::
QUERY:
{:database 2,
:type “native”,
:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n [[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}}},
:parameters
[{:type “date/single”, :target [“variable” [“template-tag” “min_date”]], :value “2017-01-01”} {:type “date/single”, :target [“variable” [“template-tag” “max_date”]], :value “2017-05-31”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:query-hash [-30, -11, -28, -71, -92, -94, -13, 14, -24, -95, 114, -7, 97, -15, -52, -11, 56, 15, 79, 106, -119, -99, -11, 71, -106, 1, 103, -13, -29, 92, -61, 110],
:query-type “native”}}
Mar 24 14:10:46 DEBUG metabase.query-processor.sql-parameters :: PARAM INFO:
({:param-key :min_date,
:original-snippet “[[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]”,
:variable-snippet “{{min_date}}”,
:optional-snippet “AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’”,
:replacement-snippet “AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’”,
:prepared-statement-args (#inst “2017-01-01T00:00:00.000000000-00:00”)})
Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.parameters ::
PARAMS/SUBSTITUTED:
{:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:params (#inst “2017-01-01T00:00:00.000000000-00:00”)}}
Mar 24 14:10:46 DEBUG metabase.query-processor.permissions :: Permissions Check : Does user have permissions for /db/2/native/?
Mar 24 14:10:46 DEBUG metabase.query-processor.permissions :: Permissions Check : Yes because user has permissions for /.
Mar 24 14:10:46 DEBUG metabase.query-processor.middleware.mbql-to-native :: NATIVE FORM:
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n AND fbh.field_booking_insert_date between ?::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}},
:params (#inst “2017-01-01T00:00:00.000000000-00:00”)}
Mar 24 14:10:46 DEBUG metabase.driver.generic-sql.query-processor :: [“UPDATE pg_settings SET setting = ? WHERE name ILIKE ‘timezone’;” “Europe/Berlin”]
Mar 24 14:10:46 ERROR metabase.driver.generic-sql.query-processor :: Failed to set timezone:
PSQLException:
Message: ERROR: syntax error at or near “{”
Position: 1935
SQLState: 42601
Error Code: 0
Mar 24 14:10:46 ERROR metabase.driver.generic-sql.query-processor :: nil
Mar 24 14:10:46 WARN metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error “ERROR: syntax error at or near “{”\n Position: 1935”,
:stacktrace
[“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:323)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:319)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:365)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:361)”
“driver$fn__19566$G__19559__19573.invoke(driver.clj:43)”
“query_processor$execute_query.invokeStatic(query_processor.clj:39)”
“query_processor$execute_query.invoke(query_processor.clj:33)”
“query_processor.middleware.mbql_to_native$mbql__GT_native$fn__23125.invoke(mbql_to_native.clj:28)”
“query_processor.middleware.annotate_and_sort$annotate_and_sort$fn__21793.invoke(annotate_and_sort.clj:37)”
“query_processor.middleware.limit$limit$fn__23097.invoke(limit.clj:14)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__22958.invoke(cumulative_aggregations.clj:46)”
“query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__22958.invoke(cumulative_aggregations.clj:46)”
“query_processor.middleware.format_rows$format_rows$fn__23088.invoke(format_rows.clj:21)”
“query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__21473.invoke(add_row_count_and_status.clj:14)”
“query_processor.middleware.driver_specific$process_query_in_context$fn__22978.invoke(driver_specific.clj:12)”
“query_processor.middleware.resolve_driver$resolve_driver$fn__24064.invoke(resolve_driver.clj:14)”
“query_processor.middleware.catch_exceptions$catch_exceptions$fn__22900.invoke(catch_exceptions.clj:51)”
“query_processor$process_query.invokeStatic(query_processor.clj:64)”
“query_processor$process_query.invoke(query_processor.clj:59)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:180)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:175)”
“query_processor$fn__24095$dataset_query__24100$fn__24101.invoke(query_processor.clj:212)”
“query_processor$fn__24095$dataset_query__24100.invoke(query_processor.clj:199)”
“api.dataset$fn__24866$fn__24869.invoke(dataset.clj:36)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)”
“api.dataset$fn__24866.invokeStatic(dataset.clj:30)”
“api.dataset$fn__24866.invoke(dataset.clj:30)”
“middleware$enforce_authentication$fn__34556.invoke(middleware.clj:119)”
“api.routes$fn__34682.invokeStatic(routes.clj:57)”
“api.routes$fn__34682.invoke(routes.clj:57)”
“routes$fn__35755.invokeStatic(routes.clj:44)”
“routes$fn__35755.invoke(routes.clj:44)”
“middleware$log_api_call$fn__34655$fn__34657.invoke(middleware.clj:331)”
“middleware$log_api_call$fn__34655.invoke(middleware.clj:330)”
“middleware$add_security_headers$fn__34605.invoke(middleware.clj:246)”
“middleware$bind_current_user$fn__34560.invoke(middleware.clj:139)”
“middleware$maybe_set_site_url$fn__34609.invoke(middleware.clj:268)”],
:query
{:type “native”,
:native
{:query
“select\n u.id AS id_utente,\n lower(u.email) AS email,\n initcap(u.firstname) AS nome,\n initcap(u.lastname) AS cognome,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 1 THEN 1 ELSE 0 END) AS tot_prenotazioni,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 1 THEN 1 ELSE 0 END) AS paga_al_campo,\n SUM(CASE WHEN fbh.payment_transaction IS NULL AND action_id = 3 THEN 1 ELSE 0 END) AS annullate_al_campo,\n SUM(CASE WHEN fbh.payment_transaction > 0 THEN 1 ELSE 0 END) AS promo_acquistate,\n SUM(CASE WHEN fbh.payment_transaction > 0 AND action_id = 3 THEN 1 ELSE 0 END) AS promo_cancellate,\n MIN(fbh.field_booking_insert_date) AS prima_prenotazione,\n MAX(fbh.field_booking_insert_date) AS ultima_prenotazione,\n MAX(fbh.date) AS ultima_da_giocare,\n u.active AS attivo,\n u.disabled AS disattivato,\n u.verified AS verificato,\n u.member_since AS membro_dal,\n u.first_activation_date AS attivo_dal,\n u.verified_by_mandrill AS verificato_da_mandrill,\n u.password,\n u.confirm_code\nfrom users as u\nleft join fields_bookings_histories as fbh on fbh.user_id = u.id\nLEFT JOIN fields AS fi ON fi.id = fbh.field_id\nLEFT JOIN facilities AS fa ON fa.id = fi.facility_id\nLEFT JOIN cities AS c ON c.id = fa.city_id\nLEFT JOIN province AS p ON p.id = c.province_id\nWHERE \n u.disabled = false\n and fbh.booked_online = TRUE\n and fbh.is_pending = FALSE\n and u.active = true\n and u.role_id = 1 /* creata da utente */\n and fbh.action_id != 2\n and (u.password != ‘’ OR u.facebook_id IS NOT NULL OR u.sportilia_id IS NOT NULL OR u.google_plus_id IS NOT NULL)\n and fa.id NOT IN (58, 671)\n [[AND fbh.field_booking_insert_date between {{min_date}}::timestamptz and {{max_date}}::timestamptz + interval ‘1 day’]]\nGROUP BY u.id\nORDER BY tot_prenotazioni DESC;”,
:template_tags
{:min_date {:id “b2d5ce0d-4ebb-49d9-9893-2f39c0a4e7e9”, :name “min_date”, :display_name “Min date”, :type “date”, :required true, :default “2016-01-01”},
:max_date {:id “5858100d-8533-5360-090c-80e6d59162b0”, :name “max_date”, :display_name “Max date”, :type “date”, :required true, :default “2016-12-31”}}},
:parameters
[{:type “date/single”, :target [“variable” [“template-tag” “min_date”]], :value “2017-01-01”} {:type “date/single”, :target [“variable” [“template-tag” “max_date”]], :value “2017-05-31”}],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info
{:executed-by 1,
:context :ad-hoc,
:query-hash [-30, -11, -28, -71, -92, -94, -13, 14, -24, -95, 114, -7, 97, -15, -52, -11, 56, 15, 79, 106, -119, -99, -11, 71, -106, 1, 103, -13, -29, 92, -61, 110],
:query-type “native”}},
:expanded-query nil}
Mar 24 14:10:47 WARN metabase.query-processor :: Query failure: ERROR: syntax error at or near “{”
Position: 1935
[“query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:149)”
“query_processor$assert_query_status_successful.invoke(query_processor.clj:142)”
“query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:181)”
“query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:175)”
“query_processor$fn__24095$dataset_query__24100$fn__24101.invoke(query_processor.clj:212)”
“query_processor$fn__24095$dataset_query__24100.invoke(query_processor.clj:199)”
“api.dataset$fn__24866$fn__24869.invoke(dataset.clj:36)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:229)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:224)”
“api.dataset$fn__24866.invokeStatic(dataset.clj:30)”
“api.dataset$fn__24866.invoke(dataset.clj:30)”
“middleware$enforce_authentication$fn__34556.invoke(middleware.clj:119)”
“api.routes$fn__34682.invokeStatic(routes.clj:57)”
“api.routes$fn__34682.invoke(routes.clj:57)”
“routes$fn__35755.invokeStatic(routes.clj:44)”
“routes$fn__35755.invoke(routes.clj:44)”
“middleware$log_api_call$fn__34655$fn__34657.invoke(middleware.clj:331)”
“middleware$log_api_call$fn__34655.invoke(middleware.clj:330)”
“middleware$add_security_headers$fn__34605.invoke(middleware.clj:246)”
“middleware$bind_current_user$fn__34560.invoke(middleware.clj:139)”
“middleware$maybe_set_site_url$fn__34609.invoke(middleware.clj:268)”]
Mar 24 14:10:47 DEBUG metabase.middleware :: POST /api/dataset 200 (1 s) (5 DB calls)`