dhruv-techdev opened a new issue, #45434:
URL: https://github.com/apache/superset/issues/45434
### Bug description
The Hive engine spec's `Week` time grain (`P1W`) doesn't group the days of a
week into one bucket. Each weekday gets a different date, so weekly charts on
Hive show roughly one point every two days, each holding a partial total.
The expression in `superset/db_engine_specs/hive.py`
(`HiveEngineSpec._time_grain_expressions[TimeGrain.WEEK]`):
```python
"date_format(date_sub({col}, CAST(7-from_unixtime(unix_timestamp({col}),'u')
as int)), 'yyyy-MM-dd 00:00:00')"
```
`from_unixtime(..., 'u')` returns the ISO day of week (Monday=1 … Sunday=7),
so this subtracts `7 - u` days. To truncate to Monday it should subtract `u -
1`.
| Date | Day | `u` | Bucket (actual) | Bucket (expected) |
|---|---|---|---|---|
| 2025-01-06 | Mon | 1 | 2024-12-31 | 2025-01-06 |
| 2025-01-07 | Tue | 2 | 2025-01-02 | 2025-01-06 |
| 2025-01-08 | Wed | 3 | 2025-01-04 | 2025-01-06 |
| 2025-01-09 | Thu | 4 | 2025-01-06 | 2025-01-06 |
| 2025-01-10 | Fri | 5 | 2025-01-08 | 2025-01-06 |
| 2025-01-11 | Sat | 6 | 2025-01-10 | 2025-01-06 |
| 2025-01-12 | Sun | 7 | 2025-01-12 | 2025-01-06 |
**How to reproduce**
1. Connect a Hive database and create a dataset on a table with a date
column that has rows on consecutive days.
2. Build a Line Chart with that column on the X-axis, a metric such as
`COUNT(*)`, and **Time grain = Week**.
3. The X-axis shows points about every two days instead of one per week.
The same result is visible directly in SQL Lab:
```sql
SELECT d,
date_format(date_sub(d, CAST(7-from_unixtime(unix_timestamp(d),'u')
AS int)), 'yyyy-MM-dd') AS week_bucket
FROM (SELECT
explode(array('2025-01-06','2025-01-07','2025-01-08','2025-01-09',
'2025-01-10','2025-01-11','2025-01-12')) AS d) t;
```
**Expected:** all seven rows share one `week_bucket`.
**Actual:** all seven `week_bucket` values are different.
**Suggested fix:** subtract `u - 1` instead of `7 - u`, for example
`date_sub({col}, CAST(from_unixtime(unix_timestamp({col}),'u') AS int) - 1)`.
### Screenshots/recordings
_No response_
### Superset version
master / latest-dev
### Python version
Not applicable
### Node version
Not applicable
### Browser
Not applicable
### Additional context
- The expression was added in #10269 and hasn't changed since. No unit test
covers it.
- `SparkEngineSpec` and `DatabricksHiveEngineSpec` subclass `HiveEngineSpec`
but define their own grains, so they aren't affected.
- Verified the `'u'` values and bucket arithmetic with Java's
`SimpleDateFormat`, which Hive 1.x–3.x uses for `from_unixtime`. Not yet run on
a live Hive server.
### 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.
- [x] 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.
--
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]
