Implementing custom time dimension granularities

cubes:
  - name: custom_granularities
    sql: |
      SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-12-15'::TIMESTAMP AS timestamp

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

        granularities:
          - name: sunday_week
            interval: 1 week
            offset: -1 day

          - name: fiscal_year
            title: Federal fiscal year in the United States
            interval: 1 year
            offset: -3 months

          - name: fiscal_quarter
            title: Federal fiscal quarter in the United States
            interval: 1 quarter
            offset: -3 months
      
      - name: sunday_week
        sql: "{timestamp.sunday_week}"
        type: time
      
      - name: fiscal_year
        sql: "{timestamp.fiscal_year}"
        type: time
      
      - name: fiscal_quarter
        sql: "{timestamp.fiscal_quarter}"
        type: time

      - name: fiscal_quarter_label
        sql: |
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
        type: string

cube(`custom_granularities`, {
  sql: `
    SELECT '2024-01-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-02-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-03-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-04-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-05-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-06-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-07-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-08-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-09-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-10-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-11-15'::TIMESTAMP AS timestamp UNION ALL
    SELECT '2024-12-15'::TIMESTAMP AS timestamp
  `,

  dimensions: {
    timestamp: {
      sql: `timestamp`,
      type: `time`,

      granularities: {
        sunday_week: {
          interval: `1 week`,
          offset: `-1 day`
        },

        fiscal_year: {
          title: `Federal fiscal year in the United States`,
          interval: `1 year`,
          offset: `-3 months`
        },

        fiscal_quarter: {
          title: `Federal fiscal quarter in the United States`,
          interval: `1 quarter`,
          offset: `-3 months`
        }
      }
    },
    
    sunday_week: {
      sql: `${timestamp.sunday_week}`,
      type: `time`
    },

    fiscal_year: {
      sql: `${timestamp.fiscal_year}`,
      type: `time`
    },
      
    fiscal_quarter: {
      sql: `${timestamp.fiscal_quarter}`,
      type: `time`
    },

    fiscal_quarter_label: {
      sql: `
          'FY' || (EXTRACT(YEAR FROM {timestamp.fiscal_year}) + 1) ||
          '-Q' || EXTRACT(QUARTER FROM {timestamp.fiscal_quarter} + INTERVAL '3 MONTHS')
      `,
      type: `string`
    }
  }
})