Chapter 8, dbt - strptime issue

leminkhoa commented 4 months ago

In chapter 8, dbt section, the file models/atp/matches.sql has errors when compiling sql query.

This will cause error

Runtime Error in model matches (models/atp/matches.sql)
  Binder Error: No function matches the given name and argument types 'strptime(BIGINT, STRING_LITERAL)'. You might need to add explicit type casts.
        Candidate functions:
        strptime(VARCHAR, VARCHAR) -> TIMESTAMP
        strptime(VARCHAR, VARCHAR[]) -> TIMESTAMP

The issue happened for strptime function. I did check the documentation of duckdb and it requires the first argument would be of type varchar Reference: https://duckdb.org/docs/sql/functions/dateformat.html

The revised code would be as follows:

{{ config(
    materialized='external', 
    location='output/matches.parquet', 
    format='parquet'
) }} 

WITH noWinLoss AS (
    SELECT COLUMNS(col -> 
      NOT  regexp_matches(col, 'w_.*') AND
      NOT regexp_matches(col, 'l_.*')
    ) 
    FROM {{ source('github', 'matches_file') }}  
)

SELECT * REPLACE (
    cast(strptime(cast(tourney_date AS VARCHAR), '%Y%m%d') AS date) as tourney_date
)
FROM noWinLoss

leminkhoa commented 4 months ago

My library version:

dbt-duckdb 1.8.2
duckdb 1.0.0

michael-simons commented 2 days ago

@mneedham @jexp Care to have look?

duckdb-in-action / examples

Chapter 8, dbt - strptime issue #2