Barbara Deaton wrote:
> All,
>
> I know MySQL comes with all sorts of wonderful functions to do date
> arithmetic, the problem is the context that my application is being
> called in I don't know if a user wants me to add or subtract days.  I'm
> just given the number of days that need to be either added or subtracted
> from the date given.
>
> So for example, if your table was
>
> mysql> select * from dtinterval;
> +------------
> | datecol
> +------------
> 2005-09-01
> 2005-08-30
> 2005-08-31
> +--------------
>
> a user could enter:
>
> select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
>
> Which is our applications SQL, my part of the product is only give the
> value 1, I have to transform that into something MySQL will understand as
> 1 day and then pass that back into the SQL statement to be passed down to
> the MySQL database.  I transform our applications SQL into
> select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = '1974-12-04'
>
> I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
> and that just gives me a year
>
> mysql> select '2005-08-31' - 1;
> +------------------+
> | '2005-08-31' - 1 |
> +------------------+
> |             2004 |
> +------------------+
>
> What do I need to translate the 1 into in order to get back the value
> '2005-08-30' ?
>
> Thanks for your help.
> Barbara

The answer is contained in the previous replies, and in the referenced manual page. Translate the 1 into

  INTERVAL 1 DAY

So your query becomes

  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` - INTERVAL 1 DAY =  '1974-12-04';

In general, n days becomes INTERVAL n DAY, so the query becomes

  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` - INTERVAL n DAY =  '1974-12-04';

That isn't the right way to do it, however. This query compares a value which depends on the row with a constant. No index on datecol can be used to satisfy this query. You get a full-table scan, with the date calculation done on every row. Always write your WHERE clauses to avoid calculations involving row values, if possible. In this case, your query should be

  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` =  '1974-12-04' + INTERVAL n DAY;

Adding n days to the constant date on the right results in a constant, so it can be done once, then the resulting constant can be compared with the values of datecol. In this case, an index on datecol can be used to make this quick.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to