Option to Turn Off Timezone Conversion for Time Dimensions
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Is your feature request related to a problem? Please describe.**
Currently, there is no way to turn off Timezone Conversion for CubeJS query rewrite for time dimensions. This caused some problems when the Time is already converted to the specific timezone based on the columns in the table.
E.g.
CubeJS PostgreSQL query:
```
select
AreaId,
date_trunc('hour',
CurrentLocalTime) AS Time,
CurrentLocalTime,
FinalOccupancy
from
live_data
```
MySQL rewrite:
```
SELECT
`live_data`.areaid `live_data___area_id`,
`live_data`.currentlocaltime `live_data___current_local_time`,
`live_data`FinalOccupancy `live_data___FinalOccupancy`,
CAST(
DATE_FORMAT(
CONVERT_TZ(
`live_data`.currentlocaltime,
@@session.time_zone,
'+00:00'
),
'%Y-%m-%dT%H:00:00.000'
) AS DATETIME
) `live_data___current_local_time_hour`
FROM
(
SELECT
av.AreaId,
av.CurrentLocalTime,
Max_by(fo.FinalOccupancy, fo.__time) FinalOccupancy
FROM
(
SELECT
areaid,
(CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', Timezone)) CurrentLocalTime
FROM
area_view
) av
LEFT JOIN final_occupancy fo ON av.AreaId = fo.AreaId
WHERE
OutputTopicName = concat('occupancy-data-area-', CAST(typeid AS varchar))
GROUP BY
1,
2
) AS `live_data`
GROUP BY
1,
2,
3,
4
ORDER BY
3 ASC
LIMIT
50000
```
This not only converts to timezone to an unintended value, but also caused an unexpected error in the underlying MySQL engine due to using the CONVERT_TZ function twice (although this error is the query engine's responsibility, we would still like the option to disable timezone conversion).
**Describe the solution you'd like**
Add a configuration option in cube.js or cube schema to disable timezone conversion.
**Describe alternatives you've considered**
A temporary solution for us is to set the time dimension as string type.
Contributor guide
Assessment
This issue has not been assessed yet.