🐛 Bug: Custom Weekly Granularities with Offsets Produce Inconsistent Results Due to Year Start Origin

[divij123]

Describe the bug
Custom weekly granularities with offsets produce inconsistent and unpredictable results because timeSeriesFromCustomInterval uses startOf('year') as the default origin. This causes week boundaries to shift unexpectedly based on what day of the week January 1st falls on, making it impossible to create reliable Tuesday/Wednesday/Thursday-based weekly intervals.

To Reproduce
Steps to reproduce the behavior:

1. Create a custom granularity with a weekly interval and offset to shift from Monday to Tuesday
2. Query data using this granularity across different calendar years
3. Compare the resulting week boundaries between years
4. Observe that the same configuration produces different weekday alignments

// In cube schema, time dimension with custom granularity
granularities: {
  tuesday_week: {
    title: "Tuesday Week",
    interval: "1 week",
    offset: `-6 days`
  }
}

Expected behavior
The tuesday_week granularity should consistently produce Tuesday-based week boundaries regardless of the calendar year:
2025-09-23T00:00:00.000 (Tuesday)
2025-09-30T00:00:00.000 (Tuesday)
2025-10-07T00:00:00.000 (Tuesday)
2025-10-14T00:00:00.000 (Tuesday)
2025-10-21T00:00:00.000 (Tuesday)
This behavior should be consistent across all years.

Screenshots
If applicable, add screenshots to help explain your problem.

Minimally reproducible Cube Schema
N/A

Version:
1.3.83

Additional context
Root Cause:
The issue is in packages/cubejs-client-core/src/time.ts (line ~214) and packages/cubejs-backend-shared/src/time.ts where the default origin is set to:

let origin = granularity.origin ? internalDayjs(granularity.origin) : internalDayjs().startOf('year');

Since January 1st can fall on any weekday, the offset calculations produce different results in different years:
2025: Jan 1 = Wednesday → offset "1 day" creates Thursday weeks (not Tuesday)
2024: Jan 1 = Monday → offset "1 day" creates Tuesday weeks (correct by accident)
2026: Jan 1 = Thursday → offset "1 day" creates Friday weeks (not Tuesday)
Proposed Fix:
Replace year start with first Monday of the year as default origin:

let origin = granularity.origin ? internalDayjs(granularity.origin) : getFirstMondayOfYear();

function getFirstMondayOfYear(): dayjs.Dayjs {
  const yearStart = internalDayjs().startOf('year');
  const jan1Weekday = yearStart.isoWeekday();
  const daysToFirstMonday = jan1Weekday === 1 ? 0 : (8 - jan1Weekday);
  return yearStart.add(daysToFirstMonday, 'day');
}

Secondary Issue:
Version 1.3.83 also introduced ESM build import errors that prevent proper module resolution in ES module environments.

Impact:
This bug makes it impossible to create reliable fiscal weeks, business reporting periods, or any custom weekly intervals that need to consistently start on specific weekdays other than Monday.

[gauravbodara]

+1

[igorlukanin]

Hi @divij123 @gauravbodara @csn2002,

I've tried to reproduce this with the following code example:

cubes:
  - name: weeks
    sql: >
      SELECT '2023-12-25'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-26'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-27'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-28'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-29'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-30'::TIMESTAMP AS time UNION ALL
      SELECT '2023-12-31'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-01'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-02'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-03'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-04'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-05'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-06'::TIMESTAMP AS time UNION ALL
      SELECT '2024-01-07'::TIMESTAMP AS time UNION ALL
      
      SELECT '2024-12-25'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-26'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-27'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-28'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-29'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-30'::TIMESTAMP AS time UNION ALL
      SELECT '2024-12-31'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-01'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-02'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-03'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-04'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-05'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-06'::TIMESTAMP AS time UNION ALL
      SELECT '2025-01-07'::TIMESTAMP AS time UNION ALL
      
      SELECT '2025-12-25'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-26'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-27'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-28'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-29'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-30'::TIMESTAMP AS time UNION ALL
      SELECT '2025-12-31'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-01'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-02'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-03'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-04'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-05'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-06'::TIMESTAMP AS time UNION ALL
      SELECT '2026-01-07'::TIMESTAMP AS time

    dimensions:
      - name: time_str
        sql: time
        type: string

      - name: time
        sql: time
        type: time

        granularities:
          - name: tuesday_week
            title: Tuesday Week
            interval: 1 week
            offset: -6 days

Here are the results that I got:

To the best of my understanding, everything looks to be correct. Could you please modify the example and/or point to an incorrect result on the screenshot? Thanks!

[igorlukanin]

As for "ESM Build Issues in v1.3.83", it would be great to have a separate ticket.