List:General Discussion« Previous MessageNext Message »
From:Gordon Bruce Date:August 23 2005 7:55pm
Subject:RE: Date arithmetic: 2005-08-31 - 1
View as plain text  
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:Barbara.Deaton@stripped] 
Sent: Tuesday, August 23, 2005 2:37 PM
To: mysql@stripped
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/mysql?unsub=1

 

 


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