fix: datetime with timezone excel export by betodealmeida · Pull Request #25318 · apache/superset

betodealmeida · 2023-09-15T19:15:48Z

SUMMARY

Handle datetimes with timezones when exporting to Excel.

Fixes #23584.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

Has associated issue:
Required feature flags:
Changes UI
Includes DB Migration (follow approval process in SIP-59)
- Migration is atomic, supports rollback & is backwards-compatible
- Confirm DB migration upgrade and downgrade tested
- Runtime estimates and downtime expectations provided
Introduces new feature or API
Removes existing feature or API

michael-s-molina

Thank you for the PR @betodealmeida!

superset/utils/excel.py

Co-authored-by: Michael S. Molina <[email protected]> (cherry picked from commit 5ebcd2a)

* fix: is_select with UNION (apache#25290) (cherry picked from commit bb002d6) * fix: Add explicit ON DELETE CASCADE for dashboard_roles (apache#25320) (cherry picked from commit d54e827) * fix(chart): Supporting custom SQL as temporal x-axis column with filter (apache#25126) Co-authored-by: Kamil Gabryjelski <[email protected]> * fix: Use RLS clause instead of ID for cache key (apache#25229) (cherry picked from commit fba66c6) * fix: Improve the reliability of alerts & reports (apache#25239) (cherry picked from commit f672d5d) * fix: DashboardRoles cascade operation (apache#25349) (cherry picked from commit a971a28) * fix: datetime with timezone excel export (apache#25318) Co-authored-by: Michael S. Molina <[email protected]> (cherry picked from commit 5ebcd2a) * fix: Workaround for Cypress ECONNRESET error (apache#25399) (cherry picked from commit d76ff39) * fix(sqllab): invalid persisted tab state (apache#25308) (apache#25398) * fix: Rename on_delete parameter to ondelete (apache#25424) (cherry picked from commit 893b45f) * fix: preventing save button from flickering in SQL Lab (apache#25106) (cherry picked from commit 296ff17) * fix: chart import (apache#25425) (cherry picked from commit a4d8f36) * fix: swagger UI CSP error (apache#25368) (cherry picked from commit 1716b9f) * fix: smarter date formatter (apache#25404) (cherry picked from commit f0080f9) * fix(sqllab): invalid start date (apache#25437) * fix(nativeFilters): Speed up native filters by removing unnecessary rerenders (apache#25282) Co-authored-by: JUST.in DO IT <[email protected]> (cherry picked from commit a0eeb4d) * fix(SqlLab): make icon placement even (apache#25372) (cherry picked from commit 11b49a6) * fix: Duplicate items when pasting into Select (apache#25447) (cherry picked from commit 7cf96cd) * fix: update the SQLAlchemy model definition at json column for Log table (apache#25445) (cherry picked from commit e83a76a) * fix(helm chart): set chart appVersion to 3.0.0 (apache#25373) * fix(mysql): handle string typed decimal results (apache#24241) (cherry picked from commit 7eab59a) * fix: Styles not loading because of faulty CSP setting (apache#25468) (cherry picked from commit 0cebffd) * fix(sqllab): error with lazy_gettext for tab titles (apache#25469) (cherry picked from commit ddde178) * fix: Address Mypy issue which is causing CI to fail (apache#25494) (cherry picked from commit 36ed617) * chore: Adds 3.0.1 CHANGELOG * fix: Unable to sync columns when database or dataset name contains `+` (apache#25390) (cherry picked from commit dbe0838) * fix(sqllab): Broken query containing 'children' (apache#25490) (cherry picked from commit b92957e) * chore: Expand error detail on screencapture (apache#25519) (cherry picked from commit ba541e8) * fix: tags permissions error message (apache#25516) (cherry picked from commit 50b0816) * fix: Apply normalization to all dttm columns (apache#25147) (cherry picked from commit 58fcd29) * fix: REST API CSRF exempt list (apache#25590) (cherry picked from commit 549abb5) * fix(RLS): Fix Info Tooltip + Button Alignment on RLS Modal (apache#25400) (cherry picked from commit a6d0e6f) * fix: thubmnails loading - Talisman default config (apache#25486) (cherry picked from commit 52f631a) * fix(Presto): catch DatabaseError when testing Presto views (apache#25559) Co-authored-by: Rui Zhao <[email protected]> (cherry picked from commit be3714e) * fix(Charts): Set max row limit + removed the option to use an empty row limit value (apache#25579) (cherry picked from commit f556ef5) * fix(window): unavailable localStorage and sessionStorage (apache#25599) * fix: finestTemporalGrainFormatter (apache#25618) (cherry picked from commit 62bffaf) * fix: revert fix(sqllab): Force trino client async execution (apache#24859) (apache#25541) (cherry picked from commit e56e0de) * chore: Updates 3.0.1 CHANGELOG * fix(sqllab): Mistitled for new tab after rename (apache#25523) (cherry picked from commit a520124) * fix(sqllab): template validation error within comments (apache#25626) (cherry picked from commit b370c66) * fix: avoid 500 errors with SQLLAB_BACKEND_PERSISTENCE (apache#25553) (cherry picked from commit 99f79f5) * fix(import): Make sure query context is overwritten for overwriting imports (apache#25493) (cherry picked from commit a0a0d80) * fix: permalink save/overwrites in explore (apache#25112) Co-authored-by: Elizabeth Thompson <[email protected]> (cherry picked from commit e58a3ab) * fix(header navlinks): link navlinks to path prefix (apache#25495) (cherry picked from commit 51c56dd) * fix: improve upload ZIP file validation (apache#25658) * fix: warning of nth-child (apache#23638) (cherry picked from commit 16cc089) * fix(dremio): Fixes issue with Dremio SQL generation for Charts with Series Limit (apache#25657) (cherry picked from commit be82657) --------- Co-authored-by: Beto Dealmeida <[email protected]> Co-authored-by: John Bodley <[email protected]> Co-authored-by: Zef Lin <[email protected]> Co-authored-by: Kamil Gabryjelski <[email protected]> Co-authored-by: Jack Fragassi <[email protected]> Co-authored-by: Michael S. Molina <[email protected]> Co-authored-by: JUST.in DO IT <[email protected]> Co-authored-by: Jack <[email protected]> Co-authored-by: Daniel Vaz Gaspar <[email protected]> Co-authored-by: Stepan <[email protected]> Co-authored-by: Corbin Bullard <[email protected]> Co-authored-by: Gyuil Han <[email protected]> Co-authored-by: Celalettin Calis <[email protected]> Co-authored-by: Ville Brofeldt <[email protected]> Co-authored-by: ʈᵃᵢ <[email protected]> Co-authored-by: Michael S. Molina <[email protected]> Co-authored-by: mapledan <[email protected]> Co-authored-by: Igor Khrol <[email protected]> Co-authored-by: Rui Zhao <[email protected]> Co-authored-by: Fabien <[email protected]> Co-authored-by: Hugh A. Miles II <[email protected]> Co-authored-by: OskarNS <[email protected]>

Sonalit23 · 2024-02-01T16:29:06Z

This issue is still reproducible on superset version 3.0.0

More information:
Failing for DATETIME type.
Even with DATETIMETZ type and the column time is customized to adaptive format (like 2024-01-12) export to excel fails with 500 error

dfoley84 · 2024-02-07T13:04:26Z

This issue also affects 3.1.0 for DATETIME

:ERROR:superset.views.base:Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel. Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps return f(self, *args, **kwargs) File "/app/superset/views/base_api.py", line 127, in wraps raise ex File "/app/superset/views/base_api.py", line 121, in wraps duration, response = time_function(f, self, *args, **kwargs) File "/app/superset/utils/core.py", line 1454, in time_function response = func(*args, **kwargs) File "/app/superset/utils/log.py", line 255, in wrapper value = f(*args, **kwargs) File "/app/superset/charts/data/api.py", line 256, in data return self._get_data_response( File "/app/superset/charts/data/api.py", line 412, in _get_data_response result = command.run(force_cached=force_cached) File "/app/superset/commands/chart/data/get_data_command.py", line 45, in run payload = self._query_context.get_payload( File "/app/superset/common/query_context.py", line 101, in get_payload return self._processor.get_payload(cache_query_context, force_cached) File "/app/superset/common/query_context_processor.py", line 600, in get_payload query_results = [ File "/app/superset/common/query_context_processor.py", line 601, in <listcomp> get_query_results( File "/app/superset/common/query_actions.py", line 224, in get_query_results return result_func(query_context, query_obj, force_cached) File "/app/superset/common/query_actions.py", line 186, in _get_results payload = _get_full(query_context, query_obj, force_cached) File

I was able to export DateTime Data to Excel with the following appended to query_context_processory.py
Only issue with the below Code, is that it appends H:M:S to the end of the DateTime field

Example 30/06/2023 00:00:00

  # Convert timezone aware datetimes to timezone unwaware datetimes
                for col in df.select_dtypes(include=["datetimetz"]).columns:
                    if pd.api.types.is_datetime64_any_dtype(ndf[col]):
                        if ndf[col].dt.tz is not None:
                            ndf[col] = ndf[col].dt.tz_convert(None)

Co-authored-by: Michael S. Molina <[email protected]>

fix: datetime with timezone excel export

c59649a

pull-request-size bot added the size/M label Sep 15, 2023

betodealmeida requested a review from michael-s-molina September 15, 2023 19:30

michael-s-molina approved these changes Sep 15, 2023

View reviewed changes

superset/utils/excel.py Outdated Show resolved Hide resolved

michael-s-molina added the v3.0 Label added by the release manager to track PRs to be included in the 3.0 branch label Sep 15, 2023

Fix typo

de73f6f

betodealmeida merged commit 5ebcd2a into master Sep 21, 2023

michael-s-molina added a commit that referenced this pull request Sep 25, 2023

fix: datetime with timezone excel export (#25318)

88e6f22

Co-authored-by: Michael S. Molina <[email protected]> (cherry picked from commit 5ebcd2a)

rusackas deleted the fix-excel-export branch February 7, 2024 19:27

dfoley84 mentioned this pull request Feb 10, 2024

Fixing Date Time issue as seen in v3.1.0 #27076

Closed

9 tasks

cccs-rc pushed a commit to CybercentreCanada/superset that referenced this pull request Mar 6, 2024

fix: datetime with timezone excel export (apache#25318)

a1cce85

Co-authored-by: Michael S. Molina <[email protected]>

vinothkumar66 pushed a commit to vinothkumar66/superset that referenced this pull request Nov 11, 2024

fix: datetime with timezone excel export (apache#25318)

78586ee

Co-authored-by: Michael S. Molina <[email protected]>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

fix: datetime with timezone excel export#25318

fix: datetime with timezone excel export#25318
betodealmeida merged 2 commits intomasterfrom
fix-excel-export

betodealmeida commented Sep 15, 2023

Uh oh!

michael-s-molina left a comment

Uh oh!

Uh oh!

Sonalit23 commented Feb 1, 2024 •

edited

Loading

Uh oh!

dfoley84 commented Feb 7, 2024 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Conversation

betodealmeida commented Sep 15, 2023

SUMMARY

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

Uh oh!

michael-s-molina left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Sonalit23 commented Feb 1, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dfoley84 commented Feb 7, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Sonalit23 commented Feb 1, 2024 •

edited

Loading

dfoley84 commented Feb 7, 2024 •

edited

Loading