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 -- http://mail.python.org/mailman/listinfo/python-list