Even though this is a bit more complicated, it does seem to work correctly.
 
SELECT t1.realdate1,
             t2.realdate2
from test_table_1 t1 left outer join test_table_2 t2 where
    strftime ('%s', t2.realdate2) between  strftime ('%s', 
datetime(t1.realdate1, '-1 minutes') ) and   strftime ('%s', 
datetime(t1.realdate1, '+1 minutes') )
 
 
I guess there are parts of SQLite that work better in different usages.
 
Bart

________________________________

From: sqlite-users-boun...@sqlite.org on behalf of Barton Torbert
Sent: Mon 9/21/2009 6:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons



Richard,

What you suggested sort of worked.  The problem was that the two dates I were 
comparing were only 1 minute apart, but I had to put in '-1000 minutes' and 
'+1000 minutes' to get the test to work. 

My two data values are;
2009-04-13T12:19:00.0000000 
and
2009-04-13T12:18:00.0000000

Bart

________________________________

From: sqlite-users-boun...@sqlite.org on behalf of D. Richard Hipp
Sent: Mon 9/21/2009 6:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons




On Sep 21, 2009, at 8:00 PM, Barton Torbert wrote:

> My data are dates in the format;
> YYYY-MM-DD HH:MM:SS.SSS
>
> I am trying to use the function strftime () to convert the data to
> seconds since 1970-01-01.  I was hoping that within a WHERE clause 
> or the ON condition in an OUTER JOIN.
>
> What I wanted to do was something like this;
>
> SELECT t1.dateitem1,
>                 t2.dateitem2
> FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
>          strftime ('%s', t1.dateitem1) BETWEEN strftime
> (t2.dateitem2) - 600 AND
> strftime (t2.dateitem2) + 600
>
> Where columns dateitem1 and dateitem 2 are both declared as DateTime
> fields.
>
> Can this ( or something like it ) work?

You left out the '%s' on the second two strftime() calls....

I think the following will likely run faster if you have an index on
table2.dateitem2:

SELECT ... FROM table1 AS t1 LEFT JOIN table2 AS t2
  WHERE t2.dateitem2 BETWEEN datetime(t1.dateitem1, '-10 minutes') AND
datetime(t1.dateitem1,'+10 minutes');

D. Richard Hipp
d...@hwaci.com



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to