Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLAlchemy: filter by date for an datetime field(does not work with SQLite, with PostgreSQL it works fine)
from datetime import date
from sqlalchemy import cast, DATE
Match.query.filter(cast(Match.date_time_field, DATE)==date.today()).all()
@Fatman13
Copy link

Fatman13 commented Mar 24, 2017

Same problem here. Might have to do with Sqlite doesn't have a DATE type for its column type.

@jonathanmach
Copy link

jonathanmach commented Mar 12, 2018

Hi guys,
For SQLite, I'm using the func.DATE() function:

from sqlalchemy import func

session.query(db.Transaction).filter(func.DATE(db.Transaction.datetime) == date.today())

@stuartmaxwell
Copy link

stuartmaxwell commented Aug 6, 2019

Hi guys,
For SQLite, I'm using the func.DATE() function:

from sqlalchemy import func

session.query(db.Transaction).filter(func.DATE(db.Transaction.datetime) == date.today())

I've been pulling my hair out trying to solve this, but your suggestion worked perfectly. Thanks!

@haanzee
Copy link

haanzee commented Nov 13, 2019

@bhandler.route('/sbooking', methods=['GET', 'POST'])
def sbooking():
form = SBookingForm()
if form.validate_on_submit():
s_date = form.bdate.data
sr_date = s_date.strftime("%Y-%m-%d %H:%M:%S")

	if form.bresort.data=='Sona Grand':
		rec = db.session.query(Booking).filter(Booking.bresort == 'Sona Grand'
			).filter(Booking.bauth == 'Yes'
			).filter(Booking.bdate == sr_date)
		return render_template('sbookingrec.html', rec=rec, sr_date=sr_date)
return render_template('sbooking.html', title='Date Status',
	form=form, legend='Date Status')

@haanzee
Copy link

haanzee commented Nov 13, 2019

sir when comparison with date return nothing but use less then and greater then code is working
problem with date comparison is not working

@arqeco
Copy link

arqeco commented Jan 18, 2020

Thank you Jonatan. func.DATE() works.
I've opened an issue sqlalchemy/sqlalchemy#5104

@AlejandroRodriguezP
Copy link

AlejandroRodriguezP commented Sep 3, 2020

Hi guys,
For SQLite, I'm using the func.DATE() function:

from sqlalchemy import func

session.query(db.Transaction).filter(func.DATE(db.Transaction.datetime) == date.today())

It worked! Thank you sooo much!

@3yggy
Copy link

3yggy commented Sep 9, 2020

👺it doesn't work with even postgress for me

@FabricioPatrocinio
Copy link

FabricioPatrocinio commented Feb 12, 2021

This worked for me:

from sqlalchemy import func
table_db = Youtable.query.filter(func.DATE(Youtable.date_bd) == your_date)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment