Convert postgersql query to sqlalchemy

Question

I try to convert this sql query to sqlalchemy or but cant.

    SELECT AVG(value), 
           date_trunc('day', datetime) + (((date_part('hour', datetime)::integer / 5::integer) * 5::integer + 5::integer) || 'hours')::interval AS some_timestamp 
    FROM tablename 
    GROUP BY some_timestamp 
    ORDER BY some_timestamp;

I dont understand hot to convert postgresql :: to sqlalchemy and + and ||

Answer 1

My solution:

q = db.query(func.avg(Temperature.value).label('avg_value'),
                 (func.date_trunc('day', Temperature.date) +
                  cast(func.concat(
                      ((cast(func.date_part('hour', Temperature.date), Integer) / cast(5, Integer))
                       * cast(5, Integer) + cast(5, Integer)), 'hours'), Interval))
                 .label("datetime")
                 ).filter(Temperature.sensor_id == sensor_id,
                          Temperature.date >= since,
                          Temperature.date <= to).group_by('datetime').order_by('datetime')