DATEDIFF does not consistently cast params to date

[kyle-cheung]

read Snowflake
write DuckDB

Depending on the complexity of the params in a DATEDIFF transpile does not consistently cast the params to the intended datatype, and depending on the date part could produce incorrect results

Snowflake is capable of ingesting varchars as params to DATEDIFF

SELECT 
    datediff(year, TO_CHAR(current_timestamp - interval '5 years', 'YYYY-MM-DD'), '2025-01-01'),
    datediff(year, current_timestamp - interval '5 years', '2025-01-01'),
    datediff(seconds, '2020-01-01', '2025-10-12 00:56:42.345 +0000'),
    datediff(seconds, '2020-01-01', '2025-05-01'::varchar),
    datediff(hours, '12:30'::time, '14:30'::time);

However, DuckDB is not.

SQLGlot will cast varchars to dates sometimes

import sqlglot
from sqlglot.optimizer import optimize


sql = """
SELECT 
    datediff(seconds, '2020-01-01', '2025-05-01'::varchar);
"""

sqlglot.transpile(sql, "snowflake", "duckdb", pretty=True)[0]
> SELECT
  DATE_DIFF('SECOND', CAST('2020-01-01' AS DATE), CAST('2025-05-01' AS TEXT))

In the event that there are actual timestamp values, it could produce incorrect results

# sqlglot.transpile(SELECT datediff('second', '2020-01-01', '2025-10-12 00:56:42.345 +0000') , "snowflake", "duckdb", pretty=True)[0]

SELECT
  DATE_DIFF('SECOND', CAST('2020-01-01' AS DATE), CAST('2025-10-12 00:56:42.345 +0000' AS DATE))

> 182390400 
-- This should be 182393802

Might be safer to cast everything to a TIMESTAMP unless it is explicitly casted to a TIME

Reproducible Code

import sqlglot
from sqlglot.optimizer import optimize


sql = """
SELECT 
    datediff(year, TO_CHAR(current_timestamp - interval '5 years', 'YYYY-MM-DD'), '2025-01-01'),
    datediff(year, current_timestamp - interval '5 years', '2025-01-01'),
    datediff(seconds, '2020-01-01', '2025-10-12 00:56:42.345 +0000'),
    datediff(seconds, '2020-01-01', '2025-05-01'::varchar),
    datediff(hours, '12:30'::time, '14:30'::time);
"""

transpiled_sql = sqlglot.transpile(sql, "snowflake", "duckdb", pretty=True)[0]

>
    SELECT
      DATE_DIFF(
        'YEAR',
        STRFTIME(CURRENT_TIMESTAMP - INTERVAL '5' YEARS, '%Y-%m-%d'),
        CAST('2025-01-01' AS DATE)
      ),
      DATE_DIFF('YEAR', CURRENT_TIMESTAMP - INTERVAL '5' YEARS, CAST('2025-01-01' AS DATE)),
      DATE_DIFF('SECOND', CAST('2020-01-01' AS DATE), CAST('2025-10-12 00:56:42.345 +0000' AS DATE)),
      DATE_DIFF('SECOND', CAST('2020-01-01' AS DATE), CAST('2025-05-01' AS TEXT)),
      DATE_DIFF('HOUR', CAST('12:30' AS TIME), CAST('14:30' AS TIME))

Official Documentation
https://docs.snowflake.com/en/sql-reference/functions/datediff
https://duckdb.org/docs/stable/sql/functions/date.html#date_diffpart-startdate-enddate

[georgesittas]

I addressed the issue of not inferring the timestamp(tz) type. Handling timezone parts like +0000 is out of scope for now, because they don't appear to work out-of-the-box in DuckDB:

D select datediff('seconds', '2020-01-01', cast('2025-10-12 00:56:42.345 +0000' as timestamptz));
Conversion Error:
Unknown TimeZone '+0000'!

Candidate time zones: "GMT0"

LINE 1: select datediff('seconds', '2020-01-01', cast('2025-10-12 00:56:42.345 +0000' as timestamptz));
                                                 ^

P.S.: contributing PRs would be appreciated for future issues you encounter.

[kyle-cheung]

Thank you @georgesittas !