Do You know about INTERVAL?
Use it in an exprecssion or funtion as
..................................INTERVAL expr type
where expr is any numerical value
* The INTERVAL keyword and the type specifier are not case
sensitive.
The following table shows how the type and expr arguments are related:
type Value
Expected expr Format
MICROSECOND
MICROSECONDS
SECOND
SECONDS
MINUTE
MINUTES
HOUR
HOURS
DAY
DAYS
WEEK
WEEKS
MONTH
MONTHS
QUARTER
QUARTERS
YEAR
YEARS
SECOND_MICROSECOND
'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND
'MINUTES.MICROSECONDS'
MINUTE_SECOND
'MINUTES:SECONDS'
HOUR_MICROSECOND
'HOURS.MICROSECONDS'
HOUR_SECOND
'HOURS:MINUTES:SECONDS'
HOUR_MINUTE
'HOURS:MINUTES'
DAY_MICROSECOND
'DAYS.MICROSECONDS'
DAY_SECOND
'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE
'DAYS HOURS:MINUTES'
DAY_HOUR
'DAYS HOURS'
YEAR_MONTH
'YEARS-MONTHS'
mysql> select min(addr_id) from addresses;
+--------------+
| min(addr_id) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> select now() + INTERVAL min(addr_ID) Day from addresses;
+-----------------------------------+
| now() + INTERVAL min(addr_ID) Day |
+-----------------------------------+
| 2005-08-25 15:38:15 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select now()
-> ;
+---------------------+
| now() |
+---------------------+
| 2005-08-23 15:38:31 |
+---------------------+
1 row in set (0.00 sec)
-----Original Message-----
From: Barbara Deaton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 23, 2005 2:37 PM
To: mysql@lists.mysql.com
Subject: Date arithmetic: 2005-08-31 - 1
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
