I have an sqlalchemy ORM for a Postgres table:

class Item(Base, BoilerPlateMixinBase):
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    value = Column(Float)

My project performs several SELECT queries on it.
What's the quickest way to experiment having all the dates offset by 1 year?

I could do that by modifying either:

• the records in the table
• the lines of code that read the records
• the ORM date column

The latter is preferable as it'd require changing just one place in the code.

But how can this be achieved?

(tried DATEADD as suggested here, but didn't work for me. sqlalchemy 2.0)

I have an sqlalchemy ORM for a Postgres table:

class Item(Base, BoilerPlateMixinBase):
    id = Column(Integer, primary_key=True)
    date = Column(Date)
    value = Column(Float)

My project performs several SELECT queries on it.
What's the quickest way to experiment having all the dates offset by 1 year?

I could do that by modifying either:

• the records in the table
• the lines of code that read the records
• the ORM date column

The latter is preferable as it'd require changing just one place in the code.

But how can this be achieved?

(tried DATEADD as suggested here, but didn't work for me. sqlalchemy 2.0)

• python
• postgresql
• sqlalchemy
• dateadd

Share Improve this question Follow edited Mar 13 at 21:26 EliadL asked Mar 13 at 12:48 EliadLEliadL 7,14833 gold badges2929 silver badges4444 bronze badges 2

• 1) DATEADD is an SQL Server function not a Postgres one. 2) What date are you trying to modify and when do you want it modified, on INSERT or UPDATE or both? – Adrian Klaver Commented Mar 13 at 15:43
• @AdrianKlaver 1) I meant I've tried the sqlalchemy equivalent of DATEADD, which doesn't seem to exist in the docs anymore. 2) The dates I'm trying to modify are all the Items.date values, on SELECT. I'll reflect that in the question so it's clearer, thanks. – EliadL Commented Mar 13 at 21:22

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You can also use hybrid_property (docs):

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import Column, Integer, Date, Float, func

class Item(Base, BoilerPlateMixinBase):
    id = Column(Integer, primary_key=True)
    _date = Column("date", Date)
    value = Column(Float)

    @hybrid_property
    def date(self):
        return self._date.replace(year=self._date.year + 1) if self._date else None

    @date.expression
    def date(cls):
        # in case of mysql
        return func.date_add(cls._date, func.interval(1, 'year'))
        # in case of postgresql
        return cls._date + func.cast('1 year', Interval)

column_property achieves this by replacing:

    date = Column(Date)

with:

    _date = Column('date', Date)

    date = column_property(
        cast(
            _date + text("INTERVAL '1 YEAR'"),
            Date,
        ).label(_date.name)
    )

and it works with leap years as well.