how to filter a timestamp field by date ?

[Jonathan Vanasco]

I this class/table

class Posting:
id INT
timestamp_created TIMESTAMP

i'm trying to figure out how to execute this sql

SELECT * FROM posting WHERE date(timestamp_created) = date(%s) ;

i can't seem to find a "date" operator in the api docs. i swear i've
seen one before.

[David Bowser]

You're looking for func which generates SQL function expressions.
See: http://docs.sqlalchemy.org/en/latest/core/expression_api.html



For your example:

from sqlalchemy.sql.expression import func

session.query(Posting).filter(func.date(Posting.timestamp_created)==func.date(my_var)).all()

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Michael Bayer]

I'd also add that if your TIMESTAMP is indexed, you'll want to compare it using BETWEEN "date 00:00:00 " / "date 23:59:59" (pseudocode) for better efficiency.

[Jonathan Vanasco]

thanks to you both!

i managed to just now figure out how to do it with sql.expression.cast
as well :

filter( sqlalchemy.sql.expression.cast(model.core.Posting.timestamp_created,sqlalchemy.types.Date)
== self.request.app_meta.datetime.date() )

the docs on the .func were confusing at first. now i understand how
it does that.