[Feature] Support "Interval" function for dbt Models and Unit Testing

[Auric-Manteo]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I am running a dbt test on a working model and get a database error.
I can work around it by using dateadd instead of interval, but it is inconvenient.

Expected Behavior

The unit test runs without database errors.

Steps To Reproduce

Set up the unit test and the model and run dbt test --select integration_metrics,test_type:unit

integration_metrics_unit_test.yml:

version: 2

unit_tests:
  - name: integration_metrics
    description: "Test the aggregation of sent contracts for each integration and account."
    model: integration_metrics
    given:
      - input: ref('account_usage')
        rows:
          - {account_id: 1, extension_id: 101, type: 'agreement_publish', created_time: '2024-09-15', position_id: 1001, collection_id: 2001}
      - input: ref('extension_definition')
        rows:
          - {key: 'salesforce', category: 'sales_and_crm'}
    expect:
      rows:
        - {workspaces_contracts_sent_last_3_months: 2}

integration_metrics.sql:

    with
    account_usage as (select * from {{ ref('account_usage') }}),
    
    usage as (
        select
            extension_id,
            count(
                distinct case
                    when created_time between current_date - interval '6 month'
                        and current_date - interval '3 month'
                        then 1
                end
            ) as integration_user_count_last_3_to_6_months
        from account_usage
        group by
            extension_id
    ),
    
    workspace_usage as (
        select
            extension_id,
            listagg(distinct collection_id, ', ') within group (
                order by collection_id
            ) as workspace_ids
        from account_usage
        group by
            extension_id
    ),
    
    usage_total as (
        select ws_usage.workspace_ids as workspaces_contracts_sent_last_3_months
        from usage
        left join workspace_usage as ws_usage on usage.extension_id = ws_usage.extension_id
    )
    
    select coalesce(workspaces_contracts_sent_last_3_months, '') as workspaces_contracts_sent_last_3_months
    from usage_total

Relevant log output

dbt test --select integration_metrics,test_type:unit
06:10:50  Running with dbt=1.9.0-b2
06:10:50  Registered adapter: redshift=1.8.1
06:10:51  Found 185 models, 638 data tests, 3 seeds, 147 sources, 14 exposures, 674 macros, 2 unit tests
06:10:51  
06:10:51  Concurrency: 1 threads (target='default_target')
06:10:51  
06:11:05  1 of 1 START unit_test integration_metrics::integration_metrics ........................................................ [RUN]
...dbt-gi1dhiau/lib/python3.11/site-packages/dbt/adapters/sql/connections.py:93: UserWarning: DB-API extension cursor.connection used
  cursor.execute(sql, bindings)
06:11:06  1 of 1 ERROR integration_metrics::integration_metrics .................................................................. [ERROR in 1.16s]
06:11:06  
06:11:06  Finished running 1 unit test in 0 hours 0 minutes and 15.36 seconds (15.36s).
06:11:07  
06:11:07  Completed with 1 error, 0 partial successs, and 0 warnings:
06:11:07  
06:11:07    Runtime Error in unit_test integration_metrics (models/marts/standard/integration/integration_metrics_unit_tests.yml)
  An error occurred during execution of unit test 'integration_metrics'. There may be an error in the unit test definition: check the data types.
   Database Error
    Interval values with month or year parts are not supported
06:11:07  
06:11:07  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Environment

- OS:MacOS
- Python:3.9
- dbt-core:1.9.0
- dbt-redshift:1.8.1

Additional Context

Note that I broke down the example as much as I could.
It's not as simple as running a select on account usage which was very frustrating :D
I mentioned the bug in the dbt channel but didn't get any feedback yet (For reference so I can update the thread once the issue is resolve): https://getdbt.slack.com/archives/CBSQTAPLG/p1728993577116609