I'm getting some odd results with your second example. It looks like the 'cnt' value is not being summed together for each day, and instead I'm getting the following:
... 1|31 1|31 1|31 1|31 1|31 1|31 1|31 1|31 1|31 1|01 1|01 1|01 1|01 1|01 1|01 1|01 1|01 1|01 The sql is verbatim, so I'm not sure what's going on..? Bobby On Mon, Nov 1, 2010 at 1:27 PM, Simon Davies <simon.james.dav...@gmail.com>wrote: > On 1 November 2010 16:45, J. Bobby Lopez <j...@jbldata.com> wrote: > > Hello all, > > > > I've been reading > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions, > > but I'm having some trouble understanding how I would pull records within > a > > specific date range (last five days for example). > > > . > . > . > > CREATE TABLE data ( > > id INTEGER PRIMARY KEY, > > user_id varchar(30), > > date varchar(30), > > keywords varchar(1024), > > log_files varchar(1024), > > script varchar(30), > > tracker_ip varchar(30), > > user_ip varchar(30) > > ); > > > > > > Basically I'd like to get output similar to the following: > > > > 5|25 > > 3|26 > > 7|27 > > 2|29 > > 4|30 > > > > > > These being the number of records logged for each day in the past 7 days, > > the days being Oct 25 - Nov 1. > > > > > > Something like: > SQLite version 3.6.11 > Enter ".help" for instructions > sqlite> > sqlite> > sqlite> CREATE TABLE data ( > ...> id INTEGER PRIMARY KEY, > ...> user_id varchar(30), > ...> date varchar(30), > ...> keywords varchar(1024), > ...> log_files varchar(1024), > ...> script varchar(30), > ...> tracker_ip varchar(30), > ...> user_ip varchar(30) > ...> ); > sqlite> > sqlite> > sqlite> insert into data( date ) values( '2010-10-25' ); > sqlite> insert into data( date ) values( '2010-10-25' ); > sqlite> insert into data( date ) values( '2010-10-26' ); > sqlite> insert into data( date ) values( '2010-10-26' ); > sqlite> insert into data( date ) values( '2010-10-26' ); > sqlite> insert into data( date ) values( '2010-10-27' ); > sqlite> insert into data( date ) values( '2010-10-29' ); > sqlite> insert into data( date ) values( '2010-10-29' ); > sqlite> insert into data( date ) values( '2010-10-30' ); > sqlite> insert into data( date ) values( '2010-10-30' ); > sqlite> insert into data( date ) values( '2010-10-30' ); > sqlite> insert into data( date ) values( '2010-11-01' ); > sqlite> insert into data( date ) values( '2010-11-01' ); > sqlite> insert into data( date ) values( '2010-11-01' ); > sqlite> insert into data( date ) values( '2010-11-02' ); > sqlite> insert into data( date ) values( '2010-11-03' ); > sqlite> insert into data( date ) values( '2010-11-03' ); > sqlite> insert into data( date ) values( '2010-11-05' ); > sqlite> > sqlite> select cnt, day from ( > ...> select count(*) as cnt, > ...> julianday( date ) as jul, > ...> strftime( '%d', date ) as day > ...> from data, (select '2010-10-30' as ref ) > ...> where jul > julianday( ref, '-7 days' ) and > ...> jul <= julianday( ref ) > ...> group by jul > ...> order by jul ); > 2|25 > 3|26 > 1|27 > 2|29 > 3|30 > sqlite> select cnt, day from ( > ...> select count(*) as cnt, > ...> julianday( date ) as jul, > ...> strftime( '%d', date ) as day > ...> from data, (select '2010-11-02' as ref ) > ...> where jul > julianday( ref, '-7 days' ) and > ...> jul <= julianday( ref ) > ...> group by jul > ...> order by jul ); > 1|27 > 2|29 > 3|30 > 3|01 > 1|02 > sqlite> select cnt, day from ( > ...> select count(*) as cnt, > ...> julianday( date ) as jul, > ...> strftime( '%d', date ) as day > ...> from data, (select 'now' as ref ) > ...> where jul > julianday( ref, '-7 days' ) and > ...> jul <= julianday( ref ) > ...> group by jul > ...> order by jul ); > 3|26 > 1|27 > 2|29 > 3|30 > 3|01 > sqlite> > > > > > > Bobby > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
