apache / apache/superset

Improve DB2 time grain expressions using DATE_TRUNC scalar function

Open Beginner friendly
#43,231 4 comments 0 reactions 0 assignees View on GitHub
data:connect:db2
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.