List:General Discussion« Previous MessageNext Message »
From:Eric Anderson Date:January 10 2004 7:46pm
Subject:Date query question
View as plain text  
I don't have much experience working with dates in Mysql; I've always
prefered to manage dates in code, so bear with me.

I've got the following definition (yes, no indexes right now):

CREATE TABLE se_t (
  datestamp timestamp(14) NOT NULL,
  remote_addr char(16) NOT NULL default '',
  request_uri char(128) NOT NULL default '',
  remote_agent char(32) NOT NULL default '',
  domain char(32) NOT NULL default '',
  http_referer char(128) NOT NULL default '',
  query char(64) NOT NULL default '',
  status int(11) NOT NULL default '0',
  spider_type int(11) NOT NULL default '0',
) TYPE=MyISAM;

And what I'm looking to be able to do is to be able to sum up totals
based on date, whether it be today, yesterday, last week, last month,
etc.

I'm currently using 4.0.14, so I don't have access to certain functions
(like date()), otherwise I'd be able to do something like this:

SELECT
  count(*),
  sum(spider_type=0),
  sum(spider_type=1),
  sum(spider_type=2),
  sum(spider_type=3),
  sum(spider_type=4),
  sum(spider_type=5),
  sum(spider_type=6),
  sum(spider_type=7)
FROM se_t WHERE date(datestamp)=date_sub(curdate(),interval 7 day)

Is there an easier/cleaner/better way of doing this?  Or should I just
upgrade Mysql?  (how stable is 4.1.x?)


Thread
Date query questionEric Anderson10 Jan