feat(duckdb): Add transpilation support for PREVIOUS_DAY function

[fivetran-amrutabhimsenayachit]

Snowflake's PREVIOUS_DAY(date, day_name) function is not supported in DuckDB. SQLGlot passes the PREVIOUS_DAY function through unchanged, causing a runtime error in DuckDB: "Scalar Function with name previous_day does not exist!"

DuckDB lacks a native PREVIOUS_DAY function, requiring synthesis using:

• Use ISODOW(date) for ISO day of week (1=Monday through 7=Sunday) to check what day of the week it is for current and target dates.
• Date arithmetic with INTERVAL to calculate number of days to go forward/backward
• PREVIOUS_DAY: [((current_dow - target_dow - 1 + 7) % 7) + 1]
• CAST to ensure DATE return type

After:

Transpilation:
python3 -c "import sqlglot; print(sqlglot.transpile(\"SELECT PREVIOUS_DAY(DATE '2024-01-15', 'Monday') AS prev_monday, PREVIOUS_DAY(DATE '2024-01-15', 'Tu') AS prev_tuesday, PREVIOUS_DAY(DATE '2024-01-15', 'We') AS prev_wednesday, PREVIOUS_DAY(DATE '2024-01-15', 'Th') AS prev_thursday, PREVIOUS_DAY(DATE '2024-01-15', 'Fr') AS prev_friday, PREVIOUS_DAY(DATE '2024-01-15', 'Sa') AS prev_saturday, PREVIOUS_DAY(DATE '2024-01-15', 'Su') AS prev_sunday, PREVIOUS_DAY(TIMESTAMP '2024-01-15 10:30:45', 'Mo') AS prev_monday_from_ts, PREVIOUS_DAY(NULL, 'Monday') AS null_date, PREVIOUS_DAY(DATE '2024-01-15', NULL) AS null_dow\", read='snowflake', write='duckdb')[0])"
-->
SELECT CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 1 + 6) % 7 + 1) DAY AS DATE) AS prev_monday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 2 + 6) % 7 + 1) DAY AS DATE) AS prev_tuesday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 3 + 6) % 7 + 1) DAY AS DATE) AS prev_wednesday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 4 + 6) % 7 + 1) DAY AS DATE) AS prev_thursday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 5 + 6) % 7 + 1) DAY AS DATE) AS prev_friday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 6 + 6) % 7 + 1) DAY AS DATE) AS prev_saturday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 7 + 6) % 7 + 1) DAY AS DATE) AS prev_sunday, CAST(CAST('2024-01-15 10:30:45' AS TIMESTAMP) - INTERVAL ((ISODOW(CAST('2024-01-15 10:30:45' AS TIMESTAMP)) - 1 + 6) % 7 + 1) DAY AS DATE) AS prev_monday_from_ts, CAST(NULL AS DATE) AS null_date, CAST(NULL AS DATE) AS null_dow


DuckDB:
 duckdb -c "SELECT CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 1 + 6) % 7 + 1) DAY AS DATE) AS prev_monday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 2 + 6) % 7 + 1) DAY AS DATE) AS prev_tuesday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 3 + 6) % 7 + 1) DAY AS DATE) AS prev_wednesday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 4 + 6) % 7 + 1) DAY AS DATE) AS prev_thursday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 5 + 6) % 7 + 1) DAY AS DATE) AS prev_friday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 6 + 6) % 7 + 1) DAY AS DATE) AS prev_saturday, CAST(CAST('2024-01-15' AS DATE) - INTERVAL ((ISODOW(CAST('2024-01-15' AS DATE)) - 7 + 6) % 7 + 1) DAY AS DATE) AS prev_sunday, CAST(CAST('2024-01-15 10:30:45' AS TIMESTAMP) - INTERVAL ((ISODOW(CAST('2024-01-15 10:30:45' AS TIMESTAMP)) - 1 + 6) % 7 + 1) DAY AS DATE) AS prev_monday_from_ts, CAST(NULL AS DATE) AS null_date, CAST(NULL AS DATE) AS null_dow"
┌─────────────┬──────────────┬────────────────┬───────────────┬─────────────┬───────────────┬─────────────┬─────────────────────┬───────────┬──────────┐
│ prev_monday │ prev_tuesday │ prev_wednesday │ prev_thursday │ prev_friday │ prev_saturday │ prev_sunday │ prev_monday_from_ts │ null_date │ null_dow │
│    date     │     date     │      date      │     date      │    date     │     date      │    date     │        date         │   date    │   date   │
├─────────────┼──────────────┼────────────────┼───────────────┼─────────────┼───────────────┼─────────────┼─────────────────────┼───────────┼──────────┤
│ 2024-01-08  │ 2024-01-09   │ 2024-01-10     │ 2024-01-11    │ 2024-01-12  │ 2024-01-13    │ 2024-01-14  │ 2024-01-08          │ NULL      │ NULL     │
└─────────────┴──────────────┴────────────────┴───────────────┴─────────────┴───────────────┴─────────────┴─────────────────────┴───────────┴──────────┘

[github-actions]

SQLGlot Integration Test Results

Comparing:

• this branch (sqlglot:RD-1069371-previous-day, sqlglot version: RD-1069371-previous-day)
• baseline (main, sqlglot version: 28.6.1.dev1)

⚠️ Limited to dialects: duckdb, snowflake

By Dialect

dialect	main	sqlglot:RD-1069371-previous-day	difference	links
duckdb -> duckdb	4003/4003 passed (100.0%)	4003/4003 passed (100.0%)	No change	full result / delta
snowflake -> duckdb	599/847 passed (70.7%)	599/847 passed (70.7%)	No change	full result / delta
snowflake -> snowflake	847/847 passed (100.0%)	847/847 passed (100.0%)	No change	full result / delta

Overall

main: 5697 total, 5449 passed (pass rate: 95.6%), sqlglot version: 28.6.1.dev1

sqlglot:RD-1069371-previous-day: 5697 total, 5449 passed (pass rate: 95.6%), sqlglot version: RD-1069371-previous-day

Difference: No change

[georgesittas]

@fivetran-amrutabhimsenayachit nice work. The feedback I gave for #6728 applies to this PR as well.

Let's consolidate NEXT_DAY and PREVIOUS_DAY so that there's only one generator method and depending on the expression type, it adjusts the arithmetic appropriately. If we do this, then the comment I left re: removing the TRANSFORMS entry does not need to be addressed, since we will have a helper function at the global namespace and we'll reference in TRANSFORMS.

[fivetran-amrutabhimsenayachit]

@fivetran-amrutabhimsenayachit nice work. The feedback I gave for #6728 applies to this PR as well.

Let's consolidate NEXT_DAY and PREVIOUS_DAY so that there's only one generator method and depending on the expression type, it adjusts the arithmetic appropriately. If we do this, then the comment I left re: removing the TRANSFORMS entry does not need to be addressed, since we will have a helper function at the global namespace and we'll reference in TRANSFORMS.

Consolidated NEXT_DAY and PREVIOUS_DAY implementation. Refer #6728

[georgesittas]

Closing this in favor of 6278 then, thanks!

[fivetran-amrutabhimsenayachit]

Closing this PR as NEXT_DAY and PREVIOUS_DAY implementations have been consolidated in #6728