Simon, It's likely that when you specify the times as integer literals they are being converted to something you don't expect. You can use EXPLAIN EXTENDED followed by SHOW WARNINGS to see what's happening; it might be interesting and educational. I would specify the times you want as datetime literals, in 'quotes', to make sure the database interprets your values correctly.
On Fri, Mar 16, 2012 at 1:30 PM, Simon Wilkinson <simon.wilkin...@gmail.com> wrote: > Hi, > > I have a table that holds two datetime columns. Â I am trying to find values > from this table that fall into specific time ranges, but am getting some > strange results. Â For example, if I try to find rows where the difference > between the two column is between 47 and 48 weeks, I get back a result > where the actual difference is less than 1 month. > > My query for this is as follows: select * from table where table.date1 - > table.date2 between 28425600 and 29030400; > > The result returns a row where date1 is 2010-10-31 18:24:49, and date2 > is 2010-10-02 20:29:54. > > I seem to get proper results for some values (I am trying to find results > that fall into different weekly ranges), but then some are just way off. > Â Does anybody have any ideas for why this is happening? > > Thanks, > > Simon -- Baron Schwartz Win free MySQL conference tickets! http://goo.gl/mvZ4W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
