PHP —

MySQL Date and time functions

Table 3-5 shows sample uses of selected time and date functions available in MySQL. The date_add( ) function can be used to add and subtract times and dates; more details can be found in Section 7.4.11 of the manual.html file distributed with MySQL.

dayofweek : Select day of week from date

Returns the weekday index for date. 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday and 7 = Saturday 

SELECT dayofweek('2010-05-03');

Output: 2

dayname : Select day's name from date

Returns the full name of the weekday for date. i.e. Sunday, Monday...

SELECT dayname('2010-05-03');

Output: Monday

monthname : Select day month's name from date

Returns the full name of the month for date. i.e. January, February...

SELECT monthname('2010-05-03');

Output: May

week

This function returns the week number for date. WEEK(date[,mode]) accepts two arguments (with optional second argument "mode"). The optional "mode" enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. For more information http://dev.mysql.com 

SELECT week('2010-05-03');

Output: 18

date_add

This function add time values (intervals) to a date value.

Adding one day interval to existing date:

SELECT date_add("2010-05-03", INTERVAL 1 DAY);

Output: 2010-05-04

Adding one year interval to existing date:

SELECT date_add("2010-05-03", INTERVAL 1 YEAR);

Output: 2011-05-03

Adding one second interval to existing date and time:

SELECT date_add("2012-01-01 11:27:20", INTERVAL 1 SECOND)

Output: 2012-01-01 11:27:21

curdate

This function returns the current date.

SELECT curdate(  );

Output: 2012-01-01

curtime

This function returns the current time.

SELECT curtime(  );

Output: 11:27:20

now

This function returns the current date and time.

SELECT now(  );

Output: 2012-01-01 11:27:20