The Passionate Craftsman

Ruby, PHP, MySql, Software engineering and more.

Friday, 16 April 2010

Date and time functions in MySQL

MySQL has many date and time functions to manipulate and compare dates and time, the reference documentation form 5.1 version. If you want to add a day to a datetime or date field:

SELECT now(), now() + INTERVAL 10 DAY;

If you run the query above you will see the current date and the current date plus ten days. You can add or subtract even hours, second and more.

FROM article
WHERE approved=1
(birth <> now( ))
OR (news=1 OR never_expires=1)

The example above shows a query which loads the articles between the bith and now and between now and the expiry (death) date. If hte article is a news or should not expire, the date comparison is not taken into consideration.

If you want the current time:


If you want to get the day of a date:

SELECT DAY('2009-12-15');

If you want the difference, in days, between two dates:

SELECT DATEDIFF('2007-12-30 23:59:59','2007-12-20');

The result is 10 because MyQSL subtract the second value from the first. The documentation is quite clear and complete.



Post a Comment

Subscribe to Post Comments [Atom]

<< Home