MySQL Date Time Functions

Tutorials

Mysql

Topic

What are the Datetime Functions in MySQL?

Explanation

MySQL Date Time Functions :

    This function is used to manipulate the display format of a date and time. Lets see some basic functions for date and time.

CURDATE
CURTIME
DATEDIFF
DATE_ADD
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW

                      Lets we see the Date Time Functions In MySQL

MySQL Date Format :

     The following details shows the mysql Date format.

CURDATE() :

    This date function returns the current date in the format 'YYYY-MM-DD' or 'YYYYMMDD'.

mysql> select curdate();
 --> 2007-01-03

CURTIME() :

Returns the current time in the format 'HH:MM:SS' or 'HHMMSS'.

mysql> select curtime();
 --> 17:33:07

DATEDIFF(expression1,expression2) :

expression1 and expression2 are date or date-and-time expressions. This function returns expression1 � expression2 expressed as a value in days from one date to the other. Here only the date parts will be considered for calculation.

mysql> select datediff('2007-2-6 17:33:25','2007-1-1');
 --> 36

DATE_ADD(datetime, INTERVAL expression datetimetype) :

This date function adds the expression to the datetime supplied.

mysql> select date_add('2007-1-14', interval 15 day);
 --> 2007-01-29

This function is same for DATE_SUB, but subtracting will take place instead of adding.

DAYNAME(date) :

Returns the name of the day for the specified date.

mysql> select dayname('2007-01-04');
 --> Thursday

DAYOFMONTH(date) or DAY(date) :

Returns the date for the day of the month in the range of 1 to 31. DAY() is a synonym for DAYOFMONTH().

mysql> select dayofmonth('2007-01-04');
 --> 4

DAYOFWEEK(date) :

Returns the day of the week in the numeric format as 1 for Sunday to 7 for Saturday.

mysql> select dayofweek('2007-01-04');
 --> 5

DAYOFYEAR(date) :

Returns the day of the year for given date in the numeric format, in the range 1 to 366.

mysql> select dayofyear('2007-07-09');
 --> 190

     The above description defines how to use mysql Date format.

MySQL Time Functions

     The following details shows the mysql time funcions.

HOUR(time) :

    Returns the hour of the specified time in the numeric format from 0 to 23.

mysql> select hour('14:46:12');
 --> 14

MINUTE(time) :

Returns the minute of the specified time in the numeric format from 0 to 59.

mysql> select minute('14:46:12');
 --> 46

MONTH(date) :

Returns the month for the given date in the numeric format, in the range 0 to 12.

mysql> select month('2007-07-09');
 --> 7

MONTHNAME(date) :

Returns the name of the month for the specified date.

mysql> select monthname('2007-07-09');
 --> July

NOW() :

This date time function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.

mysql> select now();
 --> 2007-01-04 14:56:15

The above details shows how to use the MySQL Date Time Functions.