Intuitive display of negative timedeltas

[benvdh]

Is your feature request related to a problem? Please describe.
At the moment I'm working on a dashboard for a customer of ours that has to display averages of timedeltas. Luckily PostgreSQL has a native datatype called interval to save such data, and that makes it possible to calculate averages over them too. Superset is currently able to show the results of such aggregations in its table visualisation by converting the postgres interval data to python's timedelta type. However, this has one disadvantage, a negative interval of -6 minutes becomes:

>>> from datetime import timedelta
>>> a = timedelta(minutes=-6)
>>> str(a)
'-1 day, 23:54:00

The main rationale behind the above result is that the string representation matches the timedelta object's internal representation[1]. The above displayed value relates to the -6 minutes by interpreting -1 day as -24 hours and the hours and minutes part as +23 hours + 54 minutes. If we sum those values we end up with -6 minutes.

However, for a customer having to interpret a whole column with tens of values like this, this quickly becomes a hassle and makes it hard to compare the values, not to speak of the cognitive load it brings to interpret the data.

A similar discussion is currently going on in the pandas community: pandas-dev/pandas#17232

Describe the solution you'd like
Personally I would like -6 minutes to be displayed as -0 days, 00:06:00 or something similar. This makes it much more intuitive for the user of the dashboard. I already have a pull request available which does exactly that, it's based on a solution to this problem suggested on SO[2].

Describe alternatives you've considered

1. I have considered storing the intervals as strings, but that would make aggregating impossible.
2. Another solution would be to store the intervals in absolute terms (e.g. -360 seconds), and then formatting that number with fairly complex SQL (i.e. applying modulus operators for seconds, minutes, hours, days... etc.) into a human-readable string, That would be the workaround at the database level applicable for MySQL/MariaDB databases, as its time datatype cannot store intervals longer than ~35 days.
3. A more generic solution to these kinds of problems might be to allow columns in Table data sources to be formatted with an arbitrary python function, which you could define on a per column basis.
4. To solve many other issues with the postgres Interval type, it would be nice if superset would support TimeDelta's as a native datatype (like String, Numeric, and Datetime), formatting negative values could then be one small problem solved by this more generic solution.

Solution 2 would require more effort from the user when building a dasboard, solution 3 has a much larger scope, and 4 would likely be the size of a SIP.

Additional context
Add any other context or screenshots about the feature request here.
[1] pandas-dev/pandas#17232 (comment)
[2] https://stackoverflow.com/a/8408947/10243474

[issue-label-bot]

Issue-Label Bot is automatically applying the label #enhancement to this issue, with a confidence of 0.89. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.