sqlalchemy: Incorrect results or errors when adding a datetime.timedelta() value to a column

Describe the bug When using pymssql and a timedelta is added to a column which has DateTime format, an additional 70 years added beside the specified value.

Column:

end_time = Column(DateTime, nullable=False)

Update:

session.query(Auction).filter_by(id=auction.id).update({Auction.end_time: Auction.end_time + datetime.timedelta(seconds=10)})

Expected behavior 10 seconds are added to the original end_time value.

To Reproduce Run the following code.

#!/usr/bin/python3

import datetime

import sqlalchemy.ext.declarative as dec
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Column, Integer, DateTime
from contextlib import contextmanager


engine = create_engine("mssql+pymssql://<user>:<password>@<ip>:1433/<db>", echo=True)
session_factory = sessionmaker(bind=engine)
DbSession = scoped_session(session_factory)

@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = DbSession()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()
        DbSession.remove()


Base = dec.declarative_base()


class Auction(Base):
    __tablename__ = "auction"
    id = Column(Integer, primary_key=True, autoincrement=True)
    end_time = Column(DateTime, nullable=False)

Auction.metadata.create_all(engine, checkfirst=True)

with session_scope() as session:
    auction = Auction()

    auction.end_time = datetime.datetime(2020, 10, 27, 9, 55, 31)

    session.add(auction)
    session.commit()

    session.query(Auction).filter_by(id=auction.id).update({Auction.end_time: Auction.end_time + datetime.timedelta(seconds=10)})
    session.commit()

    modified_auction = session.query(Auction).filter_by(id=auction.id).first()

    if modified_auction.end_time != datetime.datetime(2020, 10, 27, 9, 55, 31) + datetime.timedelta(seconds=10):
        print("ERROR", modified_auction.end_time.isoformat())

Versions.

OS: Ubuntu 20.04.1 LTS
Python: Python 3.8.5 (default, Jul 28 2020, 12:59:40)
SQLAlchemy: 1.3.19
Database: Docker container from mcr.microsoft.com/mssql/server:2017-CU8-ubuntu image
DBAPI: pymssql 2.1.5

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 15 (15 by maintainers)

Most upvoted comments

Okay, thanks for the pointers. To be clear, I wasn’t suggesting a full implementation of +/- date arithmetic for drivers that don’t natively support it like psycopg2 does …

cnxn = psycopg2.connect(
    host="192.168.0.199", user="scott", password="tiger", dbname="mydb"
)
crsr = cnxn.cursor()
crsr.execute("CREATE TABLE gh_sqla_5672 (id int primary key, dtm timestamp)")
crsr.execute(
    "INSERT INTO gh_sqla_5672 (id, dtm) VALUES (1, '2001-01-01 00:00:00')"
)
crsr.execute(
    "SELECT dtm + %s AS foo FROM gh_sqla_5672 WHERE id = 1",
    (datetime.timedelta(seconds=10),),
)
print(crsr.fetchone()[0])  # 2001-01-01 00:00:10

… I was looking for the most straightforward way to warn/raise if somebody tries it when the underlying database/DBAPI “doesn’t do that”. mssql+pymssql is actually the one particular case here because it returns an incorrect result without complaint; the other failing cases (mssql+pyodbc, mysql, and sqlite) will raise an exception which is a pretty good hint that the operation is not supported. 😉

gordthompson on Oct 31, 2020

so as mentioned earlier we don’t implement date arithmetic operators. however this kind of issue could be prevented by at least having the operators that we implement for other systems, do something appropriate, like if dateadd is available, have the “+” sign render a dateadd, and for backends where we know we aren’t doing it, particularly SQLite where you need to convert both sides of an expression into an epoch to do date arithmetic, just raise an exception or emit a warning.

this is a lot of work for little positive result so it has not been prioritized for many years.

zzzeek on Oct 27, 2020

hey @gordthompson set synchronize_session=False and run that UPDATE again like that, just to see if the SQL part works. we don’t need to exercise the ORM part here.

zzzeek on Oct 26, 2020

I am able to reproduce your issue:

import datetime

import pymssql
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

connection_uri = "mssql+pymssql://scott:tiger^5HHH@192.168.0.199/test"
engine = sa.create_engine(connection_uri, echo=True)

Base = declarative_base()


class Thing(Base):
    __tablename__ = "gh_sqla_5672"
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=False)
    dtm = sa.Column(sa.DateTime)

    def __repr__(self):
        return f"<Thing(id={self.id}, dtm={self.dtm})>"


Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

print(sa.__version__)  # 1.3.20
print(pymssql.__version__)  # 2.1.5

thing1 = Thing(id=1, dtm=datetime.datetime(2001, 1, 1, 1, 1, 1))
session.add(thing1)
session.commit()
print(thing1)  # <Thing(id=1, dtm=2001-01-01 01:01:01)>

session.query(Thing).update(
    {Thing.dtm: Thing.dtm + datetime.timedelta(seconds=10)}
)
"""sql rendered:
UPDATE gh_sqla_5672 SET dtm=(gh_sqla_5672.dtm + %(dtm_1)s)
{'dtm_1': datetime.datetime(1970, 1, 1, 0, 0, 10)}
"""
session.commit()
print(thing1)  # <Thing(id=1, dtm=2071-01-01 01:01:11)>

I will investigate further.

gordthompson on Oct 26, 2020