feat(duckdb): Add transpilation support for nanoseconds used in date/time functions. #6617

fivetran-amrutabhimsenayachit · 2025-12-22T20:47:58Z

Issue: DuckDB does not support NANOSECOND as a unit in DATE_DIFF() or INTERVAL operations.

Solution: To enable Snowflake → DuckDB transpilation with nanosecond precision, following workaround implemented:
EPOCH_NS(): Converts TIMESTAMP_NS to nanoseconds since Unix epoch (returns INT64)
make_timestamp_ns(): Converts nanoseconds (INT64) to TIMESTAMP_NS

Snowflake:

SELECT DATEDIFF(NANOSECOND, '2023-01-01 10:00:00.000000000', '2023-01-01 10:00:00.123456789') AS datediff_result, DATEADD(NANOSECOND, 123456789, '2023-01-01 10:00:00.000000000') AS dateadd_result, TIMEDIFF(NANOSECOND, '2023-01-01 10:00:00.000000000', '2023-01-01 10:00:00.123456789') AS timediff_result, TIMEADD(NANOSECOND, 987654321, '2023-01-01 10:00:00.000000000') AS timeadd_result, TIMESTAMPDIFF(NANOSECOND, '2023-01-01 10:00:00.000000000', '2023-01-01 10:00:00.999999999') AS timestampdiff_result, TIMESTAMPADD(NANOSECOND, 555555555, '2023-01-01 10:00:00.000000000') AS timestampadd_result;


DATEDIFF_RESULT | DATEADD_RESULT | TIMEDIFF_RESULT | TIMEADD_RESULT | TIMESTAMPDIFF_RESULT | TIMESTAMPADD_RESULT

123456789 | 2023-01-01 10:00:00.123 | 123456789 | 2023-01-01 10:00:00.987 | 999999999 | 2023-01-01 10:00:00.555

Duckdb (after transpilation):

SELECT EPOCH_NS(CAST('2023-01-01 10:00:00.123456789' AS TIMESTAMP_NS)) - EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) AS datediff_result, MAKE_TIMESTAMP_NS(EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) + 123456789) AS dateadd_result, EPOCH_NS(CAST('2023-01-01 10:00:00.123456789' AS TIMESTAMP_NS)) - EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) AS timediff_result, MAKE_TIMESTAMP_NS(EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) + 987654321) AS timeadd_result, EPOCH_NS(CAST('2023-01-01 10:00:00.999999999' AS TIMESTAMP_NS)) - EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) AS timestampdiff_result, MAKE_TIMESTAMP_NS(EPOCH_NS(CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP_NS)) + 555555555) AS timestampadd_result

┌─────────────────┬───────────────────────────────┬─────────────────┬───────────────────────────────┬──────────────────────┬───────────────────────────────┐
│ datediff_result │        dateadd_result         │ timediff_result │        timeadd_result         │ timestampdiff_result │      timestampadd_result      │
│      int64      │         timestamp_ns          │      int64      │         timestamp_ns          │        int64         │         timestamp_ns          │
├─────────────────┼───────────────────────────────┼─────────────────┼───────────────────────────────┼──────────────────────┼───────────────────────────────┤
│    123456789    │ 2023-01-01 10:00:00.123456789 │    123456789    │ 2023-01-01 10:00:00.987654321 │      999999999       │ 2023-01-01 10:00:00.555555555 │
└─────────────────┴───────────────────────────────┴─────────────────┴───────────────────────────────┴──────────────────────┴───────────────────────────────┘

Function Mapping Summary:

Snowflake Function	DuckDB Transpilation
DATEDIFF(NANOSECOND, start, end)	EPOCH_NS(end) - EPOCH_NS(start)
DATEADD(NANOSECOND, n, ts)	MAKE_TIMESTAMP_NS(EPOCH_NS(ts) + n)
TIMEDIFF(NANOSECOND, start, end)	EPOCH_NS(end) - EPOCH_NS(start)
TIMEADD(NANOSECOND, n, ts)	MAKE_TIMESTAMP_NS(EPOCH_NS(ts) + n)
TIMESTAMPDIFF(NANOSECOND, start, end)	EPOCH_NS(end) - EPOCH_NS(start)
TIMESTAMPADD(NANOSECOND, n, ts)	MAKE_TIMESTAMP_NS(EPOCH_NS(ts) + n)

