List:General Discussion« Previous MessageNext Message »
From:Jason Dixon Date:March 30 2005 3:38am
Subject:Group by datetime
View as plain text  
Hi folks-

This is my first post to any MySQL lists, so be gentle.  If I'm posting 
SQL queries to the wrong forum, please direct me to the proper 
resources.

Anyways, I have a simple schema that stores some IP accounting data.  
I'm attempting to extract the data, grouping by the service type 
("label"), and also grouping by each 24-hour window/day from the 
datetime column ("timestamp").  I'm not sure how to accomplish this 
type of query, but I'm hoping someone here will help me do this in SQL 
and avoid having to do it in my Perl code.  Thanks in advance!


CREATE TABLE stats (
   id int(10) unsigned NOT NULL auto_increment,
   host varchar(255) NOT NULL default '',
   label varchar(255) NOT NULL default '',
   evals bigint(20) unsigned NOT NULL default '0',
   packets bigint(20) unsigned NOT NULL default '0',
   bytes bigint(20) unsigned NOT NULL default '0',
   date datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY  (id)
) TYPE=MyISAM;


mysql> select * from stats limit 2\G;
*************************** 1. row ***************************
        id: 1
      host: test2
     label: http-inbound
     evals: 149
   packets: 5748
     bytes: 583571
date: 2005-03-27 22:23:45
*************************** 2. row ***************************
        id: 2
      host: test2
     label: ssh-inbound
     evals: 149
   packets: 1104
     bytes: 630864
date: 2005-03-27 23:40:09
2 rows in set (0.00 sec)


--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net

Thread
Group by datetimeJason Dixon30 Mar
  • Re: Group by datetime [SUMMARY]Jason Dixon30 Mar