List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 23 2005 8:00pm
Subject:Re: Date arithmetic: 2005-08-31 - 1
View as plain text  
"Barbara Deaton" <Barbara.Deaton@stripped> 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 = 
24*3600*@daysplusminus;
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

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