sqlalchemy filter by comparing datetime.now() and column default date

Question

in my table definition, i have a column defined like this:

created_date = Column(DateTime, nullable=False, default=datetime.now)

i want to query a instance when its created date is equal to current date(eg, if it is created today).

so i tried something like this:

res = session.query(Object).filter(datetime.now() == Object.created_date)

it never works because two dates are compared in seconds i guess, so they will never be equal to each other. then i tried this:

res = session.query(Object).filter((datetime.now() - Object.created_date).days < 1)

while (datetime.now() - datetime.now()).days works in python, it doesnt work in my situation here. i got error says: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'days'.

So how should i do a query that filters instances created on a current day? thanks!

well, it doesnt work practically. by saying it works i mean it will return a value which is -1. — shangsunset, Mar 04 '15 at 16:19
which datetime library and which version of python are you using? I just don't think there is a `days` attribute for `datetime` instance — Paul Lo, Mar 04 '15 at 16:22

score 4 · Accepted Answer · answered Mar 04 '15 at 16:15

4

I doubt that (datetime.now() - datetime.now()).days works since datetime.datetime instance only have attribute named day rather than days. Using datetime.now().days would result in AttributeError: 'datetime.datetime' object has no attribute 'days'

You might try this:

from datetime import timedelta

res = session.query(Object).filter(
    (Object.created_date+timedelta(days=1))>datetime.now())

answered Mar 04 '15 at 16:15

Paul Lo

6,032
6
31
36

yes! this works perfectly!. i do `from datetime import datetime` and use python 2.7. `datetime.now() - datetime.now()` will become a timedelta object, which has `days` attribute. thanks for your help! – shangsunset Mar 04 '15 at 16:32
@shangyeshen Oops, looks like `(datetime.now()-datetime.now()).days` do work. So your origin code yields errors might because `datetime.now()` isn't really compatible with `Object.created_date` with SQLAlcehmy's `DateTime` type? Need to be investigated... – Paul Lo Mar 04 '15 at 16:37

score 0 · Answer 2 · answered Feb 14 '19 at 08:26

You want to fetch row having created date == current date For example, your table column

| Id | created |

| 1 | 2019-02-13 12:48:49 |

| 2 | 2019-02-14 12:48:49 |

| 3 | 2019-02-14 12:48:49 |

Today date is: 2019-02-14

You can try this

from sqlalchemy import exc, extract
from datetime import datetime, date, timedelta    

res = session.query(Object).filter(
      extract('month', Object.created) >= datetime.today().month,
      extract('year', Object.created) >= datetime.today().year,
      extract('day', Object.created) >= datetime.today().day)
     ).all()

You will get output:

| Id | created |

| 2 | 2019-02-14 12:48:49 |

| 3 | 2019-02-14 12:48:49 |

MORE YOU CAN FIND HERE

but if the table contain 2030-01-10 , it will not be in the results because the day and month is less than today's day and month although year is greater — Flowra, Nov 13 '20 at 04:26

score 0 · Answer 3 · answered Aug 17 '20 at 12:24

This is how I got all entries related to One date or from a particular date.

from datetime import date

start = date(year=2019, month=12, day=30)
today_date = date.today()

with DBConx(TEST_METRICS) as db_conx:
    answer =  db_conx.the_session.query(TestRun).filter(
        (TestRun.datetime) >= start)

    # This will print all entries from the start date.
    print (len(answer.all()))

    answer =  db_conx.the_session.query(TestRun).filter(
        (TestRun.datetime) == the_date)

    # This will print all entries of today.
    print (answer.all())

sqlalchemy filter by comparing datetime.now() and column default date

3 Answers3