GitHub user dineshsaini added a comment to the discussion: config superset for local time zone?
This ``` from datetime import timedelta DEFAULT_TIMEZONE = 'UTC' ``` _only works with druid database i think_. because it is not working for me(with cratedb). however i made other tweaks that make other things works for me those are written in this gist of mine: [Configuring Timezone in Apache Superset](https://gist.github.com/dineshsaini/69dc28c8a5258808cfe5e1c17433972c) for sake of completeness i am also writing my hypothesis here, if anything work in other way then please correct them.: > Recently working with [NEC]("https://www.nec.com/";) on a specific project, i > encountered timezone issue in Apache superset. After detailed analyses i > solved this issue, below are the analysis and steps to do it. > > > Conditions: > 1. Superset does all time related calculations in UTC format only. So, it is > recommended for you to do the same. > 2. if possible skip setting timeaware dates in db, as this can create > un-neccesery complexity/confusion on/during conversions. > 3. have a central timzezone on which all operation happens > (read/calculations/write), and UTC is best for that. > > Steps to follow: > 1. Superset consider its system's timezone as default timezone for its > UI dates, like date range. So set your container's timzezone as your desired > time zone. > steps: > #### # apt-get install -y tzdata > #### # ln -sf /usr/share/zoneinfo/Asia/Jakarta /etc/localtime > #### # echo "Asia/Jakarta" > /etc/timezone > 2. Now your UI dates selection will be in Jakarta timezone, which, once > you select, will be converted to UTC by superset behind the scean. > 3. As db dates are already in UTC now comparision can happens in sync. > 4. once results are generated you need to change those UTC dates to > your timezone (here Jakarta/UTC+7). for this, superset has "HOURS OFFSET" in > "datasets" settings, set it to your timezone's hours. (here it is +7) > 5. this makes results dates to converted back to UTC+7 from UTC. > > Half part for timezone settings is done here, here only limitation is that > "HOURS OFFSET" doesn't consider float values, which makes it harder for > minutes to change. > If change of minutes are required then you will need to write template (jinja > syntax) in/for your query and then pass those value from "Template > parameters" (as dict, eg {key: value,...}) from "datasets" only. > > In simple words, This works as below (consider your timezone is UTC+7): > 1. change container timezone, this makes UI to show dates in UTC+7. > 2. if you select any dates(which are now visible in UTC+7) from time > range, superset converts them in UTC. > 3. UTC dates are compared with db dates which were already in UTC. > 4. Generated result has dates in UTC, which are converted to UTC+7 by > "HOURS OFFSET". > > I repeated above steps twice to consider one specific issue with superset. > > Issue is: > Once UI is in UTC+7 all dates from "date range" will be in UTC+7 only, except > "Time Grains". "Time grains" like "Day", "Month"; are not time, they are > labels for db function which extract that specified part from date's column. > So what happens with them is that, in above part, there is no 1st & 2nd > steps, ie., in UI there is no "dates" in UTC+7 to be converted to UTC by > superset, but 3rd & 4th steps are still gets to get executed. > This makes an extra 7 hours (timzezone's hours) to get added in query > results's dates, and another thing is that, with compbination of "Time range" > and "Time grain", one is handled for timezone and other(time grain) is not, > this makes data to be wrongly grouped while executing query, This issue needs > to be solved to completly configure timezones in superset. > > To configure "time grain" with timezone handled, superset provides these 3 > variables from configuration file: > 1. TIME_GRAIN_ADDONS > 2. TIME_GRAIN_ADDON_EXPRESSIONS > 3. TIME_GRAIN_DENYLIST > > Explanation: ``` TIME_GRAIN_ADDONS = {'<key>': '<value to display on ui>',...} TIME_GRAIN_ADDON_EXPRESSIONS = { '<db_engine_name>': { '<key_as_defined_in_addons>': '<sql_expr_to_follow>',.... },... } TIME_GRAIN_DENYLIST= ['<key_to_deny>',...] ``` > TIME_GRAIN_ADDON_EXPRESSIONS's keys that are defined in TIME_GRAIN_ADDONS and > not defined in TIME_GRAIN_DENYLIST, are reflected in UI. > > in sql_expr {col} is to be used as a placeholder for actual value. > > this everything, happens in superset_config.py file, and make sure that > 'sql_expr_to_follow' is supported by the db you are using. > > > > To configure time grain, for out of the box grains, you need to get their > actual sql expression, which you can get by selecting that "time grain", then > click "apply filter" and then view chart's query, in that you will see the > expression used as out of the box. > > for example, for me with cratedb as selected db, for "day" it uses below > expression: ``` "DATE_TRUNC('day', {col})" ``` > > now, to make it rightfully consider timezone setting, we need to do this (for > me with UTC+7): ``` "DATE_TRUNC('day', {col} - INTERVAL '7 hour') - INTERVAL '7 hour'" ``` > > Now proper configuration becomes(for me, with cratedb): ``` TIME_GRAIN_ADDON_EXPRESSIONS = { 'crate': { "P1D": "DATE_TRUNC('day', {col} - INTERVAL '7 hour') - INTERVAL '7 hour'", } } ``` > > As we're overriding out of the box setting so no need to define > "TIME_GRAIN_ADDONS", as "P1D" is already internally defined, and because > we're re-setting it and not denying it so "TIME_GRAIN_DENYLIST" is also not > usefull to us, as of now. > > > now, what we did is subrtaction two time, because: > 1. First subtraction ({col} - INTERVAL '7 hours') ensures the correct > records are grouped into the right day > 2. Second subtraction (DATE_TRUNC() - INTERVAL '7 hours') ensures the > displayed timestamp shows as 00:00:00 in your UTC+7 interface > > Now, when query executed and results generated it will be placed in your UI > with "HOURS OFFSET" added now thats where this secound subrtaction will makes > data's dates correctly displayed. > > > Thats how you make apache superset timezone aware. GitHub link: https://github.com/apache/superset/discussions/19176#discussioncomment-12753779 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
