List:General Discussion« Previous MessageNext Message »
From:(Hal Date:March 17 2012 6:00am
Subject:Re: date comparison query
View as plain text  
>>>> 2012/03/16 13:30 -0400, Simon Wilkinson >>>>
My query for this is as follows: select * from table where table.date1 -
table.date2 between 28425600 and 29030400;
<<<<<<<<
I would not count on that subtraction s yielding a meaningful number: the types are not
Unix timestamps. I would use TIMESTAMPDIFF, with a good unit.

 From the downloaded help file:

>>>>
Date arithmetic also can be performed using INTERVAL together with the
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_plus>+
or
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.1/HELP/functions.html#operator_minus>-
operator: 


date + INTERVAL expr unit
date - INTERVAL expr unit

<<<<

but subtracting timestamp from timestamp hoping for a while (interval) is not mentioned.
If you want the subtraction to work, make them big integers and use UNIX_TIMESTAMP and
FROM_UNIXTIME. See
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html 

Thread
date comparison querySimon Wilkinson16 Mar
  • Re: date comparison queryBaron Schwartz16 Mar
  • Re: date comparison queryhsv17 Mar
    • One inst has 39 columns- the other 40Charles Brown18 Mar
      • Re: One inst has 39 columns- the other 40Johan De Meersman19 Mar
  • One inst has 39 columns- the other 40Charles Brown19 Mar
  • RE: One inst has 39 columns- the other 40Charles Brown21 Mar
    • Re: One inst has 39 columns- the other 40Johan De Meersman21 Mar
      • Re: One inst has 39 columns- the other 40Rik Wasmus21 Mar
        • RE: One inst has 39 columns- the other 40Charles Brown22 Mar
          • Re: One inst has 39 columns- the other 40Nuno Tavares22 Mar
            • Re: One inst has 39 columns- the other 40Michael Dykman22 Mar
          • Re: One inst has 39 columns- the other 40Reindl Harald22 Mar
            • RE: One inst has 39 columns- the other 40Charles Brown22 Mar
          • Re: One inst has 39 columns- the other 40Mark Goodge22 Mar
    • Re: One inst has 39 columns- the other 40Reindl Harald21 Mar
      • tables do not have the same number of columns. Charles Brown22 Mar
        • Re: tables do not have the same number of columns.Johan De Meersman22 Mar