# Dynamically Change Date Granularity Based on Filter Timeframe

**URL:** <https://community.omni.co/t/dynamically-change-date-granularity-based-on-filter-timeframe/367>\
**Category:** Patterns\
**Tags:** filtering\
**Created:** [July 23, 2025, 5:27pm UTC](https://community.omni.co/t/dynamically-change-date-granularity-based-on-filter-timeframe/367 "2025-07-23T17:27:31Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![adrienb](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/adrienb/32/385_2.png) [@adrienb](https://community.omni.co/u/adrienb)\
**Post date:** [July 23, 2025, 5:27pm UTC](https://community.omni.co/t/dynamically-change-date-granularity-based-on-filter-timeframe/367/1 "2025-07-23T17:27:31Z")

</div>

When working with time series data, you may be commonly switching between hourly/daily/weekly or even other time granularities. Omni provides a built-in way to flip between these options using the [Time Frame Switcher](https://docs.omni.co/docs/visualization-and-dashboards/dashboards#time-frame-switcher) control.

However, maybe you want this timeframe to switch automatically based on the date filter selection, such as showing you hourly numbers when filtered to \< 2 days of data and flipping to daily when \> 2 days. Here’s a working pattern to do this in Omni (see video at the end for a walkthrough!):

1. Figure out which date field will drive all of this, and add it as a filter to your query/dashboard
2. Open the Workbook IDE (Model → Model layers → Workbook) and navigate to the view file (shortcut: click the three dots next to view name → Modeling → Go to definition)
3. Paste the code below as a new dimension and modify the field/view references. Also modify the threshold and/or add criteria as needed
4. Back in the workbook, build your query using this new Dynamic Date Part and any other fields you need

That’s it! Now you should observe the dynamic date aggregating differently when above/below the threshold(s) you specified!

#### Snowflake

```auto
dynamic_date_part:
    sql: |
      case
        when timestampdiff(hour, {{filters.omni_dbt_ecomm __order_items.created_at.range_start}}, {{filters.omni_dbt_ecomm__ order_items.created_at.range_end}}) <= 24*2
          then TO_CHAR(${omni_dbt_ecomm__order_items.created_at[hour]}, 'MM-DD-YYYY HH24:MI')
        else TO_CHAR(${omni_dbt_ecomm__order_items.created_at[date]}, 'MM-DD-YYYY')
      end
    label: Dynamic Time Frame
    description: Switches between daily and hourly based on the dates selected

```

#### BigQuery

```auto
dynamic_date_part:
    sql: |
      case
        when timestamp_diff({{filters.omni_dbt_ecomm __order_items.created_at.range_start}}, {{filters.omni_dbt_ecomm__ order_items.created_at.range_end}}, hour) <= 24*2
          then cast(${omni_dbt_ecomm__order_items.created_at[hour]} as string)
        else FORMAT_TIMESTAMP('%B %d, %Y',${omni_dbt_ecomm__order_items.created_at[date]})
      end
    label: Dynamic Time Frame
    description: Switches between daily and hourly based on the dates selected

```

#### Other Dialects

This should work with all SQL dialects, just replace the `timestampdiff` and `TO_CHAR` syntax with the syntax required by your dialect.

## Video Walkthrough

https://www.loom.com/embed/e680b4bf0d8a4f46a47e07b024781c07?sid=3dae03e3-7da1-4c06-8bf9-c34bb352c982

---

<div class="post-metadata">

**Author:** ![Jiro](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/jiro/32/268_2.png) [@Jiro](https://community.omni.co/u/Jiro)\
**Post date:** [October 27, 2025, 4:48pm UTC](https://community.omni.co/t/dynamically-change-date-granularity-based-on-filter-timeframe/367/2 "2025-10-27T16:48:34Z")

</div>

This is a super useful pattern for keeping the chart legible as you traverse larger ranges of data.

Worth noting that you can combine this with PoP (best when limited to “prior period”) if you just divide the datediff() by 2 and wrap in ROUND() function.

Also if you want to allow end users to override the automatic grain selection (like a timeframe swapper) you can use a field switcher instead, setting the dynamic timeframe field as the default option and the grabbing the timeframes of the actual date field as alternative options.

This lets the user see the trend in the automated grain but then decided they want to see it more granular and override the automation.