Author: tom000
Date: 2007-03-06 17:11:44

With the aid of some built-in functions you can processing date in MySQL database in very handy way. Now, here I'm going to presents few functions which you can use to implement this goals.

Few samples of use

The above operations haven't any bigger usage in operating on database. Usually they are used with condition WHERE. It's let accurately state which data will be downloaded, updated or deleted from database. It's often use to add new records to database too.

Adding new data with current date:

INSERT INTO test.test (vol,date) VALUES('aaa',NOW());

Adding new data with tomorrow date:

INSERT INTO test.test (vol,date) 
 VALUES('aaa',DATE_ADD(NOW(),INTERVAL 1 DAY));

Adding new data with date from last year:

INSERT INTO test.test (vol,date)
 VALUES('aaa',DATE_SUB(NOW(),INTERVAL 1 YEAR));

Getting data older than one year:

SELECT * FROM test.test 
 WHERE test.date < DATE_SUB(NOW(),INTERVAL 1 YEAR);

Getting data between tomorrow - yesterday:

SELECT * FROM test.test 
 WHERE test.date > DATE_SUB(NOW(),INTERVAL 1 DAY) 
 AND test.date < DATE_ADD(NOW(),INTERVAL 1 DAY);

Getting data with yesterday date:

SELECT * FROM test.test WHERE test.date LIKE 
 CONCAT(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'),'%');

Showing date without time:

SELECT id, DATE_FORMAT(date,'%Y-%m-%d') as date FROM test.test;

Adding to date one year:

UPDATE test.test SET date = 
 DATE_ADD(date, INTERVAL 1 YEAR) WHERE id='1';

Getting difference between current date and chosen:

SELECT id,TO_DAYS(NOW()) - TO_DAYS(date)
 as difference FROM test.test;

Getting day of week:

SELECT id,DAYNAME(date) as day_of_week FROM test.test;

« 1 2 [3] »

Comments

Add comment

Login :
Website :
Comment :
Image text :