"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 08/23/2005 03:36:52 PM:
> 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 > You are right. There are a lot of date functions in MySQL. However, I am not sure exactly what your needs are... I think you are trying to compute date intervals (did you NOT see the INTERVAL keyword when you RTFM?). Most of the date functions currently supported are for v4.1 or newer and you didn't say which version you are on. The pre-v4.1 way of doing date math is to convert your datetime values into seconds, add or subtract some number of seconds, then convert your answer back into a date. To do that you need the UNIX_TIMESTAMP()/FROM_UNIXTIME() pair of functions ( http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html) The pre v4.1 way of computing a date range (it even works with non-integral ranges as in my example) SET @basedate = '2005-05-03 16:00:00', @daysplusminus = 1.5; SET @baseunix = UNIX_TIMESTAMP(@basedate), @secsplusminus = [EMAIL PROTECTED]; SET @startdate = FROM_UNIXTIME(@baseunix - @secsplusminus), @enddate = FROM_UNIXTIME(@baseunix + @secsplusminus); SELECT @startdate, @enddate; +---------------------+---------------------+ | @startdate | @enddate | +---------------------+---------------------+ | 2005-05-02 04:00:00 | 2005-05-05 04:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) The post v4.1 way (uses only integral days): SET @basedate = '2005-05-31', @daysplusminus = 1.5; SET @startdate = @basedate - interval @daysplusminus day, @enddate = @basedate + interval @daysplusminus day; SELECT @startdate, @enddate; +------------+------------+ | @startdate | @enddate | +------------+------------+ | 2005-05-30 | 2005-06-01 | +------------+------------+ 1 row in set (0.00 sec) Does that help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
