On Mon, Dec 30, 2019, at 1:27 PM, Chris Modzelewski wrote: > Hi There, > > So I have a bit of an (admittedly unusual) question: > > I understand that the Interval type is designed to operate using > datetime.timedelta on those SQL engines that natively support the > Interval data type (eg PostgreSQL). > > I also understand that when operating on SQL engines that do *not* > natively support the Interval data type, SQLAlchemy coerces > datetime.timedelta values to datetime.datetime values relative to the > epoch date. > > Is my understanding so far correct?
yes, the slight caveat is that we have recently learned that SQL Server's DATETIMEOFFSET datatype does intervals also and the Interval datatype isn't making use of that just yet. Also I dont think Interval is really used very much and we probably shouldn't have it as a built in type at this point as it's too opinionated. > > If so, here is the crux of the situation: given an ORM model class > with an Interval attribute, when introspecting either that model class > (i.e. the class of the model itself) OR when introspecting an instance > of that model class (presumably navigating across > InstrumentedAttributes and related Comparators) how can one > differentiate between: > A) an attribute that is still a native Interval type, > B) a “converted” Interval (now represented as a DATETIME type), and > C) a native DATETIME type that does not have any relationship to intervals reflection can't do this, it will see the datatype created by Interval on a non-supporting backend as DATETIME or whatever is on that backend. if you are only running on backends that support native interval, e.g. you're on Postgresql or Oracle (or in theory on SQL Server with some workarounds to use DATETIMEOFFSET), then you can see the correct type. Otherwise, from refection alone there is no way to know. > > I’m almost certain there’s a mechanism to do this differentiation by > navigating the internals, but I'm having trouble putting my finger on > how best to accomplish this. if you have an ORM model that is making explicit use of Interval, then you typically don't need to use reflection. So...if you have explict use of Interval but are also using reflection, there are ways to tell the reflection process that these columns should use Interval (see https://docs.sqlalchemy.org/en/13/core/reflection.html#overriding-reflected-columns ). But if your code doesn't use "Interval" anywhere, and you're reflecting a schema without any information up front about columns, on a non-supporting database you'll just see DATETIME types. > > > > My overall objective is to use this > differentiation to do appropriate datetime / timedelta arithmetic and > conversion based on whether the timedelta is to be persisted to the DB > as a native Interval type, or as a datetime relative to the epoch > time. > > Any help or guidance would be much appreciated! if you are building an application that expects to have a high degree of backend-agnosticism and for which you are hoping to do arithmetic with these objects, I would consider using native=False so that all backends have the same datetime datatype set up, or even use an integer-based type like the MyEpochType example at https://docs.sqlalchemy.org/en/13/core/custom_types.html#augmenting-existing-types, as it's easier to do "math" with integers than dates in a backend agnostic way. backend-agnostic date arithmetic is a little tricky because all the database do dates and intervals extremely differently, good luck ! > > All the best, > Chris > > Sent from my iPhone > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CACng_W8AwxAZp1ODeBBTsh%2B1v0hRcet%2Bw6cvdOmsfxwp8WRSLw%40mail.gmail.com. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2b324f87-f878-4937-abf3-3cbc905b931c%40www.fastmail.com.
