dbt-labs / dbt-labs/dbt-adapters

[Bug] dateadd function doesn't properly work with dbt-utils date_spine

Open
#1,554 0 comments 0 reactions 0 assignees View on GitHub
triage:product type:bug
Dominant language
Python
Stars
233
Forks
362
Avg merge
3d 22h
Merged PRs (30d)
9

Description

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [ ] dbt-redshift
- [ ] dbt-snowflake
- [x] dbt-spark

### Current Behavior

When running this macro in Spark or Databricks, the series is output as seconds instead of minutes:
```
{{ dbt_utils.date_spine(
datepart='minute',
start_date="cast('2025-01-01' as date)",
end_date="cast('2025-02-01' as date)",
) }}
```
This is because in the Spark adapter function dateadd, the function converts a timestamp to unixtime, takes the interval number times 60 to convert to minutes, and adds that: `cast({{interval}} * {{multiplier}} as int)`

When {{interval}} is passed in as not a plain number but a function, it causes problems. Specifically, with `dbt_utils.date_spine`, this is passed in as the {{interval}}: `row_number() over (order by generated_number) - 1`

This results in `row_number() over (order by generated_number) - 1 * 60` instead of what should be `(row_number() over (order by generated_number) - 1) * 60`.

### Expected Behavior

And easy fix would be to change `cast({{interval}} * {{multiplier}} as int)` to `cast(({{interval}}) * {{multiplier}} as int)` to avoid issues like this.

### Steps To Reproduce

1. Create a new model with this macro:

```
{{ dbt_utils.date_spine(
datepart='minute',
start_date="cast('2025-01-01' as date)",
end_date="cast('2025-02-01' as date)",
) }}
```
2. Check output of model
3. Confirm intervals are seconds instead of minutes

### Relevant log output

```shell

```

### Environment

```markdown
- OS:
- Python:
- dbt-adapters:
- :
```

### Additional Context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.