Option to Turn Off Timezone Conversion for Time Dimensions

[IanMeta]

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.

[igorlukanin]

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.

[IanMeta]

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.

[igorlukanin]

Gotcha. Let me add @paveltiunov to check what his thoughts are about having such an option.