metabase / metabase/metabase

Allow for columns as arguments for convertTimezone (aka dynamic timezone conversion)

Open
#33,782 11 comments 20 reactions 0 assignees View on GitHub
.Backend .Frontend Misc/Timezones Querying/Notebook Querying/Notebook/Custom Column Type:New Feature
Dominant language
Clojure
Stars
49.3k
Forks
6.8k
Avg merge
1d 13h
Merged PRs (30d)
653

Description

### Describe the bug

When you try to use the convertTimezone function and instead of passing a static target (i.e "UTC") and you pass a column which will have timezone information the Query processor doesn't know how to handle it ... Running the preview doesn't even show the custom column:

image

And clicking visualize throws an error

### To Reproduce

This is a list of supported timezones [dates_.csv](https://github.com/metabase/metabase/files/12547155/dates_.csv) which you can use for testing

1. Upload CSV -> And upload the above CSV
2. Click on New Question -> Dates -> Custom Column -> `convertTimezone(now, [Time Zone Config])`
3. Click Preview and notice the column doesn't show:

image

4. Click Visualize and for some reason the query processor removes the custom column when you go back to the editor
5. Click on view SQL and you will see an error:

image

### Expected behavior

Since we don't support this to work with columns. Simply don't allow the input of a column for this function or improve the error message

### Logs

Input to resolve-card-id-source-tables* does not match schema:

 [(named {:query {:expressions {"test" (named [nil nil (named (named (not (#{"Asia/Rangoon" "Mexico/BajaNorte" "Europe/Amsterdam" "America/Montevideo" "Africa/Kinshasa" "Asia/Sakhalin" "America/Santarem" "Europe/Tiraspol" "Africa/Ouagadougou" "Asia/Amman" "Europe/Tallinn" "Africa/Blantyre" "America/Nipigon" "America/Jamaica" "Africa/El_Aaiun" "Australia/LHI" "America/Costa_Rica" "Canada/Eastern" "Asia/Kuala_Lumpur" "America/Cordoba" "Asia/Kuwait" "SystemV/YST9YDT" "Asia/Tokyo" "America/Maceio" "Europe/Brussels" "America/Nome" "Australia/Broken_Hill" "Asia/Chita" "Universal" "Africa/Timbuktu" "Africa/Casablanca" "Europe/Isle_of_Man" "America/Ensenada" "America/Argentina/San_Luis" "America/St_Johns" "Europe/Berlin" "Africa/Lome" "America/North_Dakota/Center" "Asia/Aqtobe" "Antarctica/Macquarie" "Asia/Seoul" "Chile/EasterIsland" "US/Hawaii" "America/Punta_Arenas" "Indian/Christmas" "Atlantic/Madeira" "Asia/Barnaul" "SystemV/PST8PDT" "Asia/Colombo" "America/Argentina/Mendoza" "Asia/Novokuznetsk" "America/Indiana/Vevay" "America/Argentina/Rio_Gallegos" "Etc/GMT" "US/Aleutian" "America/Fortaleza" "Australia/South" "Europe/Uzhgorod" "Asia/Beirut" "Australia/Yancowinna" "Africa/Sao_Tome" "GMT" "Cuba" "Asia/Dubai" "Europe/Stockholm" "Portugal" "America/Boise" "America/Scoresbysund" "Antarctica/Syowa" "Indian/Maldives" "Europe/Simferopol" "Pacific/Efate" "Asia/Qatar" "GMT0" "Europe/Jersey" "Pacific/Honolulu" "America/Tegucigalpa" "Atlantic/South_Georgia" "Asia/Hong_Kong" "Pacific/Pohnpei" "Pacific/Norfolk" "America/Santiago" "America/Indiana/Knox" "Etc/GMT0" "Australia/Brisbane" "America/Anguilla" "Asia/Hovd" "Asia/Jakarta" "Atlantic/Stanley" "America/Jujuy" "Indian/Comoro" "America/Sao_Paulo" "Asia/Macao" "Etc/GMT+12" "Asia/Macau" "America/Menominee" "Asia/Bahrain" "SystemV/EST5" "Asia/Katmandu" "America/Montreal" "Atlantic/St_Helena" "MET" "Africa/Tunis" "Africa/Lusaka" "Etc/GMT+2" "CST6CDT" "Pacific/Easter" "Asia/Ashkhabad" "Africa/Cairo" "Etc/GMT-10" "Pacific/Chuuk" "US/Eastern" "America/Montserrat" "Pacific/Tarawa" "Antarctica/Troll" "America/Resolute" "Pacific/Saipan" "Asia/Thimbu" "Asia/Muscat" "Asia/Saigon" "Hongkong" "Europe/Zaporozhye" "Asia/Aqtau" "Antarctica/Davis" "Asia/Hebron" "Asia/Kuching" "Pacific/Tahiti" "America/Argentina/Jujuy" "Etc/GMT+1" "Atlantic/Faroe" "Asia/Vladivostok" "Europe/Oslo" "Australia/Tasmania" "Asia/Oral" "Arctic/Longyearbyen" "America/Indiana/Marengo" "America/Rainy_River" "America/Barbados" "Indian/Chagos" "Indian/Cocos" "Australia/Victoria" "Africa/Harare" "America/Aruba" "America/El_Salvador" "America/Porto_Velho" "Etc/GMT-4" "Navajo" "Australia/Hobart" "America/Ojinaga" "Asia/Jayapura" "Etc/Universal" "America/Goose_Bay" "America/Panama" "Africa/Dar_es_Salaam" "America/Mendoza" "Asia/Tehran" "America/Blanc-Sablon" "PST8PDT" "Pacific/Johnston" "Etc/GMT-12" "America/Noronha" "America/St_Kitts" "America/Ciudad_Juarez" "Etc/GMT+10" "Pacific/Enderbury" "Australia/Queensland" "Etc/GMT+5" "Pacific/Ponape" "Antarctica/McMurdo" "Africa/Ceuta" "Asia/Baku" "Europe/Sofia" "CET" "America/Pangnirtung" "America/Monterrey" "Asia/Pontianak" "America/Moncton" "Europe/Belfast" "America/Dominica" "America/Swift_Current" "Eire" "America/Argentina/Salta" "America/Sitka" "America/Creston" "America/St_Vincent" "Europe/Andorra" "Pacific/Wallis" "Asia/Ujung_Pandang" "Africa/Niamey" "MST7MDT" "Asia/Manila" "Asia/Shanghai" "Etc/GMT+4" "Europe/London" "Europe/Vienna" "Jamaica" "Africa/Kigali" "Etc/GMT-0" "Libya" "Pacific/Auckland" "EET" "Europe/Luxembourg" "Europe/Bucharest" "Asia/Khandyga" "Asia/Kolkata" "America/Louisville" "America/Atka" "Etc/UTC" "Pacific/Gambier" "Asia/Dushanbe" "Asia/Choibalsan" "Asia/Phnom_Penh" "Pacific/Guadalcanal" "US/Arizona" "Pacific/Yap" "Europe/Tirane" "Africa/Maseru" "America/Cambridge_Bay" "America/Denver" "America/Thule" "America/St_Barthelemy" "Etc/GMT+3" "Asia/Srednekolymsk" "America/Recife" "Europe/Belgrade" "Europe/Kiev" "Europe/Zagreb" "Asia/Chungking" "Canada/Mountain" "Pacific/Samoa" "Africa/Douala" "America/Argentina/La_Rioja" "Europe/Kaliningrad" "Asia/Gaza" "US/Alaska" "SystemV/MST7MDT" "Asia/Yerevan" "Europe/Warsaw" "Australia/Currie" "Africa/Nouakchott" "Egypt" "Africa/Brazzaville" "Asia/Yekaterinburg" "America/Belize" "Atlantic/Azores" "Etc/GMT-9" "America/Argentina/San_Juan" "US/Michigan" "Asia/Kamchatka" "Asia/Qostanay" "America/Knox_IN" "Australia/Adelaide" "ROK" "America/Rankin_Inlet" "Etc/UCT" "Pacific/Kwajalein" "Asia/Yangon" "Mexico/BajaSur" "Canada/Pacific" "SystemV/YST9" "America/Tortola" "Japan" "Europe/Volgograd" "America/Inuvik" "UCT" "Asia/Pyongyang" "Canada/Central" "Asia/Vientiane" "Africa/Lubumbashi" "W-SU" "America/Dawson" "Asia/Ulan_Bator" "Asia/Yakutsk" "Europe/San_Marino" "America/Porto_Acre" "Pacific/Rarotonga" "GB" "America/Curacao" "America/La_Paz" "Europe/Astrakhan" "Europe/Prague" "America/Chihuahua" "Antarctica/Rothera" "America/Los_Angeles" "America/Lima" "America/Caracas" "Africa/Dakar" "Kwajalein" "Etc/GMT-14" "America/Buenos_Aires" "America/Virgin" "Asia/Nicosia" "America/Indiana/Petersburg" "Etc/GMT-2" "America/Tijuana" "Pacific/Kanton" "Europe/Guernsey" "Africa/Conakry" "Europe/Dublin" "Europe/Helsinki" "Africa/Abidjan" "America/Campo_Grande" "America/Guatemala" "Pacific/Tongatapu" "Africa/Monrovia" "Atlantic/Bermuda" "Asia/Almaty" "Asia/Bishkek" "SystemV/AST4" "Indian/Mayotte" "America/Martinique" "America/Shiprock" "Antarctica/DumontDUrville" "SystemV/AST4ADT" "America/Danmarkshavn" "Asia/Tel_Aviv" "Europe/Athens" "Brazil/Acre" "Europe/Sarajevo" "NZ" "America/Yellowknife" "Africa/Freetown" "Asia/Tbilisi" "Canada/Yukon" "Pacific/Pago_Pago" "America/Argentina/Ushuaia" "America/Metlakatla" "America/Boa_Vista" "Pacific/Nauru" "Asia/Brunei" "Pacific/Palau" "Asia/Dili" "Etc/GMT+8" "Etc/Zulu" "America/Port-au-Prince" "Pacific/Chatham" "America/Indiana/Indianapolis" "Atlantic/Reykjavik" "Iran" "America/Kentucky/Louisville" "Africa/Asmera" "Australia/Sydney" "America/Indiana/Vincennes" "America/Bogota" "US/Indiana-Starke" "America/Phoenix" "Africa/Djibouti" "Etc/GMT-3" "Africa/Banjul" "SystemV/PST8" "Brazil/East" "Asia/Atyrau" "America/Cayenne" "America/Santa_Isabel" "America/Grand_Turk" "Europe/Vilnius" "Europe/Kyiv" "America/Hermosillo" "America/Yakutat" "Africa/Kampala" "Europe/Copenhagen" "Asia/Novosibirsk" "Europe/Monaco" "Brazil/West" "America/Nuuk" "EST5EDT" "America/Chicago" "Etc/GMT-1" "America/Godthab" "Canada/Atlantic" "Asia/Singapore" "Etc/GMT-8" "America/Lower_Princes" "Africa/Accra" "America/Asuncion" "America/Thunder_Bay" "America/Bahia" "Pacific/Majuro" "Etc/GMT+9" "Asia/Tomsk" "Africa/Mbabane" "Antarctica/Casey" "Pacific/Fakaofo" "Africa/Addis_Ababa" "America/Nassau" "Asia/Makassar" "Europe/Nicosia" "Australia/West" "America/Whitehorse" "Asia/Urumqi" "Asia/Famagusta" "US/Samoa" "Asia/Samarkand" "Asia/Calcutta" "Antarctica/Vostok" "Pacific/Niue" "Australia/Perth" "Africa/Bissau" "Asia/Thimphu" "America/Antigua" "Africa/Khartoum" "America/Guyana" "America/Puerto_Rico" "Indian/Kerguelen" "Pacific/Apia" "America/Indiana/Winamac" "US/East-Indiana" "Pacific/Galapagos" "Europe/Skopje" "GB-Eire" "Europe/Istanbul" "Chile/Continental" "Asia/Jerusalem" "Asia/Magadan" "UTC" "America/North_Dakota/Beulah" "America/Guadeloupe" "America/St_Lucia" "America/Mexico_City" "Africa/Johannesburg" "Africa/Bamako" "Etc/GMT+0" "America/Toronto" "Etc/GMT-7" "America/Managua" "America/Belem" "Europe/Lisbon" "Asia/Kashgar" "America/Juneau" "America/Fort_Wayne" "US/Mountain" "Etc/GMT+6" "Europe/Minsk" "America/Indiana/Tell_City" "Australia/North" "Europe/Moscow" "Pacific/Kosrae" "Europe/Ulyanovsk" "Africa/Lagos" "Asia/Harbin" "Iceland" "Mexico/General" "America/Guayaquil" "America/Argentina/Cordoba" "Africa/Windhoek" "Europe/Rome" "Asia/Krasnoyarsk" "America/Glace_Bay" "Asia/Karachi" "SystemV/HST10" "Europe/Busingen" "Asia/Aden" "America/Mazatlan" "America/Winnipeg" "Europe/Chisinau" "America/Argentina/Buenos_Aires" "Europe/Riga" "Pacific/Pitcairn" "Turkey" "Pacific/Guam" "Indian/Reunion" "Pacific/Bougainville" "Africa/Malabo" "America/New_York" "SystemV/EST5EDT" "America/Coral_Harbour" "Africa/Tripoli" "Asia/Bangkok" "America/Atikokan" "America/Detroit" "Poland" "Etc/GMT+11" "Asia/Ashgabat" "America/Edmonton" "Europe/Podgorica" "Europe/Saratov" "Greenwich" "Australia/ACT" "Asia/Damascus" "Africa/Bujumbura" "Australia/Lord_Howe" "Singapore" "Africa/Bangui" "America/Kentucky/Monticello" "Europe/Mariehamn" "SystemV/MST7" "Australia/Eucla" "Canada/Newfoundland" "America/North_Dakota/New_Salem" "Africa/Juba" "Africa/Gaborone" "Etc/GMT-5" "Canada/Saskatchewan" "Europe/Ljubljana" "Australia/Lindeman" "Africa/Mogadishu" "America/St_Thomas" "America/Havana" "Asia/Ulaanbaatar" "Europe/Vaduz" "America/Anchorage" "America/Iqaluit" "Asia/Riyadh" "America/Vancouver" "US/Pacific" "America/Grenada" "America/Rio_Branco" "Europe/Bratislava" "Pacific/Fiji" "Asia/Omsk" "America/Catamarca" "Pacific/Funafuti" "SystemV/CST6" "America/Merida" "Asia/Istanbul" "America/Cayman" "Etc/GMT+7" "Etc/GMT-6" "Zulu" "Pacific/Noumea" "Indian/Mahe" "Asia/Irkutsk" "Antarctica/Palmer" "America/Bahia_Banderas" "Europe/Budapest" "America/Regina" "America/Halifax" "Asia/Anadyr" "America/Cuiaba" "Australia/NSW" "Africa/Porto-Novo" "America/Argentina/Catamarca" "Indian/Mauritius" "Europe/Samara" "America/Eirunepe" "America/Fort_Nelson" "Atlantic/Faeroe" "Africa/Asmara" "Antarctica/Mawson" "Asia/Tashkent" "Etc/GMT-11" "Africa/Maputo" "Pacific/Kiritimati" "PRC" "America/Argentina/ComodRivadavia" "Asia/Qyzylorda" "Pacific/Midway" "America/Kralendijk" "Asia/Kabul" "America/Port_of_Spain" "Europe/Zurich" "America/Indianapolis" "NZ-CHAT" "Atlantic/Jan_Mayen" "Asia/Kathmandu" "Africa/Algiers" "America/Matamoros" "America/Araguaina" "America/Rosario" "Etc/Greenwich" "America/Argentina/Tucuman" "Antarctica/South_Pole" "America/Marigot" "Pacific/Marquesas" "Pacific/Truk" "Asia/Baghdad" "America/Dawson_Creek" "Europe/Kirov" "America/Santo_Domingo" "US/Central" "America/Miquelon" "Pacific/Wake" "Asia/Ust-Nera" "Australia/Canberra" "Indian/Antananarivo" "Brazil/DeNoronha" "Europe/Malta" "Europe/Paris" "Africa/Luanda" "Europe/Madrid" "Asia/Chongqing" "Europe/Gibraltar" "America/Cancun" "Asia/Dacca" "Israel" "Pacific/Port_Moresby" "Atlantic/Cape_Verde" "Australia/Darwin" "Africa/Nairobi" "WET" "Atlantic/Canary" "Africa/Libreville" "America/Paramaribo" "Asia/Ho_Chi_Minh" "Europe/Vatican" "Africa/Ndjamena" "SystemV/CST6CDT" "Australia/Melbourne" "America/Manaus" "Etc/GMT-13" "America/Adak" "Asia/Taipei" "Asia/Dhaka"} [:field 721 nil])) "timezone-id") "to")] "Must be a valid instance of one of these clauses: :+, :datetime-add, :datetime-subtract, :convert-timezone, :now")}}} arg0)] 

### Information about your Metabase installation

```JSON
1.47.0 and master
```

### Severity

You need to end up using SQL which will remove a lot of features that are available for GUI questions

### Additional context

_No response_

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the custom-column flow with convertTimezone(now, [Time Zone Config]) and the supplied dates_.csv, then inspect the convertTimezone entry point and the query processor error around resolve-card-id-source-tables*. Done means column arguments are either rejected before preview or produce a clear error instead of removing the custom column and failing during SQL generation.

Written by the indexing model from the issue text.

Assessment

Tech stack
clojure
Domain
backend
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.