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