Pfthb

I have this query that is trying to find a record given the same day and a status:

ld=LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp == datetime.date.today()), LunchDay.status==1))

The model:

class LunchDay(db.Model):
 __tablename__ = 'lunch_day'

 id = db.Column(db.Integer, primary_key=True)
 user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
 user = relationship("User", foreign_keys=[user_id])
 timestamp = db.Column(db.DateTime, index=True, default=datetime.today())
 status = db.Column(db.Integer) 

It runs and doesn't throw an error, yet it seems to not regard date. It will find entries with dates like yesterdays in the DateTimeField of the database:

2018-11-13 00:00:00.000000

Which is yesterdays date, but it is picking it up based just on the status almost just like it is looking at it as an OR. The imports I use are:

from sqlalchemy import func, and_

Print out the actual sql that is being generated by your query to see what is happening. E.g.:

ld=LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp == datetime.date.today()), LunchDay.status==1))
print(ld)

Prints:

SELECT lunch_day.id AS lunch_day_id, lunch_day.timestamp AS lunch_day_timestamp, lunch_day.status AS lunch_day_status
FROM lunch_day
WHERE DATE(lunch_day.timestamp = %(timestamp_1)s) AND lunch_day.status = %(status_1)s

There you can see that the equality of lunch_day.timestamp and the param timestamp_1 is being passed to the DATE function.

Which is actually pretty easy to see in your sqlalchemy query: func.DATE(LunchDay.timestamp == datetime.date.today()). I assume you want to convert LunchDay.timestamp to a date and then compare it to datetime.date.today() which should be db.func.DATE(LunchDay.timestamp) == date.today().

print(LunchDay.query.filter(and_(func.DATE(LunchDay.timestamp) == datetime.date.today(), LunchDay.status == 1)))

prints:

SELECT lunch_day.id AS lunch_day_id, lunch_day.timestamp AS lunch_day_timestamp, lunch_day.status AS lunch_day_status
FROM lunch_day
WHERE DATE(lunch_day.timestamp) = %(DATE_1)s AND lunch_day.status = %(status_1)s

One other thing to note is that multiple statements passed to filter() are automatically treated as an and_(), so you can simplify your query a little by removing that:

LunchDay.query.filter(func.DATE(LunchDay.timestamp) == datetime.date.today(), LunchDay.status == 1)