On Dec 12, 2:53 pm, John Machin <[EMAIL PROTECTED]> wrote: > On Dec 13, 7:22 am, [EMAIL PROTECTED] 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 -- http://mail.python.org/mailman/listinfo/python-list
