Provide a way to set timezone via Cube SQL API call

[johanjvrens]

Thank you so much for the wonderful Cube product! I know the Cube SQL API is currently in Preview and I really find it useful.

Is your feature request related to a problem? Please describe. When running a query through Cube SQL API via MySQL-compatible SQL the generated REST API query is only run in UTC instead of my time zone

Describe the solution you'd like A solution would be to have a way to indicate the timezone in the MySQL-compatible SQL or use the client timezone settings when generating the REST API query

Describe alternatives you've considered I've tried to set the MySQL client and this doesn't seem to make a difference. I've also tried to find some info about this in the documentation, but I couldn't find anything out there.

Additional context

mysql> SELECT
    ->   count,
    ->   status,
    ->   DATE(DATE_SUB(`created`, INTERVAL DAYOFMONTH(`created`) - 1 DAY)) date
    -> FROM Orders
    -> GROUP BY date, status
    -> ORDER BY date ASC;

The query above will translate to UTC time, I would like to run this as timezone UTC + 2. I can't seem to find a way to achieve this.

[paveltiunov]

Hey @johanjvrens ! Thanks for the feedback! We're considering the support of time zones for Cube SQL API. Could you please elaborate on what tool do you use to connect to Cube SQL API and what is the use case for different time zones?

[johanjvrens]

@paveltiunov Thank you for your prompt response.

I'm using Metabase and DBeaver locally to connect to cubejs via the SQL API we have Athena And Postgres Drivers setup. We have some real-time queries like the Number of orders today these are run via Postgres and the number of orders for last year is run on Athena. Our timezone in South Africa is UTC + 2 so when data is binned per day the numbers are wrong when the timezone is not used in the query. This isn't a problem from our frontend calls to the load API as these queries support timezone.

{
  "measures": ["Orders.lineItemTotal"],
  "segments": ["Orders.paid"],
  "timeDimensions": [
    {
      "dimension": "Orders.paymentDateTime",
      "dateRange": "This month"
    }
  ],
  "timezone": "Africa/Johannesburg"
}

So to summarize there is a difference in the answer when run on Metabase (SQL API) vs the frontend apps (REST API) and the difference is due to the timezone offset not being applied or settable when using the SQL API.

[johanjvrens]

Any Updates on how to set the timezone for SQL API

[wei-fh]

I have the exact same question. Has there been any resolution on this?