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
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
