cube-js / cube-js/cube

Support for local time dimensions without query timezone conversion

Open
#10,166 7 comments 3 reactions 1 assignee Claimed by @igorlukanin View on GitHub
data modeling
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## 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.

```yaml
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

```yaml
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

```yaml
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

```js
{
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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.