Query specified timeDimension granularity offset?

[hank-sq]

Problem

I'd like to be able to customize the 'offset' of timeDimension granularities at query time. For example, I want to support a client that maybe wants to group by a time dimension with daily granularity, but for that day to map to 2:30am -> 2:30am.

For example, if there's the following data with a created_at time at:

• 2025-01-01T00:00:00Z
• 2025-01-01T01:00:00Z
• 2025-01-01T02:00:00Z
• 2025-01-01T03:00:00Z

I want the ability to have the query timezone be UTC, group by created_at with granularity daily, but offset that granularity by -2.5 hours, with the expected result (date, count):

1. 2025-12-31, 3
2. 2025-01-01, 1

I can define custom granularities for pre-defined offsets, but I want this to be able to be supplied by clients at query time. This is a viable but non-ideal workaround for supporting hour shifts, but moving to supporting minute shifts causes a significant number of custom granularities and bloats the data model.

This would also be an alternative to supporting weekly aggregations on different starting days of week, without adding 7 custom granularities to every time dimension.

I could pass in a timezone that maps to the required shift - but this is extremely unintuitive to ask a client to do, and doesn't fully solve the problem (for example, I'd still want to respect DST change in the client's actual timezone).

Is there a way to do this with cube today?

[igorlukanin]

Hi @hank-sq 👋

For example, I want to support a client that maybe wants to group by a time dimension with daily granularity, but for that day to map to 2:30am -> 2:30am.

Could you please explain the business use case here? Why would anyone want to shift a day by 2.5 hours? I'm very curious to learn more. (Also, is this custom time-shift fixed for a specific user/customer/account/etc. or do you expect each of N users to be using M time-shifts at the same time?)

FYI, query performance and efficient caching are paramount for Cube (and the pre-aggregations feature is an important pillar of that). Any query-time parameters make efficient caching practically impossible. Did you test your PR with pre-aggs?

---

One use case I'm very much aware about is querying in the client time zone. It is covered with the timezone parameter in REST API queries, the time zone selector in Playground, the scheduled_refresh_time_zones configuration parameter, etc.

Consider this data model:

cubes:
  - name: makeshift_time_zones
    sql: >
      SELECT '2025-01-01T00:00:00Z'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-01T01:00:00Z'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-01T02:00:00Z'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-01T03:00:00Z'::TIMESTAMP AS time

    dimensions:
      - name: time
        sql: time
        type: time

    measures:
      - name: count
        type: count

By querying with the default (UTC) time zone, you get:

By querying with another timezone, you get results in that time zone:

---

One more thing: #9791. We're going to bring calendar cubes, which would allow overriding both custom and built-in time dimension granularities: https://cube-docs-prod-gjp9h47p6-cube-dev.vercel.app/product/data-modeling/concepts/calendar-cubes#overriding-granularities

I understand you'd probbaly still consider this a non-ideal solution but it can still help with "bloating the data model."