Filter/get items by date

Question

Database model:

class Expense(Base):
    __tablename__ = "expenses"

    id = Column(Integer, primary_key=True, unique = True, index=True)
    item = Column(String, index=True)
    price = Column(String, index=True)
    currency = Column(String, index=True)
    created_at = Column(DateTime, default=date.today())

Using SQLAlchemy I want to get all items that had been added today.

I tried :

today = date.today()
res = db.query(Expense).filter(Expense.created_at == today).all()

But date.today() returns 2022-09-16 and Expense.created_at is 2022-09-16 00:00:00. Also I tried to store created_at = Column(DateTime, default=datetime.now()) and query like:

today = datetime.now().day
res = db.query(Expense).filter(Expense.created_at.day == today).all()

But it returns an error.

Aleksandar Varicak · Answer

There are a few ways to solve this problem: The first one is to use between in order to filter for some date, as your field is DateTime, so it will compare the exact date and time. So, here is an example:

today = datetime.now()
start = today.replace(hour=0, minute=0, second=0)
end = today.replace(hour=23, minute=59, second=59)
res = db.Qeury(Expense).filter(Expense.created_at.between(start, end)).all()

The second way is to use SQLAlchemy layer over DB built-in functions. There is a built-in function date that gets the date of DateTime field. Here is an example:

from sqlalchemy import func
today = datetime.now().date()
res = db.Query(Expense).filter(func.date(Expense.created_at) == today).all()

Also, I see that you've made a small mistake in setting up one column:

created_at = Column(DateTime, default=date.today())

This means that every time you don't give the created_at column to the model, created_at will be the date of when the process started, as you've called function today().

Default expects to receive callable, not function, so it should look like this:

created_at = Column(DateTime, default=date.today)

This way, when the default is needed, it will call function date.today every time.

Filter/get items by date

1 Answers