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 - 1Barbara Deaton23 Aug
  • AW: Date arithmetic: 2005-08-31 - 1Freddie Sorensen23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1SGreen23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1Michael Stassen24 Aug
RE: Date arithmetic: 2005-08-31 - 1Gordon Bruce23 Aug
RE: Date arithmetic: 2005-08-31 - 1Barbara Deaton23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1Peter Brawley25 Aug
Re: Date arithmetic: 2005-08-31 - 1Michael Stassen24 Aug