TimeDelta support

[onyxfish]

Possible?

[aklaver]

http://docs.sqlalchemy.org/en/rel_1_0/core/type_basics.html#generic-types

SQLAlchemy has the generic SQL type INTERVAL that outputs Python datetime.timedelta. which is what the agate TimeDelta is looking for. INTERVAL is a native data type for Postgres and ORACLE. On other databases INTERVAL it is stored as a date relative to the Unix epoch 1/1/1970. From the above I understand this to mean that the INTERVAL type will only show up in metadata from tables that use the interval type as in Postgres and Oracle. For other databases a date type will show up. In either case the type is covered. The only issue I can see is if a user is using SQLAlchemy as the ORM to create and use their tables outside agate and expect to see an INTERVAL type for a field that they pull against a database that does not support it natively i.e MySQL. They will still get the field it will just be a DATE type. The same goes for pushing data back into a table. The first solution that comes to mind is to create some way for the user to map fields they are using as INTERVAL, so agate-sql can use that SQLAlchemy type instead of DATE. This would not be needed in the Postgres and Oracle cases.

[onyxfish]

Hmmm, I think the "version 1" solution for this is documentation. Let's implement it the naive (database-agnostic) way and then add a note to the docs about db-specific support for TimeDelta. If anybody ever complains we can consider a more robust solution.

[aklaver]

I agree with the above. I started working on this, but seemed to have hit a snag on the SQLAlchemy side in regards to naming of Interval(INTERVAL). I filed a issue when them:

https://bitbucket.org/zzzeek/sqlalchemy/issues/3571/interval-vs-interval-and-python_type

One option is to special case this, but I would prefer to wait until the above issue is dealt with one way or another.

[onyxfish]

Ugh, just read through that issue. Fortunately I think the variability he describes in how the adapters return types is probably moot four purposes: all numeric types collapse to agate.Number() and all text types collapse to agate.Text().

Based on my reading of your back-and-forth it sounds like the best part forward for INTERVAL is just to hold off until now? Or is there a straightforward way to special case now you know the broader shape of the problem?

[aklaver]

Longer term I am going to see about getting python_type set for INTERVAL, by submitting a PR to SQLAlchemy. Short term I am thinking a special case. The generic Interval already has python_type set, it is just a matter of intercepting INTERVAL and faking a python_type for it.

[onyxfish]

Sounds good to me! 👍