Using DATEADD in sqlalchemy

Question

How can I rewrite the following sql statement with sqlalchemy in python. I have been searching for 30 mins but still couldn't find any solutions.

DATEADD(NOW(), INTERVAL 1 DAY)

or

INSERT INTO dates (expire)
VALUES(DATEADD(NOW(), INTERVAL 1 DAY))

Thanks in advance

Martijn Pieters · Answer 1 · 2013-03-23T00:30:16.920

41

For completeness sake, here is how you'd generate that exact SQL with using sqlalchemy.sql.func:

from sqlalchemy.sql import func
from sqlalchemy.sql.expression import bindparam
from sqlalchemy import Interval

tomorrow = func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval()))

which results in:

>>> from sqlalchemy.sql import func
>>> func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval(native=True)))
<sqlalchemy.sql.expression.Function at 0x100f559d0; dateadd>
>>> str(func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval(native=True))))
'dateadd(now(), :tomorrow)'

Alternatively you could use a text() object to specify the interval instead:

from sqlalchemy.sql import func
from sqlalchemy.sql.expression import text

tomorrow = func.dateadd(func.now(), text('interval 1 day'))

edited Mar 23 '13 at 00:30

answered Mar 22 '13 at 15:12

Martijn Pieters

1,048,767
296
4,058
3,343

2

It's worth noting that [the docs state that `Interval`](http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Interval) only works with certain DBs & the `text` version may be required for those. – wbyoung Nov 18 '16 at 20:56
2

@wbyoung: since the OP stated the exact SQL expression they wanted to use in the question, it was safe to assume that they were using a database that supports the syntax. – Martijn Pieters Nov 18 '16 at 20:57
2

other databases support `DATEADD(NOW(), INTERVAL 1 DAY)`, i.e. MySQL, but SQLAlchemy does not because the adapter has issues with type. – wbyoung Nov 19 '16 at 00:38

score 10 · Accepted Answer · answered Mar 22 '13 at 15:02

10

SQLAlchemy dates automagically map to Python datetime objects, so you should just be able to do:

from sqlalchemy import Table, Column, MetaData, DateTime
from datetime import datetime, timedelta

metadata = MetaData()
example = Table('users', metadata,
   Column('expire', DateTime)
)

tomorrow = datetime.now() + timedelta(days=1)

ins = example.insert().values(expire=tomorrow)

answered Mar 22 '13 at 15:02

Doobeh

9,280
39
32

5

unfortunately SQLAlchemy's date system won't automatically coerce into platform specific funcitons like DATEADD() right now. Only on Postgresql with psycopg2 (and maybe mysql also) does simple date arithmetic as above translate into what the DB expects. – zzzeek Mar 23 '13 at 00:03
Technically, this is not the same, since Python's `now()` isn't the same as SQL's `now()`. It matters on which machine you take the timestamp. – vvv444 May 23 '23 at 09:19

score 3 · Answer 3 · answered Mar 22 '13 at 15:06

Doobeh beat me to it while I was typing, here's a flask-sqlalchemy example I was going to post though (to compliment the plain sqlalchemy example):

from flask.ext.sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta

db = SQLAlchemy()

class Thing(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime)

c = Thing(created = datetime.utcnow() + timedelta(days=1))
print repr(c.created)
# datetime.datetime(2013, 3, 23, 15, 5, 48, 136583)

You can pass default as a callable too:

from flask.ext.sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta

db = SQLAlchemy()

def tomorrow():
    return datetime.utcnow() + timedelta(days=1)

class Thing(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    publish_date = db.Column(db.DateTime, default=tomorrow)

score 0 · Answer 4 · answered Aug 26 '16 at 00:16

0

You may use MySQL timestampadd insted dateadd.

More detail http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampadd

answered Aug 26 '16 at 00:16

Vitold S.

402
4
13

score 0 · Answer 5 · answered Dec 29 '16 at 21:20

0

from datetime import datetime, timedelta
from dateutil import tz

new_date = datetime.now(tz=tz.tzlocal()) + timedelta(days=1)
new_item = Expire(expire=new_date)
session.save(new_item)
session.commit()

answered Dec 29 '16 at 21:20

jackotonye

3,537
23
31

score 0 · Answer 6 · answered Apr 29 '22 at 05:43

Specifying model default value like this worked for me. SQLAlchemy, however, expects you to pass a value to the attribute for the validator to be triggered.

from uuid import uuid4, UUID
from datetime import datetime, timedelta
from sqlalchemy.sql import func
from sqlalchemy.orm import (
    validates,
    declarative_base,
)
from sqlalchemy import (
    Column,
    DateTime,
)

Base = declarative_base()

class SomeModel(Base):
    __tablename__ = "some_model"

    id = Column(UUID, primary_key=True, default=lambda: uuid4().hex)
    created = Column(DateTime, server_default=func.now())
    expires = Column(DateTime)

    @validates("expires")
    def set_expiration(self, key, value):
        return datetime.utcnow() + timedelta(days=1)

SomeModel(expires=None)

Using DATEADD in sqlalchemy

6 Answers6

Linked