REST API Ignores Timezone Parameter with Aggregations #10159
Description
Describe the bug
When sending a POST request to /cubejs-api/v1/sql with a query that includes aggregations (e.g., SUM(), COUNT()) and a WHERE clause filtering by date, the timezone parameter is not respected, and results are returned in UTC instead. However, queries without aggregations (dimension-only queries) correctly apply the specified timezone.
To Reproduce
Steps to reproduce the behavior:
- Create a cube with a time dimension and a measure (see schema below)
- Send a POST request to
/cubejs-api/v1/sqlwith the following query and timezone parameter:
{
"query": {
"measures": ["Orders.totalAmount"],
"timeDimensions": [{
"dimension": "Orders.orderDate",
"granularity": "day",
"dateRange": ["2023-10-31", "2023-10-31"]
}],
"timezone": "America/Vancouver"
}
}- Observe that results include data from UTC timezone (e.g., showing 10-30 in addition to 10-31)
- Compare with a query without measures (only dimensions), which correctly respects the timezone
Expected behavior
The SQL API should respect the timezone parameter for all queries, including those with aggregations. When filtering for dates in a specific timezone (e.g., America/Vancouver), only data from that timezone should be returned, regardless of whether the query includes measures or not.
Screenshots
N/A - Data inconsistency issue
Minimally reproducible Cube Schema
cube(`Orders`, {
sql: `
SELECT 1 as id, 100 as amount, '2023-10-30T23:30:00Z' as order_date
UNION ALL
SELECT 2 as id, 200 as amount, '2023-10-31T08:00:00Z' as order_date
UNION ALL
SELECT 3 as id, 300 as amount, '2023-10-31T15:00:00Z' as order_date
UNION ALL
SELECT 4 as id, 400 as amount, '2023-11-01T01:00:00Z' as order_date
`,
measures: {
totalAmount: {
sql: `amount`,
type: `sum`,
},
count: {
type: `count`,
},
},
dimensions: {
orderDate: {
sql: `order_date`,
type: `time`,
},
},
});Version
1.5.2
Additional context
This issue occurs because the coerceForSqlQuery method in the API gateway doesn't pass the convertTzForRawTimeDimension option to the SQL compiler. This option is necessary for applying timezone conversion to time dimensions that are referenced within aggregated measures.
Root cause
The convertTzForRawTimeDimension parameter (controlled by CUBESQL_SQL_PUSH_DOWN environment variable, default: true) needs to be passed when preparing SQL API queries for compilation. Without it, timezone conversion is skipped for time dimensions used inside measure calculations.
Workaround
Until this is fixed, avoid using aggregations in SQL API queries that require specific timezone handling, or use the REST API instead which correctly handles timezones.
Fix
The fix involves modifying the coerceForSqlQuery method in packages/cubejs-api-gateway/src/gateway.ts:
protected coerceForSqlQuery(query, context: Readonly<RequestContext>) {
return {
...query,
timeDimensions: query.timeDimensions || [],
contextSymbols: {
securityContext: this.securityContextExtractor(context),
},
requestId: context.requestId,
convertTzForRawTimeDimension: getEnv('convertTzForRawTimeDimension') // Add this line
};
}