Thursday, July 14, 2011

Mysql Date Calculation

Simple Function for Date Computation.. useful in archive retrieval

ADDDATE(date,INTERVAL expr type)
ADDDATE(date_field,INTERVAL 1 DAY)
ADDDATE(date,INTERVAL 2 DAY)
ADDDATE and SUBDATE perform date arithmetic calculations.

date is a DATETIME or DATE value specifying the starting date.
expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a .-. for negative intervals.
type is a unit keyword indicating the units in which the expression should be interpreted.
Type Unit Expected Format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
Here we use the SUBDATE function to select products and their images added within the last month:

SELECT p.id, product_code, product_name, filename, date_added
FROM product_tbl p
INNER JOIN image_tbl i
ON p.id=i.product_id
WHERE date_added>SUBDATE(CURDATE(),INTERVAL 2 MONTH);


Source: Mysql Date Calulation - liamdelahunty.com

1 comment: