cube
cube copied to clipboard
Option to Turn Off Timezone Conversion for Time Dimensions
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.
Hi @IanMeta 👋
Cube automatically converts to necessary timezones only dimensions that are used as time dimensions in queries. So, you can avoid this kind of conversion by referencing your dimension as a regular one.
A temporary solution for us is to set the time dimension as string type. This is a fine workaround as well.
Hi @IanMeta 👋
Cube automatically converts to necessary timezones only dimensions that are used as time dimensions in queries. So, you can avoid this kind of conversion by referencing your dimension as a regular one.
A temporary solution for us is to set the time dimension as string type. This is a fine workaround as well.
Yes. However, we would still like to access other features of the time dimensions like the various date functions in PostgreSQL such as date_trunc
. Thus it would be helpful if there is an option just to disable to automatic timezone conversion.
Gotcha. Let me add @paveltiunov to check what his thoughts are about having such an option.