group_by syntax

[eluser]liamstask[/eluser]
Hiya - I'm trying to generate the appropriate group_by clause to create summaries of data in 30 minute increments from my db. My query looks like

Code:

$this->db->select_avg('value');
$this->db->select('timestamp');
$this->db->where('timestamp >', $starttime);
$this->db->where('timestamp <', $endtime);
$this->db->where('device_id', $device_id);
$this->db->order_by('timestamp');
// get half-hour bins
$this->db->group_by("2 * HOUR(timestamp) + FLOOR(MINUTE(timestamp) / 30)");
$query = $this->db->get('data');

With the resulting query looking like

Code:

SELECT AVG(`value`) AS `value`, `timestamp` FROM (`data`) WHERE timestamp > '2008-03-26 09:51:35' AND timestamp < '2008-03-27 09:51:35' AND `device_id` = '1' GROUP BY `2` * HOUR(`timestamp`) + FLOOR(MINUTE(`timestamp`) / `30`) ORDER BY `timestamp`

I'm pretty sure the numbers in the group_by clause shouldn't be quoted, but I'm pretty new to SQL...wondering if anybody can nudge me along with the appropriate way to specify this? Thanks!

[edit] was actually asking about the group_by clause, not order_by - sorry bout that!

[eluser]GSV Sleeper Service[/eluser]
nothing wrong with the order by, but that group by is going to cause problems.

[eluser]liamstask[/eluser]
Crap - meant to ask about group_by Have now edited the original post to reflect that. Any thoughts on that one?

[eluser]liamstask[/eluser]
I've been told now by a couple non-CI SQL people that

Code:

GROUP BY (2 * HOUR(timestamp) + FLOOR( MINUTE(timestamp) / 30 ))

is the appropriate syntax to specify half-hour bins, so I'm feeling reasonably confident that part's ok. Still having trouble getting this to work with the CI active record group_by( ) method, though, which is rendering it as

Code:

GROUP BY (`2` * HOUR(`timestamp`) + FLOOR( MINUTE(`timestamp`) / `30` ))

My working, uninformed theory is that the single quotes should be removed. Any other thoughts, or ideas on how to remove them?

Thanks!

[eluser]sophistry[/eluser]
you don't have to use all the AR stuff... just send your properly constructed query in raw to $this->db->query() method.

http://ellislab.com/codeigniter/user-gui...eries.html

i think that's what people mean when they say they like CI because "it just gets out of the way when you need it to."

luck to you.