postgresql do not aggregate by my timestamp -

postgresql do not aggregate by my timestamp -

i have sql following:

with cte ( select user_id,level,plugged,plugged_ac,plugged_usb,created_at probe_batteries probe_batteries.user_id='91' , probe_batteries.created_at > timestamp '2014-06-07 17:00:00' , probe_batteries.created_at < timestamp '2014-06-07 23:00:00' ) select cte.user_id user_id, avg(cte.level), bool_or(cte.plugged), bool_or(cte.plugged_ac), bool_or(cte.plugged_usb),date_trunc('second',cte.created_at) created_at cte grouping created_at, user_id order created_at asc

but table result not aggregate according created_at (see lastly column in results) timestamp type

91;95.0000000000000000;f;f;f;"2014-06-07 20:46:06" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:11" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:12" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:12" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:12" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:13" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:13" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:13" 91;95.0000000000000000;f;f;f;"2014-06-07 21:19:14"

is there wrong sql sentence? thx

you have look alias created_at matching column name of cte named subquery.

try giving alias instead, like:

with cte ( select user_id,level,plugged,plugged_ac,plugged_usb,created_at probe_batteries user_id='91' , created_at > timestamp '2014-06-07 17:00:00' , created_at < timestamp '2014-06-07 23:00:00' ) select cte.user_id user_id, avg(cte.level), bool_or(cte.plugged), bool_or(cte.plugged_ac), bool_or(cte.plugged_usb), date_trunc('second',cte.created_at) dt_at cte grouping dt_at, user_id order dt_at asc;

postgresql aggregate-functions