sqlite weirdness

Wed Dec 12 19:02:35 EST 2007

On Dec 12, 2:53 pm, John Machin <sjmac... at lexicon.net> wrote:
> 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

Thanks Duncan and John! That makes sense. But why does the official
Python docs show an example that seems to imply that there is a "date"
type? See link below:

http://docs.python.org/lib/node349.html

I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
DD' format.

Thanks again,

Mike