Hi Folks,
I’ve got a date param from dashboard URL; say {{check_in}} which could be single date or a range.
I want to convert {{check_in}} dates into a specific timezone. How would I achieve that?
I tried:
[…] from table X where [[ check_in IN {{check_in} ]] -> NOT WORKING
@flamber: Hey, I want to convert dates " Past 7 days" within {{check_in}} into different timezone say “America/Cancun”.
My use case: convert each date within {{check_in}} into some timezone date.
How would I acheive that please suggest a way.
Any leads would be highly appreciated.
@nadimayaz Then do a sub select, where you use the variable in, and then you return what you need to the parent.
SELECT * FROM (SELECT timezone_column FROM checkin_table WHERE {{check_in}})
Otherwise search the forum or look at the comments and referenced issues, they contain a lot of examples:
https://github.com/metabase/metabase/issues/3324
@flamber: Hey, can you suggest a way to convert: {{check_in}} into [date1,date2,…dateN] using postgres syntax? so that I can play with date(i).
@nadimayaz When using a field filter, it gets translated to something like this for dates (will be different depending on database type):
table.column BETWEEN '2020-01-01' AND '2020-01-31'
So you would have to write a query, where that is taken into account. Something like this perhaps:
SELECT date(sub.column) FROM (SELECT timezone_column FROM checkin_table WHERE {{check_in}}) AS sub
You might find better help on stackoverflow.com or a forum specific to writing queries.
@flamber: On the metabase dashboard, what is the timezone of date on right side upper corner ex. Today,Past 7 days, etc. Does it depend on user logging into dashboard from different timezone or its database picking tomezone?
@nadimayaz That depends on the database and your configuration.
https://www.metabase.com/docs/latest/troubleshooting-guide/timezones.html
But that’s a completely different topic, so search the forum first and then create a new topic if you cannot find an answer.