sqlite weirdness

[John Machin]

On Dec 13, 7:22 am, kyoso... at gmail.com wrote:
> Hi,
>
> I am trying to use sqlite to create a local database for an
> application I am writing and I am getting some screwy results from it.
> Basically, I have a set of values in the database and I am trying to
> select a date range and sum those values.
>
> My problem is that it only sums up every other date range and ignores
> the in between ranges.
>
> Here's the table structure:
>
> sql = '''CREATE TABLE tbl_TimeEntries (dateworked DATE,
>                                   empid INTEGER,
>                                   reg REAL,
>                                   ot REAL,
>                                   ce REAL,
>                                   hol REAL,
>                                   sklv REAL,
>                                   vac REAL,
>                                   ct REAL,
>                                   conv REAL,
>                                   misc REAL,
>                                   comments TEXT,
>                                   PRIMARY KEY (dateworked, empid))
>
>     '''
>
> I have data in it from 12/18/2006 - 01/26/2007.
>
> I use the following bits of sql:
>
> "SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
> SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
> '12/17/2006' AND dateworked <= '12/30/2006' AND empid = 281"
>
> "SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
> SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
> '12/31/2006' AND dateworked <= '01/13/2007' AND empid = 281"
>
> "SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
> SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
> '01/14/2007' AND dateworked <= '01/27/2007' AND empid = 281"
>
> The first and third return the correct sums. The middle one returns a
> list of null values.
>
> If I select everything in the database for that user, I get this:
>
> [(u'12/18/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
> u''), (u'12/19/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
> u'', u''), (u'12/20/2006', 281, 8.0, u'', u'', u'', u'', u'', u'',
> u'', u'', u''), (u'12/21/2006', 281, 8.0, u'', u'', u'', u'', u'',
> u'', u'', u'', u''), (u'12/22/2006', 281, 8.0, u'', u'', u'', u'',
> u'', u'', u'', u'', u''), (u'12/25/2006', 281, 8.0, u'', u'', u'',
> u'', u'', u'', u'', u'', u''), (u'12/26/2006', 281, 8.0, u'', u'',
> u'', u'', u'', u'', u'', u'', u''), (u'12/27/2006', 281, 8.0, u'',
> u'', u'', u'', u'', u'', u'', u'', u''), (u'12/28/2006', 281, 8.0,
> u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'12/29/2006', 281,
> 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/01/2007',
> 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
> (u'01/02/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
> u''), (u'01/03/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
> u'', u''), (u'01/04/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
> u'', u'', u''), (u'01/05/2007', 281, 8.0, u'', u'', u'', u'', u'',
> u'', u'', u'', u''), (u'01/08/2007', 281, 8.0, u'', u'', u'', u'',
> u'', u'', u'', u'', u''), (u'01/09/2007', 281, 8.0, u'', u'', u'',
> u'', u'', u'', u'', u'', u''), (u'01/10/2007', 281, 8.0, u'', u'',
> u'', u'', u'', u'', u'', u'', u''), (u'01/11/2007', 281, 8.0, u'',
> u'', u'', u'', u'', u'', u'', u'', u''), (u'01/12/2007', 281, 8.0,
> u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/15/2007', 281,
> 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/16/2007',
> 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
> (u'01/17/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
> u''), (u'01/18/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
> u'', u''), (u'01/19/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
> u'', u'', u''), (u'01/22/2007', 281, 8.0, u'', u'', u'', u'', u'',
> u'', u'', u'', u''), (u'01/23/2007', 281, 8.0, u'', u'', u'', u'',
> u'', u'', u'', u'', u''), (u'01/24/2007', 281, 8.0, u'', u'', u'',
> u'', u'', u'', u'', u'', u''), (u'01/25/2007', 281, 8.0, u'', u'',
> u'', u'', u'', u'', u'', u'', u''), (u'01/26/2007', 281, 8.0, u'',
> u'', u'', u'', u'', u'', u'', u'', u'')]
> [(80.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]
>
> So the data us there, but the sql only works part of the time. My SQL
> works if my database is in SQL Server, but not sqlite. Is my SQL
> malformed? Is it something about dates in sqlite? Or is it something
> else?
>
> Thanks a lot. I apologize in advance for the long post.
>

Try this:
    select * from tbl_TimeEntries where empid = 281 order by
dateworked
and see what you get.

Very short story: sqlite doesn't have a native date type (look at the
results it's returning (e.g. u'12/18/2006')), and is doing a *string*
comparison. SQL Server has a native date type and knows your locale.
If you always work with dates in 'YYYY-MM-DD' format, you should have
less bother across locales and across databases.

More detail on the sqlite website.

HTH,
John