Improve DB2 time grain expressions using DATE_TRUNC scalar function
- Dominant language
- Python
- Stars
- 74.8k
- Forks
- 18.3k
- Avg merge
- 2d 5h
- Merged PRs (30d)
- 685
Description
### Bug description
The [DB2 time grain expressions](https://github.com/apache/superset/blob/master/superset/db_engine_specs/db2.py) currently rely on manual arithmetic by subtracting time grain components (e.g. MICROSECONDS, MINUTES, DAYS) to obtain the desired time granularity. While it is correct, the readability and maintainability can be improved.
DB2 provides a built-in [DATE_TRUNC](https://www.ibm.com/docs/en/db2/12.1.x?topic=functions-date-trunc) scalar function that accepts a format string ('SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR') to obtain the desired time grain unit.
If the change to use the scalar function is made for all the time grain expressions, it will delegate all the logic to obtain the time grain unit to the Db2 database engine.
I can create a pull request to apply this update.
**Edit:** This suggestion to use the DATE_TRUNC function also changes the behavior of the WEEK time grain. The current implementation causes the date to land on Saturday. The new `TimeGrain.WEEK: "DATE_TRUNC('WEEK', {col})"` expression causes the WEEK time grain to land on Monday instead, aligning with the ISO 8601 standard (weeks start on Monday).
### How to reproduce the bug
The existing DB2 time grain expression [db_engine_specs/db2.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/db2.py) is functional, the change is mainly to improve it by using a new scalar function supported by Db2.
```
_time_grain_expressions = {
None: "{col}",
TimeGrain.SECOND: "CAST({col} as TIMESTAMP) - MICROSECOND({col}) MICROSECONDS",
TimeGrain.MINUTE: "CAST({col} as TIMESTAMP)"
" - SECOND({col}) SECONDS"
" - MICROSECOND({col}) MICROSECONDS",
TimeGrain.HOUR: "CAST({col} as TIMESTAMP)"
" - MINUTE({col}) MINUTES"
" - SECOND({col}) SECONDS"
" - MICROSECOND({col}) MICROSECONDS ",
TimeGrain.DAY: "DATE({col})",
TimeGrain.WEEK: "{col} - (DAYOFWEEK({col})) DAYS",
TimeGrain.MONTH: "{col} - (DAY({col})-1) DAYS",
TimeGrain.QUARTER: "{col} - (DAY({col})-1) DAYS"
" - (MONTH({col})-1) MONTHS"
" + ((QUARTER({col})-1) * 3) MONTHS",
TimeGrain.YEAR: "{col} - (DAY({col})-1) DAYS - (MONTH({col})-1) MONTHS",
}
```
### Screenshots/recordings
_No response_
### Superset version
master / latest-dev
### Python version
3.11
### Node version
18 or greater
### Browser
Chrome
### Additional context
_No response_
### Checklist
- [x] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [x] I have searched the GitHub issue tracker and didn't find a similar bug report.
- [ ] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.
Contributor guide
Research direction
Start in superset/db_engine_specs/db2.py and inspect the _time_grain_expressions mapping. Replace the manual DB2 time-grain arithmetic with DATE_TRUNC expressions for the listed units, paying attention to the documented WEEK behavior. Done means all supported expressions use the intended DB2 scalar function and the time-grain behavior is verified.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 78/100