Problems with group_by statement, sqlalchemy and postgresql

Question

I have a problem with the following query; This, itself, works fine, but it groups by second, I want to truncate seconds and group by minute. I have experimented with date_trunc, extract and so on, but I haven't had any luck. When the engine was sqlite, extract('minute') worked fine, but not with postgresql.

Anyone that can point me in the right direction?

PostgreSQL version: PostgreSQL 8.1.23 on x86_64-redhat-linux-gnu

Column('id', Integer, primary_key=True),
        Column('date', TIMESTAMP),
        Column('src', String),
        Column('dst', String),
        Column('len', String),
        Column('sport', String),
        Column('dport', String),
        Column('method', String),
        Column('host', String),
        Column('useragent', String),
        Column('statusline', String),
        Column('location', String),
        Column('server', String),
        Column('load', String),

now = datetime.datetime.now()                
DD = now - datetime.timedelta(minutes=60)    
DD = DD.strftime('%Y-%m-%d %H:%M:%S')        
query = session.query(HTTP.date,HTTP.statusline, func.count(HTTP.statusline).                                                                           
                label('count')).filter(HTTP.statusline.like('%'+status+'%'), HTTP.date>=(DD)).group_by(HTTP.date, HTTP.statusline).order_by(asc(HTTP.date)).all()

Answer 1

Before you do anything else, consider upgrading to a current version of PostgreSQL 8.1 is long dead and forgotten.

Not entirely sure about the notation, but with updates from @Audrius in the comments it should work like this:

query = session.query(
       date_trunc('min', http.date).label('date_minute')
      ,http.statusline
      ,func.count(http.statusline).label('count')
   ).filter(http.statusline.contains(status)
           ,http.date>=(DD)
   ).group_by('date_minute'
             ,http.statusline
   ).order_by(asc('date_minute')).all()

Basically, use date_trunc('min', http.date) instead of http.date in SELECT and the alias in GROUP BY and ORDER BY.

BTW: I find it very misleading to use date as name for a timestamp. Aside from that, my advice is never to use any of the base type names as identifiers. Leads to very confusing error messages and other errors hard to debug.