List: | General Discussion | « Previous MessageNext Message » | |

From: | Michael Stassen | Date: | August 24 2005 4:10am |

Subject: | Re: Date arithmetic: 2005-08-31 - 1 | ||

View as plain text |

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

Thread | ||
---|---|---|

• Date arithmetic: 2005-08-31 - 1 | Barbara Deaton | 23 Aug |

• AW: Date arithmetic: 2005-08-31 - 1 | Freddie Sorensen | 23 Aug |

• Re: Date arithmetic: 2005-08-31 - 1 | SGreen | 23 Aug |

• Re: Date arithmetic: 2005-08-31 - 1 | Michael Stassen | 24 Aug |

• RE: Date arithmetic: 2005-08-31 - 1 | Gordon Bruce | 23 Aug |

• RE: Date arithmetic: 2005-08-31 - 1 | Barbara Deaton | 23 Aug |

• Re: Date arithmetic: 2005-08-31 - 1 | Peter Brawley | 25 Aug |

• Re: Date arithmetic: 2005-08-31 - 1 | Michael Stassen | 24 Aug |