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 ||

The `::` is [casting](https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS). Here is an answer explaining how to cast to interval: [using-postgressql-interval-in-sqlalchemy-where-the-duration-is-dynamically-store](https://stackoverflow.com/questions/57267267/using-postgressql-interval-in-sqlalchemy-where-the-duration-is-dynamically-store) What did you try so far? — Ian Wilson, Mar 31 '22 at 20:39

score 0 · Accepted Answer · answered Apr 05 '22 at 17:21

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')

Convert postgersql query to sqlalchemy

1 Answers1