python - Get last record of a day using joins in Flask-SQLAlchemy -

I have a timestamp and a table of data for some values ​​where I want to get the last value of each day. / P>

In the raw SQL I will do this:

  select strftime ('% y-% m-% d', a1.created_at) created_at, a1.my_value My_table a1 zone (as SELECT max (id) from mytable group maxtime as strftime ('% y-% m-% d', created_at) a1.id = a2.MAX on a1;  

I am working on a flask application where I want to use the flask-SQLL extension and sqlalchemy.orm.session.Session or not raw SQL . The defined model looks like this:

  class MyModel (surrogate PK, model): __tablename__ = 'my_table' id = column (db.Integer (), nullable = false, primary_key = true) created_at = Columns (db.DateTime (), nullable = False, default = dt.datetime.utcnow) my_value = Columns (db.Integer (), nullable = true)  

I have so far What is:

External query:

  MyModel.query.with_entities (MyModel.created_at, MyModel.my_value) ...  

< P> Inner query:

  MyModel.query.with_entities (func.max (MyModel.id) .label ('max')) by group_ (func.strftime ('% y-% m -% d ', MyModel.created_at))  

But to get the desired result together There can not be a way to get involved in both.

In this way I will do it in Esquellaki,

  by sqlalchemy import Literal_column a2 = session.query (func.max (mytable id) .Label ("MAX")) .group_by (func.convert (literal_column ('DATE'), mytable.created_at)). Subquery () session.query (func.convert (literal_column ('DATE'), mytable.created_at) .label ("created_at"), my_table.my_value) .join (a2, my_table.id == a2.c.id)