sql_database doesn't support INTERVAL

[waterworthd-cim]

Feature description

I have a postgres source that has an INTERVAL datatype and I'm using sql_database as I'm using my own incremental strategy based on xmin. I'm getting a warning as sqltypes.INTERVAL is not one of the handled data types

https://github.com/dlt-hub/dlt/blob/3577222647898111cc675ec6416cb4d74eab5745/dlt/sources/sql_database/schema_types.py#L148

Then later on, I get an error

dlt.pipeline.exceptions.PipelineStepFailed: Pipeline execution failed at stage extract when processing package 1744337717.574294 with exception:

<class 'TypeError'>
Type is not JSON serializable: datetime.timedelta

Are you a dlt user?

Yes, I run dlt in production.

Use case

I have source data with INTERVAL datatype

Proposed solution

Support INTERVAL / datetime.timedelta

Related issues

No response

[waterworthd-cim]

I think the main issue is the failure to convert datetime.timedelta to JSON. I can probably use a type_adaptor callback to convert the postgres type to a more general sqltype but I still get a failure later on as (I assume the normaliser) does not support the python type datetime.timedelta? So for now I'm going to convert to text and convert back later with dbt

def type_adapter(sql_type):
    if isinstance(sql_type, sa.NUMERIC):
        return sa.FLOAT
    if isinstance(sql_type, sa.dialects.postgresql.types.INTERVAL):
        return sa.Interval
    return sql_type

[edwinvehmaanpera]

Had the same issue with Oracle INTERVAL DAY TO SECOND data type.

Which leads to same error TypeError: Type is not JSON serializable: datetime.timedelta