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