dineshsaini commented on issue #7958: URL: https://github.com/apache/superset/issues/7958#issuecomment-2783986367
This also happened with me when configuring form timezone, however after a while it makes sense of what i was doing wrong, I put up a complete tutorial on my hours offset and timezone conversion in my gist [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. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
