search

wireservice / agate-sql

agate-sql adds SQL read/write support to agate.
https://agate-sql.readthedocs.io
MIT License
18 stars 15 forks source link

TimeDelta type doesn't work as expected with sqlite #12

Closed onyxfish closed 9 months ago

onyxfish commented 8 years ago

Intervals get converted to datetime

FYI @aklaver. Not sure there is much we can do about this. I've excluded it from the tests for the time being.

aklaver commented 8 years ago

Yes, that is the SQLA behavior in the absence of database native interval type: http://docs.sqlalchemy.org/en/rel_1_0/core/type_basics.html#sql-standard-types

class sqlalchemy.types.Interval(native=True, second_precision=None, day_precision=None) "... The Interval type deals with datetime.timedelta objects. In PostgreSQL, the native INTERVAL type is used; for others, the value is stored as a date which is relative to the “epoch” (Jan. 1, 1970). ..."

So someone using a database other then Postgres is going to be dealing in dates if they are using SQLA as the ORM to build and manipulate tables and data, outside of agate-sql. More likely is that they are going to be using a char field of some type with an interval string or a two column approach with an interval_qty field(some number type) and an interval_desc field(char field). In other words for case one, '3 days ' and in the second case 3, 'days'. In all cases sqlite is going to be the outlier, as its type system is flexible :)

onyxfish commented 8 years ago

Got it. I'm just going to leave this ticket open as documentation for the time being. The longer-term solution is probably just to add a list of database-specific details to the real docs.

onyxfish commented 8 years ago

@aklaver Btw, are you on Twitter? I wanted to credit you publicly for your work on this release, but couldn't find you there. (No worries if you're not interested, I just try to make sure folks get attention for their contributions.)

aklaver commented 8 years ago

No. I tried it for awhile, never caught on with me.

onyxfish commented 8 years ago

@aklaver Got it. Your name in the docs/commit history will have to do then. :+1:

jpmckinney commented 9 months ago

I'm not clear what would need to be added to the documentation to close this issue. As no one else has raised any similar issue in many years, I'll close.