Interval vs DATETIME in ORM Internals

[Chris Modzelewski]

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?

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

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. 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!

All the best,
Chris

Sent from my iPhone

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CACng_W8AwxAZp1ODeBBTsh%2B1v0hRcet%2Bw6cvdOmsfxwp8WRSLw%40mail.gmail.com.