Perhaps it has something to do with the format of my date strings in the date column?
This is how it's formatted: 2010-11-01 05:36:06 On Mon, Nov 1, 2010 at 2:30 PM, J. Bobby Lopez <j...@jbldata.com> wrote: > 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
