Support for local time dimensions without query timezone conversion

[hank-sq]

Problem Statement

Currently, Cube.js dimensions have two mutually exclusive behaviors:

• type: time - Supports time features (dateRange, granularity, relative dates like "last month") BUT always applies the query timezone parameter
• type: string - No timezone conversion BUT loses all time dimension features

There's no way to define a dimension that represents pre-converted local time (already in the business's timezone) while still getting time dimension query features.

Use Case

We have a multi-tenant datastore, where some tenants have different timezones, fixed to the tenant id. We want to have time dimensions in that local timezone, that do not change based on the query timezone. These represent the business's operating hours and are the preferred dimensions for time-based analysis.

dimensions:
  - name: local_date
    sql: "DATE_FORMAT(from_utc_timestamp({CUBE}.created_at, {Location.timezone}), 'yyyy-MM-dd')"
    type: string  # Must be string to avoid timezone conversion
    description: "Date in the location's local timezone"

Current Problem:

• Users cannot filter local_date with dateRange: "last month" or other time features
• Must use exact date strings instead: ["2025-10-01", "2025-10-31"]
• Cannot use granularity for aggregation
• Loses the UX benefits of time dimensions

Why This Matters:

1. A store closing at 1 AM local time on "March 1st" might be March 2nd in UTC
2. "Sales by day of week" should reflect the business's operating hours, not UTC time or the end user's timezone (the query timezone)
3. For multi-timezone tenants, each location's data should align with their local business day
4. Users expect "last month" to work on any date/time dimension

Current Workaround

We define separate string dimensions for each time component:

• name: local_date # type: string, no time features
• name: local_hour # type: string, no time features
• name: local_day_of_week # type: string, no time features
• name: local_month # type: string, no time features

This forces users to manually construct date ranges instead of using intuitive filters.

Proposed Solution

Add a dimension property to indicate "this time dimension is already in local time, don't apply query timezone":

Option 1: New localTime flag

dimensions:
  - name: local_date
    sql: "DATE_FORMAT(from_utc_timestamp({CUBE}.created_at, {Location.timezone}), 'yyyy-MM-dd')"
    type: time
    localTime: true  # Skip query timezone conversion

Option 2: New dimension type

dimensions:
  - name: local_date
    sql: "DATE_FORMAT(from_utc_timestamp({CUBE}.created_at, {Location.timezone}), 'yyyy-MM-dd')"
    type: local_time  # Acts like 'time' but without timezone conversion

Expected Behavior

With either approach:

• ✅ Support dateRange: "last month", "yesterday", etc.
• ✅ Support granularity: "day", "hour", etc.
• ✅ Support date range pickers in UI tools
• ❌ Do NOT apply query timezone parameter to this dimension
• ❌ Do NOT convert values (they're already in the desired timezone)

Example Query

{
  measures: ['Orders.count'],
  timeDimensions: [{
    dimension: 'Orders.local_date',  // Already in location's timezone
    dateRange: 'last month',         // Should work without timezone conversion
    granularity: 'day'
  }],
  timezone: 'America/New_York'  // Applied to other time dimensions, but NOT local_date
}

Benefits

1. Better UX: Users can use familiar time filters on local time dimensions
2. Correct semantics: "Last week" means last week in the business's timezone, not UTC
3. Multi-timezone support: Each location's data aligns with their operating hours
4. Reduced complexity: No need for workarounds with string dimensions
5. Cleaner schemas: Single dimension instead of multiple string components

Related

This is particularly important for:

• Multi-location businesses across timezones
• Analyzing patterns by business hours (not UTC hours)
• Any scenario where timestamps are pre-converted to local time in the data warehouse
• Sales reports, operational dashboards, and time-based analytics

[elijah-evans-deel]

+1 to this. We have this use case as well.

For key dates, we need to have a Timestamp fixed to UTC, fixed to specific tz, and then a version for the tenant's tz. And so we need to restrict the auto-conversion for specific timestamps

cc @daniel-brown-deel @AvilaJulio

[elijah-evans-deel]

@hank-sq my proposal would be to add a new parameter to timeDimensions

export type DimensionDefinition = {
  type: string;
  sql(): string;
  primaryKey?: true;
  ownedByCube: boolean;
  fieldType?: string;
  multiStage?: boolean;
  shiftInterval?: string;
  skipTimezoneConversion?: boolean;  // NEW
};

A few questions

1. How do you envision this working with custom granularities?
2. What if a user applies two date filters, one on a time field in UTC and one on a field in the user's timezone. What is your expected behavior here?
3. When a dateRange filter is applied to a timeDimension where skipTimezoneConversion = TRUE, do we agree that the literal value should be applied? i.e. --> This means users should think of the date range as applying to the local time values in the dimension.

[igorlukanin]

Hi @elijah-evans-deel 👋

To your points/questions 2 and 3 — see how Looker handles this; just skips the conversion for the time dimension while still converting filter parameters: https://docs.cloud.google.com/looker/docs/reference/param-field-convert-tz#things_to_consider

[elijah-evans-deel]

@igorlukanin What's the Cube opinion on how this should work? I'm happy to propose a more detailed spec and open a PR, if we're aligned on solution. I need this solved before EOY, so am motivated to get this done

@hank-sq let me know if you have thoughts.

cc @daniel-brown-deel @juan-domingos-deel

[hank-sq]

Thanks @igorlukanin and @elijah-evans-deel - I do have a work in progress PR where I was exploring what this might look like and here's where I was at for these questions. I opened the WIP PR as a draft if it's helpful to see the code here: #10208

I was exploring having a boolean 'local_time' on time dimensions, which if set, treats the timetamp for that dimension at UTC as the local time. This then causes:

1. Filters to ignore the timezone and treat the passed in value as the local time to apply the filter to (so it would treat '2025-01-01T00:00:00Z' and '2025-01-01T06:00:00Z' the same, as '2025-01-01T00:00:00 local) - and to not apply the query timezone to the dimension values.
2. Do not apply the query timezone to dimension values when used for grouping
3. Do not apply the query timezone when formatting/displaying the results

Good questions, here is what I was thinking:

How do you envision this working with custom granularities?

I think it should work just fine with them - the query timezone is not used.

What if a user applies two date filters, one on a time field in UTC and one on a field in the user's timezone. What is your expected behavior here?

Igor brings up Looker's handling, which is to still apply the query timezone to filters, but that seems odd to me. The implementation I was exploring would essentially treat the filter parameters as a local time (i.e. ignore the timezone provided, so it would treat '2025-01-01T00:00:00Z' and '2025-01-01T06:00:00Z' the same, as '2025-01-01T00:00:00 local').

I don't know if I love this though, so definitely open to alternatives or discussions.

When a dateRange filter is applied to a timeDimension where skipTimezoneConversion = TRUE, do we agree that the literal value should be applied? i.e. --> This means users should think of the date range as applying to the local time values in the dimension.

Yes, I think so if I'm understanding correctly.