使用postgres在zend框架中按日期分组

Question

So I have the following query:

public function findByDateCreated($date){
        $select = $this->select();
        $select->where('dtcreated = ?', $date);
        $select->group('date');
        $rows = $this->fetchAll($select);
        if(count($rows) > 0){
            return $rows[0];
        }else{
            return false;
        }
    }

Which throws the error:

500: SQLSTATE[42703]: Undefined column: 7 ERROR: column "date" does not exist LINE 1: ...E (dtcreated = '2012-10-17 14:26:41.575479') GROUP BY "date" ^

My question is:

How do I group by date such that I get:

17-10-2012
One item
two item
three item

16-10-2012
oneitem
twoitem
threeitem

and so on?

I don't see how this is of any help but:

create table users_notifications(
    id  int primary key default nextval('users_notifications_id_seq') not null,
    userid int not null references users(id),
    itemid int not null,
    itemtype varchar(75),
    dtcreated timestamp not null default now(),
    dtupdated timestamp default now(),
    message text,
    flag boolean,
    flagnew boolean
);

that's how the table looks.

Answer 1

The error is because you don't have a column called date so you can't group by that.

Try this:

$select->group(new Zend_Db_Expr("date_trunc('hour', dtcreated)"));

This will truncate all datetime's to just the year, month, day component, and then group by those resulting values.