CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Dave
Dave

Reputation: 2073

Group by hour in SQLAlchemy?

How do I group query results by the hour part of a datetime column in SQLAlchemy?

Upvotes: 14

Views: 12870

Answers (5)

Matthew Moisen
Matthew Moisen

Reputation: 18289

In Oracle, use func.trunc(MyTable.dt, 'HH')

It is a bit finicky, however. This fails:

q = session.query(func.trunc(MyTable.dt, 'HH'), func.sum(MyTable.qty) \
           .group_by(func.trunc(MyTable.dt, 'HH'))

But this succeeds:

trunc_date = func.trunc(MyTable.dt, 'HH')
q = session.query(trunc_date, func.sum(MyTable.qty) \
           .group_by(trunc_date)

Thanks to this thread for the tip.

Upvotes: 0

svavarm
svavarm

Reputation: 81

Recently I had to do a similar thing using SqlAlchemy and MySQL and ended up using the DATE_FORMAT (http://www.w3schools.com/sql/func_date_format.asp) to group by hour, minute, second 

.group_by(func.date_format(date_col, '%H:%i:%s'))

To only group by hour it would be '%H' instead of '%H:%I:%s'

Upvotes: 7

jspcal
jspcal

Reputation: 51914

This works for PostgreSQL:

.group_by(func.date_trunc('hour', date_col))

Upvotes: 21

Scott
Scott

Reputation: 11206

If I remember correctly you must first extract the hour from your timestamp and then you can group by that.

query(extract('hour', timeStamp).label('h')).group_by('h')

Upvotes: 19

raben
raben

Reputation: 3110

You can also do it in Python. Assuming you have an ordered query_result :

from itertools import groupby

def grouper( item ): 
    return item.created.hour
for ( hour, items ) in groupby( query_result, grouper ):
    for item in items:
        # do stuff

This answer is adapted from an answer to a similar question here

Upvotes: 0

Related Questions