> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Implementing custom time dimension granularities

> This recipe shows examples of commonly used custom granularities.

## Use case

Sometimes, you might need to group the result set by units of time that are
different from [default granularities][ref-default-granularities] such as `week`
(starting on Monday) or `year` (starting on January 1).

Below, we explore the following examples of custom granularities:

* *Week starting on Sunday*, commonly used in the US and some other countries.
* *[Fiscal year][wiki-fiscal-year]* and *fiscal quarter*, commonly used in
  accounting and financial reporting.

## Data modeling

Consider the following data model. `interval` and `offset` parameters are used to
configure each custom granularity in `granularities`.

Note that custom granularities are also exposed via [proxy
dimensions][ref-proxy-granularity] so that we can conveniently query them via
[Playground][ref-playground] or BI tools connected via the [SQL API][ref-sql-api].
We can also use them in further calculations like rendering `fiscal_quarter_label`.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  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
  ```

  ```javascript title="JavaScript" theme={"dark"}
  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`
      }
    }
  })
  ```
</CodeGroup>

## Result

Querying this data modal would yield the following result:

<Frame>
  <img src="https://ucarecdn.com/c385c29d-e145-47da-8551-2be5efb1c268/" />
</Frame>

[ref-custom-granularities]: /reference/data-modeling/dimensions#granularities

[ref-default-granularities]: /docs/data-modeling/dimensions#time-dimensions

[wiki-fiscal-year]: https://en.wikipedia.org/wiki/Fiscal_year

[ref-playground]: /docs/explore-analyze/playground

[ref-sql-api]: /reference/core-data-apis/sql-api

[ref-proxy-granularity]: /docs/data-modeling/dimensions#time-dimension-granularity-references