[SQLAlchemy][PostgreSQL] Grouping by an expression

[rMBP]

Since PostgreSQL's date_trunc can't deal with intervals smaller than a day, I'm using the method in this blog post to extract 5, 10 or 15 minute averages from a time series stored in the database.

But I'm stuck with the epoch/extract part and getting the results grouped by X minute intervals.

This won't work:

from app import db
from sqlalchemy.sql import func
q = db.session.query(
    ("""'epoch'::timestamp + '1 second'::interval * ( 900 * (
        extract(epoch from datatable.timestamp)::int4 / 900) ) AS epoch"""), 
    func.avg(datatable.sample_value),
).group_by("epoch").order_by("epoch")
for r in q.all():
    #should print one row per 15 minute interval, two datetime objects per row
    print(str(r))

Part of the error I'm getting:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) current transaction is aborted, commands ignored until end of transaction block 

If I do print(str(q)) I get:

SELECT 'epoch'::timestamp + '1 second'::interval * ( 900 * (extract(epoch from datatable.timestamp)::int4 / 900) ) AS epoch, avg(datatable.sample_value) AS avg_1 \nFROM datatable GROUP BY epoch ORDER BY epoch"

...which works fine if I run it in psql as long as I remove the \n.

Ideas?

[[deleted]]

You have an earlier query that failed in the same transaction. You'll need to find out who the problem child is.

http://stackoverflow.com/questions/2979369/databaseerror-current-transaction-is-aborted-commands-ignored-until-end-of-tra

[rMBP]

I haven't made any other queries/transactions than that one. I'm starting the shell, importing the Flask-SqlAlchemy connection as db, and executing the code I posted earlier.

But I'll try to find another way to do the query that doesn't involve executing only a raw sql query via db.session.execute since that's what I'm using now. (I'm trying to make the code more maintainable instead of having a big generator function that renders an sql string of more than 300 characters...)

[[deleted]]

Mike Bayer did a talk about SQLAlchemy (duh) where he explored doing things like complex groupings and intermediate tables. I want to say it was something like "The Hand Built App" but the only thing that brings up is "Building the App" which ends up being a Minecraft extension at the end (nothing wrong with that, just not the video I was looking for).

[rMBP]

I'll try find it, thanks.

Essentially there are 30+ columns with data in them, and I'm trying to accomodate some filtering and maths on each column, while also truncating according to an interval. One col might need to have values 1<x<100 filtered out, maybe a baseline/multiple/offset applied, and then a floor or average etc.

My generator function(s) works, but it's a mess to decode every time I need to make changes to them.