github-actions · 2025-12-22T21:00:04Z

SQLGlot Integration Test Results

Comparing:

this branch (sqlglot:RD-1069375-timediff, sqlglot version: RD-1069375-timediff)
baseline (main, sqlglot version: 28.5.1.dev30)

⚠️ Limited to dialects: duckdb, snowflake

By Dialect

dialect	main	sqlglot:RD-1069375-timediff	difference	links
duckdb -> duckdb	4003/4003 passed (100.0%)	4003/4003 passed (100.0%)	No change	full result / delta
snowflake -> duckdb	626/1085 passed (57.7%)	626/1085 passed (57.7%)	No change	full result / delta
snowflake -> snowflake	981/1085 passed (90.4%)	981/1085 passed (90.4%)	No change	full result / delta

Overall

main: 6173 total, 5610 passed (pass rate: 90.9%), sqlglot version: 28.5.1.dev30

sqlglot:RD-1069375-timediff: 6173 total, 5610 passed (pass rate: 90.9%), sqlglot version: RD-1069375-timediff

Difference: No change

VaggelisD

Nice work @fivetran-amrutabhimsenayachit! Any time related transpilation is tricky by nature, leaving a few comments:

sqlglot/dialects/duckdb.py

tests/dialects/test_snowflake.py

…time functions

- Remove redundant TIMESTAMP_NS casting in _timediff_sql (lines 268-271) * Previously cast to TIMESTAMP_NS before calling _handle_nanosecond_diff * _handle_nanosecond_diff already handles casting via exp.cast (which avoids recasting) * Now passes expressions directly, matching _date_diff_sql pattern - Fix TIMEDIFF test case to use valid Snowflake syntax * Changed from TIME literal '10:00:00.000000000' * To TIMESTAMP with CAST: CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP) * TIME literals don't work with TIMEDIFF in Snowflake - Fix TIMEADD test case to use valid Snowflake syntax * Changed from TIME literal '10:00:00.000000000' * To TIMESTAMP with CAST: CAST('2023-01-01 10:00:00.000000000' AS TIMESTAMP) * TIME literals don't work with TIMEADD in Snowflake Addresses review comments from VaggelisD on PR #6617

sqlglot/dialects/duckdb.py

- Use expression.unit property accessor instead of expression.args.get('unit') * Updated in _timediff_sql, date_delta_to_binary_interval_op, _date_diff_sql * More concise and idiomatic - Remove unnecessary docstring from _is_nanosecond_unit * Function name is self-explanatory - Keep _unwrap_cast helper function * Necessary to avoid nested casts like CAST(CAST(x AS TIMESTAMP) AS TIMESTAMP_NS) * exp.cast only avoids recasting to SAME type, not DIFFERENT types * Example: CAST('2023-01-01' AS TIMESTAMP) → without unwrap → CAST(CAST(...) AS TIMESTAMP_NS) * With unwrap: extracts '2023-01-01' → CAST('2023-01-01' AS TIMESTAMP_NS) - cast parameter not needed for NANOSECOND handling * NANOSECOND operations require EPOCH_NS/make_timestamp_ns * These functions require TIMESTAMP_NS type * Must always cast regardless of cast parameter * cast parameter only applies to base implementation's interval operations Addresses review comments from georgesittas on PR #6617

…g tests for simplicity

VaggelisD

Nice work once again @fivetran-amrutabhimsenayachit, leaving a few nits to consider, feel free to merge afterwards:

VaggelisD · 2025-12-24T09:28:53Z

sqlglot/dialects/duckdb.py

+def date_delta_to_binary_interval_op(
+    cast: bool = True,
+) -> t.Callable[[DuckDB.Generator, DATETIME_DELTA], str]:
+    """DuckDB override to handle NANOSECOND operations; delegates other units to base."""
+    base_impl = base_date_delta_to_binary_interval_op(cast=cast)


