On 1 November 2010 16:45, J. Bobby Lopez <[email protected]> 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
