«

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.

Mike

wrote:

> 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?

Your dateworked field seems to have strings rather than dates so the
expression "dateworked >= '12/31/2006' AND dateworked <= '01/13/2007'"
as a string comparison isn't going to match any records. Sqllite handles
data types differently than other SQL databases: read the documentation to
find out how to store dates, or else store the dates in ISO format
(YYYY/MM/DD) where string comparisons will give the correct results.

On Dec 13, 7:22 am, kyoso...@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

On Dec 12, 2:53 pm, John Machin <sjmac...@lexicon.net> wrote:
> On Dec 13, 7:22 am, kyoso...@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

On Wed, 12 Dec 2007 16:02:35 -0800 (PST), declaimed
the following in comp.lang.python:


>
> 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
>

You missed two items... The open specified options to use either the
type /name/ declared in the CREATE TABLE or a type name attached to the
field names in a select -- and those are used to /call/ a data converter
function that has to be registered. Key phrases:

"""
There are default adapters for the date and datetime types in the
datetime module. They will be sent as ISO dates/ISO timestamps to
SQLite.

The default converters are registered under the name "date" for
datetime.date and under the name "timestamp" for datetime.datetime.
"""

"""
con = sqlite3.connect(":memory:",
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE _COLNAMES)
"""
Note the "detect_types" specification. Also take care, if your
database was populated by some other application that used that
mm/dd/yyyy format, you may have problems as the converters above specity
/sending/ ISO format to the database from Python datetime objects, and
probably expecting to convert them back on input.
--
Wulfraed Dennis Lee Bieber KD6MOG

HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-)
HTTP://www.bestiaria.com/

wrote:
>
>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:

You can certainly create fields of type "date" in sqlite, but sqlite
doesn't understand any types. **ALL** types in sqlite are stored and
compared as strings. You could declare your "date" field as type "frog"
and it would work exactly the same.

C:\tmp>sqlite x.db
SQLite version 2.8.6
Enter ".help" for instructions
sqlite> create table xyz (
...> xxx frog
...> );
sqlite> insert into xyz values (123);
sqlite> select * from xyz;
123
sqlite>

Note that the example in the web page you quoted takes a Python variable of
type datetime.date and converts it to a string, which produces
"2007-12-12". Also note that the conversion on the OTHER end has to use
the special sqlite adapter syntax ('select current_date as "d [date]"...').

In my opinion, it can be argued that the inclusion of sqlite in the Python
standard library was a mistake. It is a great product, and I've used it
many times in my own Python apps, but it has a number of unexpected
idiosyncracies. When you download and install it yourself, you can
evaluate the idiosyncracies and decide whether they are acceptable, but
when its in the standard library, you don't expect to go through that.

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

Another possible solution is to use a real database.
--
Tim Roberts,
Providenza & Boekelheide, Inc.

On Dec 13, 1:54 am, Tim Roberts <t...@probo.com> wrote:
> kyoso...@gmail.com wrote:
>

<snipped>

> >I'll have to refactor my code somewhat to force it to use the 'YYYY-MM-
> >DD' format.
>
> Another possible solution is to use a real database.

I am using a "real" database: MS SQL Server 2000. Unfortunately, my
program will also need to run in an offsite location that cannot
connect to that server right now. Thus the need to use sqlite or some
flat-file format.

Thanks for the advice.

Mike


> --
> Tim Roberts, t...@probo.com
> Providenza & Boekelheide, Inc.

On Dec 13, 12:12 am, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote:
> On Wed, 12 Dec 2007 16:02:35 -0800 (PST), kyoso...@gmail.com declaimed
> the following in comp.lang.python:
>
>
>
> > 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
>
> You missed two items... The open specified options to use either the
> type /name/ declared in the CREATE TABLE or a type name attached to the
> field names in a select -- and those are used to /call/ a data converter
> function that has to be registered. Key phrases:
>
> """
> There are default adapters for the date and datetime types in the
> datetime module. They will be sent as ISO dates/ISO timestamps to
> SQLite.
>
> The default converters are registered under the name "date" for
> datetime.date and under the name "timestamp" for datetime.datetime.
> """
>
> """
> con = sqlite3.connect(":memory:",
> detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE _COLNAMES)
> """
> Note the "detect_types" specification. Also take care, if your
> database was populated by some other application that used that
> mm/dd/yyyy format, you may have problems as the converters above specity
> /sending/ ISO format to the database from Python datetime objects, and
> probably expecting to convert them back on input.
> --
> Wulfraed Dennis Lee Bieber KD6MOG
> wlfr...@ix.netcom.com wulfr...@bestiaria.com
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff: web-a...@bestiaria.com)
> HTTP://www.bestiaria.com/

Well, that makes sense. I read the parts you mentioned earlier, but I
guess I just wasn't getting my head around the concepts.

Thanks for clearing that up.

Mike