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]
