CODEWITHSUNDEEP
pythonsqlsqlalchemy
Jesper
Jesper

Reputation: 1086

Grouping by hour in SQLAlchemy

I'm trying to get the average value of every hour from a database. The data looks like this:

Some data

And my query looks like this:

resultset = DBSession.query(dlog.time, 
    func.avg(dlog.value).label("value"),
    func.avg(dlog.minimum).label("minimum"),
    func.avg(dlog.maximum).label("maximum"))\
    .filter(dlog.time >= startObject, dlog.time <= endObject, dpoint.name == n)\
    .group_by(func.date_format(dlog.time, '%H'))\
    .join(dpoint, dpoint.point_id==dlog.point_id)\
    .all()

The problem with this is that while it gets the average of every hour, it also groups the same hour from the next day. So if i want two days of data with every hour grouped (48 items for 48 hours), i get 1 day of data that has 2 days of data grouped together.

Upvotes: 0

Views: 347

Answers (1)

sam
sam

Reputation: 2311

Can you try group by day-hour as your group by key.. so it will group all minutes of that hour

resultset = DBSession.query(dlog.time, 
func.avg(dlog.value).label("value"),
func.avg(dlog.minimum).label("minimum"),
func.avg(dlog.maximum).label("maximum"))\
.filter(dlog.time >= startObject, dlog.time <= endObject, dpoint.name == n)\
.group_by(func.date_format(dlog.time, '%Y-%M-%D %H'))\
.join(dpoint, dpoint.point_id==dlog.point_id)\
.all()

Upvotes: 1

Related Questions