Querying DATE column with date/time string.

[Doug Van Horn]

Hi,

I'm running into a problem with the database library in Django running against SQLite.  I'm trying to understand why the following happens:

$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01');
sqlite> select d from foo where d between '2008-01-01' and '2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and '2008-01-31 23:59:59.999999';
sqlite> .quit

In English, why does adding the 'time' portion to the between clause not find the record?


Thanks for any help or insights...

Doug Van Horn

[Dan Kennedy-4]

On Dec 6, 2007, at 9:48 PM, Doug Van Horn wrote:

>
> Hi,
>
> I'm running into a problem with the database library in Django running
> against SQLite.  I'm trying to understand why the following happens:
>
> $ sqlite3 date_test
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> create table foo (d date null);
> sqlite> insert into foo (d) values ('2008-01-01');
> sqlite> select d from foo where d between '2008-01-01' and  
> '2008-01-31';
> 2008-01-01
> sqlite> select d from foo where d between '2008-01-01 00:00:00' and
> '2008-01-31 23:59:59.999999';
> sqlite> .quit
>
> In English, why does adding the 'time' portion to the between  
> clause not
> find the record?

Because according to the default collation sequence, strcmp(), the
string '2008-01-01 00:00:00' is larger than '2008-01-01'.

Dan.

>
>
> Thanks for any help or insights...
>
> Doug Van Horn
> --
> View this message in context: http://www.nabble.com/Querying-DATE- 
> column-with-date-time-string.-tf4956413.html#a14193493
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> ----------------------------------------------------------------------
> -------
> To unsubscribe, send email to sqlite-users-unsubscribe@...
> ----------------------------------------------------------------------
> -------
>

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------

[Igor Sereda]

My guess is that string comparison is taking place, and so "2008-01-01" is
less than "2008-01-01 00:00:00".

HTH,
Igor
 
-----Original Message-----
From: Doug Van Horn [mailto:dougvanhorn@...]
Sent: Thursday, December 06, 2007 5:48 PM
To: sqlite-users@...
Subject: [sqlite] Querying DATE column with date/time string.


Hi,

I'm running into a problem with the database library in Django running
against SQLite.  I'm trying to understand why the following happens:

$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01'); select d from foo
sqlite> where d between '2008-01-01' and '2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and
'2008-01-31 23:59:59.999999';
sqlite> .quit

In English, why does adding the 'time' portion to the between clause not
find the record?


Thanks for any help or insights...

Doug Van Horn
--
View this message in context:
http://www.nabble.com/Querying-DATE-column-with-date-time-string.-tf4956413.
html#a14193493
Sent from the SQLite mailing list archive at Nabble.com.


----------------------------------------------------------------------------
-
To unsubscribe, send email to sqlite-users-unsubscribe@...
----------------------------------------------------------------------------
-



-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------

[Griggs, Donald-2]

Hi Doug,

You're not storing the dates as anything other than plain text, so the
short value:
    '2008-01-01'
is lexigraphically "less than" your lower bound string of:
    '2008-01-01 00:00:00'
and so falls outside the range of your "BETWEEN" select.

If you want to keep treating the strings as text only, you might want to
use
    '2008-01-01'
as your lower bound.


You may want to look into:
    http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions





-----Original Message-----
From: Doug Van Horn [mailto:dougvanhorn@...]

$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01'); select d from foo
sqlite> where d between '2008-01-01' and '2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and
'2008-01-31 23:59:59.999999';
sqlite> .quit

In English, why does adding the 'time' portion to the between clause not
find the record?
=================================

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------

[SimonDavies]

Hi Doug,

I'm guessing that you expect your inserted values to be treated as a date.

BUT
sqlite> create table foo( d date null );
sqlite> insert into foo(d) values( '2008-01-01' );
sqlite> select d, typeof(d) from foo;
2008-01-01|text

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions may provide
some insight

Rgds,
Simon


On 06/12/2007, Doug Van Horn <dougvanhorn@...> wrote:

>
> Hi,
>
> I'm running into a problem with the database library in Django running
> against SQLite.  I'm trying to understand why the following happens:
>
> $ sqlite3 date_test
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> create table foo (d date null);
> sqlite> insert into foo (d) values ('2008-01-01');
> sqlite> select d from foo where d between '2008-01-01' and '2008-01-31';
> 2008-01-01
> sqlite> select d from foo where d between '2008-01-01 00:00:00' and
> '2008-01-31 23:59:59.999999';
> sqlite> .quit
>
> In English, why does adding the 'time' portion to the between clause not
> find the record?
>
>
> Thanks for any help or insights...
>
> Doug Van Horn
> --
> View this message in context: http://www.nabble.com/Querying-DATE-column-with-date-time-string.-tf4956413.html#a14193493
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to sqlite-users-unsubscribe@...
> -----------------------------------------------------------------------------
>
>

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------

[Doug Van Horn]

Doug Van Horn wrote:

Hi, I'm running into a problem with the database library in Django running against SQLite. I'm trying to understand why the following happens: $ sqlite3 date_test SQLite version 3.4.2 Enter ".help" for instructions sqlite> create table foo (d date null); sqlite> insert into foo (d) values ('2008-01-01'); sqlite> select d from foo where d between '2008-01-01' and '2008-01-31'; 2008-01-01 sqlite> select d from foo where d between '2008-01-01 00:00:00' and '2008-01-31 23:59:59.999999'; sqlite> .quit In English, why does adding the 'time' portion to the between clause not find the record? Thanks for any help or insights... Doug Van Horn

Thanks Dan, Igor, Donald, and Simon! It's a problem with the Django library (a Python web framework), where that between clause is being used for several different databases. I'm going to report back there with your (collective) explanation. I appreciate the help!

[Samuel Neff]

'between' will work fine with your situation, you just have to use

between '2008-01-01' and '2008-01-31 23:59:59.999999'

or even better

between '2008-01-01' and '2008-01-31Z'

HTH,

Sam

-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact careers@...
 
-----Original Message-----
From: Doug Van Horn [mailto:dougvanhorn@...]
Sent: Thursday, December 06, 2007 10:30 AM
To: sqlite-users@...
Subject: Re: [sqlite] Querying DATE column with date/time string.


Thanks Dan, Igor, Donald, and Simon!

It's a problem with the Django library (a Python web framework), where that
between clause is being used for several different databases.  I'm going to
report back there with your (collective) explanation.  I appreciate the
help!


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------

[Tom Shaw-2]

I have 2 PHP 5 scripts simulatneously that just access a sqlite 3 DB
and update a entry or two or insert a new row.  They do not change
the schema of the DB however periodically I get the following error .
Can you explain 1) how we would get this and 2) how to stop it.

Error!: SQLSTATE[HY000]: General error: 17 database schema has changed

Thanks for your help

Tom

PS I also see periodically a DB locked error which shuts down a
script yet I am doing only simple times and nothing is taking a long
time.


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@...
-----------------------------------------------------------------------------