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

You forgot to provide your table definition and your postgres version number. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 7, 2013 at 18:49
@ErwinBrandstetter ohpaulsen told me that they're working together, but he doesn't have the comment privileges. — timss
– timss, Commented Apr 7, 2013 at 22:48
@TimSæterøy: I see, thanks. FYI: Please note that you can always comment on your own posts, and any part of your questions. However, commenting on other people's posts is a privilege. More info here. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 8, 2013 at 15:23

Community · Accepted Answer · 2017-05-23 12:29:17Z

3

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.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Apr 7, 2013 at 19:02

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Audrius Kažukauskas Over a year ago

'%'+status+'%' is fine, SQLAlchemy will do the right thing when emitting actual SQL, although in this case .contains(status) would look cleaner. Also in .group_by() and .order_by() values from .label() can be used, e.g. .group_by('date_minute', http.statusline).

Collectives™ on Stack Overflow

Problems with group_by statement, sqlalchemy and postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related