cube-js / cube-js/cube

Option to Turn Off Timezone Conversion for Time Dimensions

Open
#8,086 3 comments 0 reactions 0 assignees View on GitHub
question
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.