Because of the way my 'date' field is formatted, I've stripped the time
element from it, and it seems to work.. here's the SQL:
SELECT cnt, day from (
select count(*) as cnt,
julianday( strftime('%Y-%m-%d', 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
)
;
On Mon, Nov 1, 2010 at 3:12 PM, J. Bobby Lopez <[email protected]> wrote:
> 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 <[email protected]> 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 <
>> [email protected]> wrote:
>>
>>> 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
>>>
>>
>>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
