ivkhokhlachev opened a new issue, #28014:
URL: https://github.com/apache/superset/issues/28014
### Bug description
There are two data types in Clickhouse: Date (stored without timezone) and
DateTime (stored with a timezone). Default timezone for DateTime is a server's
timezone. It means that `toDateTime(col)` is actually the same as
`toDateTime(col, timeZone())`
When a Date column is used in charts with Time Grain = Day (P1D), Superset
wraps a column with `toStartOfDay(toDateTime(date_column))`, so original
no-timezone value is converted to midnight of a date in a server's timezone.
It's expected that Date value should stay the same with Time Grain = Day. It
can be achieved by using `toDate({col})` for P1D.
### How to reproduce the bug
**Important**: our Clickhouse server is in Europe/Amsterdam timezone.
**Superset version:** 3.1.1.
**Driver**: Clickhouse Connect
https://github.com/ClickHouse/clickhouse-connect
**Connection options**: apply_server_timezone = **false**. We use this
option to display original DateTime('UTC') values in UTC timezone, not in
Europe/Amsterdam.
1. Create a virtual dataset with the following script:
`SELECT toDate('2024-04-12') AS c_date, toDateTime('2024-04-12', 'UTC') AS
c_datetime`
2. Create a table chart using this dataset, query mode = Raw Records, Time
Grain = Day. Both values are shown as expected.
<img width="520" alt="image"
src="https://github.com/apache/superset/assets/88135545/21375ad6-29c6-488a-8eeb-6b68fdca0ea7";>
Resulted query:
```
SELECT `c_date` AS `c_date_017363`,
`c_datetime` AS `c_datetime_c47307`
FROM
(SELECT toDate('2024-04-12') AS c_date,
toDateTime('2024-04-12', 'UTC') AS c_datetime) AS `virtual_table`
LIMIT 10000;
```
3. Create a line chart, X-axis = **c_datetime**, Metric = count(), Time
Grain = Day. Value is a tooltip is shown as expected.
<img width="855" alt="image"
src="https://github.com/apache/superset/assets/88135545/1c3d2e33-cf8d-4aae-92e6-883e5af55ac3";>
Query:
```
SELECT toStartOfDay(toDateTime(`c_datetime`)) AS `c_datetime_c47307`,
count() AS `count()_0087ae`
FROM
(SELECT toDate('2024-04-12') AS c_date,
toDateTime('2024-04-12', 'UTC') AS c_datetime) AS `virtual_table`
GROUP BY toStartOfDay(toDateTime(`c_datetime`))
ORDER BY `count()_0087ae` DESC
LIMIT 10000;
```
5. Create a line chart, X-axis = **c_date**, Metric = count(), Time Grain =
Day. Original Date value is converted to DateTime with a server's timezone.
<img width="875" alt="image"
src="https://github.com/apache/superset/assets/88135545/683d6f60-35b4-4378-8290-42045e4b96a7";>
Query:
```
SELECT toStartOfDay(toDateTime(`c_date`)) AS `c_date_017363`,
count() AS `count()_0087ae`
FROM
(SELECT toDate('2024-04-12') AS c_date,
toDateTime('2024-04-12', 'UTC') AS c_datetime) AS `virtual_table`
GROUP BY toStartOfDay(toDateTime(`c_date`))
ORDER BY `count()_0087ae` DESC
LIMIT 10000;
```
To resolve this issue, we had to add a custom Time Grain. Function toDate
works better here, as it ignores a server's timezone, and it's compatible with
both Date and DateTime types.
```
TIME_GRAIN_ADDONS = {'CUSTOM_1D': 'Day UTC'}
TIME_GRAIN_ADDON_EXPRESSIONS = {
'clickhouse': {
'CUSTOM_1D': 'toDate({col})'
}
}
```
1. The problem with this approach is that now we have got two similar time
grains, which is confusing.
2. If we try to hide original P1D grain using `TIME_GRAIN_DENYLIST =
['P1D']`, it breaks all charts as (bad luck) this is a default time grain for
all Superset charts.
3. There's also an idea to overwrite a value for P1D in
_time_grain_expressions dictionary in superset/db_engine_specs/clickhouse.py,
but it sounds a bit risky :)
### Screenshots/recordings
_No response_
### Superset version
3.1.2
### Python version
I don't know
### Node version
I don't know
### Browser
Not applicable
### 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.
--
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: notifications-unsubscr...@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org
For additional commands, e-mail: notifications-h...@superset.apache.org
