SQL API Ignores Timezone Parameter with Aggregations

[andrew-oh-spare]

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:

1. Create a cube with a time dimension and a measure (see schema below)
2. Send a POST request to /cubejs-api/v1/sql with 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"
  }
}

3. Observe that results include data from UTC timezone (e.g., showing 10-30 in addition to 10-31)
4. 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
  };
}