Lets prefix the function with _ since its local to DuckDB, by doing so we can also leave the import name as is e.g:

```suggestion def _date_delta_to_binary_interval_op( cast: bool = True, ) -> t.Callable[[DuckDB.Generator, DATETIME_DELTA], str]: """DuckDB override to handle NANOSECOND operations; delegates other units to base.""" base_impl = date_delta_to_binary_interval_op(cast=cast)

VaggelisD · 2025-12-24T09:30:30Z

sqlglot/dialects/duckdb.py

+    if _is_nanosecond_unit(unit):
+        return _handle_nanosecond_diff(self, expression.this, expression.expression)


[Nit] We can inline the implementations of both _handle_nanosecond_diff and _handle_nanosecond_add directly under their branches now that they're both small enough after the recent changes, leaving it up to you

VaggelisD · 2025-12-24T09:31:41Z

sqlglot/dialects/duckdb.py

+    """DuckDB override to handle NANOSECOND operations; delegates other units to base."""
+    base_impl = base_date_delta_to_binary_interval_op(cast=cast)
+
+    def duckdb_date_delta_sql(self: DuckDB.Generator, expression: DATETIME_DELTA) -> str:


[Nit] Lets also prefix this with _

VaggelisD · 2025-12-24T09:35:50Z

Forgot you'll be away on holidays, I'll merge this and apply the comments

fivetran-amrutabhimsenayachit self-assigned this Dec 22, 2025

fivetran-amrutabhimsenayachit requested review from fivetran-BradfordPaskewitz and georgesittas December 22, 2025 20:50

VaggelisD reviewed Dec 23, 2025

View reviewed changes

sqlglot/dialects/duckdb.py Outdated Show resolved Hide resolved

sqlglot/dialects/duckdb.py Outdated Show resolved Hide resolved

tests/dialects/test_snowflake.py Outdated Show resolved Hide resolved

tests/dialects/test_snowflake.py Outdated Show resolved Hide resolved

fivetran-amrutabhimsenayachit added 2 commits December 23, 2025 11:57

feat(duckdb): Add transpilation support for nanoseconds used in date/…

1fa600a

…time functions

fivetran-amrutabhimsenayachit force-pushed the RD-1069375-timediff branch from 2296a92 to c8a9ce5 Compare December 23, 2025 17:04

fivetran-amrutabhimsenayachit requested a review from VaggelisD December 23, 2025 17:05

georgesittas reviewed Dec 23, 2025

View reviewed changes

fivetran-amrutabhimsenayachit requested a review from georgesittas December 23, 2025 19:30

feat(duckdb): Remove the unwrapping logic and remove the correspondin…

5dc6181

…g tests for simplicity

VaggelisD approved these changes Dec 24, 2025

View reviewed changes

VaggelisD merged commit 4aea018 into main Dec 24, 2025
9 checks passed

VaggelisD deleted the RD-1069375-timediff branch December 24, 2025 09:35

VaggelisD mentioned this pull request Dec 24, 2025

Chore: Refactor PR 6617 #6630

Merged

		if _is_nanosecond_unit(unit):
		return _handle_nanosecond_diff(self, expression.this, expression.expression)

feat(duckdb): Add transpilation support for nanoseconds used in date/time functions. #6617

feat(duckdb): Add transpilation support for nanoseconds used in date/time functions. #6617

Conversation

fivetran-amrutabhimsenayachit commented Dec 22, 2025

Uh oh!

github-actions bot commented Dec 22, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

SQLGlot Integration Test Results

By Dialect

Overall

Uh oh!

VaggelisD left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

VaggelisD left a comment

Choose a reason for hiding this comment

Uh oh!

VaggelisD Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

VaggelisD Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

VaggelisD Dec 24, 2025

Choose a reason for hiding this comment

Uh oh!

VaggelisD commented Dec 24, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

github-actions bot commented Dec 22, 2025 •

edited

Loading