group_by syntax - Printable Version

group_by syntax - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: group_by syntax (/showthread.php?tid=7163)

group_by syntax - El Forum - 03-27-2008

[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!

group_by syntax - El Forum - 03-27-2008

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

group_by syntax - El Forum - 03-27-2008

[eluser]liamstask[/eluser]
Crap - meant to ask about group_by Smile

Have now edited the original post to reflect that. Any thoughts on that one?

group_by syntax - El Forum - 03-28-2008

[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!

group_by syntax - El Forum - 03-28-2008

[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-guide/database/queries.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.