What are different Date and Time functions in MySQL ?

Date / Time functions

ADDTIME(time1, time2)

Adding 2 time values

SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002');

1998-01-02 01:01:01.000001

SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');           03:00:01.999997

CURDATE()

Returns current date. Same as CURRENT_DATE()

CURTIME()

Returns current time. Same as CURRENT_TIME()

SELECT CURDATE();

DATE(expression)

Returns date from the expression

SELECT DATE('2003-12-31 01:02:03');

DATEDIFF(date1, date2)

Returns dates difference in days

SELECT DATEDIFF('1997-12-31 23:59:59', '1997-12-30');

1

SELECT * FROM employee WHERE ( DATEDIFF(curdate(), start_date) / 365.25 ) >55;

DAY(date)

Returns an integer representing the day of the month. Same as DAYOFMONTH()

DAYNAME(date)

Returns name of week day

SELECT DAYNAME('1998-02-05');

Thursday

DAYOFMONTH(date)

Returns an integer representing the day of the month.

SELECT DAYOFMONTH('1998-02-03');

3

DAYOFWEEK(date)

Returns an integer representing the day of the week. (Returns Day week index)

SELECT DAYOFWEEK('1998-02-03');

3

DAYOFYEAR(date)

Returns an integer representing the day of the year.

SELECT DAYOFYEAR('1998-02-03');

34

EXTRACT(unit FROM date)

Returns a unit value of a date and time.

SELECT EXTRACT(YEAR FROM '1999-07-02');

1999

Same expression units can be used as used in DATE_ADD

HOUR(time)

Returns hour value of a time

SELECT HOUR('10:05:03');

10

LAST_DAY(date)

Returns a date representing the last day of the month based on the given date.

SELECT LAST_DAY('2003-02-05');

2003-02-28

LOCALTIME()

Returns the current date and time. Same as NOW()

MAKETIME(hour,minute,second)

Returns a date based on the given time unit values.

SELECT MAKETIME(12,15,30);

12:15:30

MICROSECOND(time)

Returns the microsecond unit value of the given time.

SELECT MICROSECOND('12:00:00.123456');

123456

MINUTE(time)

Returns the minute unit value of the given time.

SELECT MINUTE('98-02-03 10:05:03');

5

MONTH(date)

Returns the month unit value of the given date.

SELECT MONTH('1998-02-03');

2

MONTHNAME(date)

Returns the month name of the given date.

SELECT MONTHNAME('1998-02-05');

February

NOW()

Returns the current date and time.

SECOND(time)

Returns the second unit value of the given time.

SELECT SECOND('11:05:03');

3

SEC_TO_TIME(seconds)

Converts a second value to a time

SELECT SEC_TO_TIME(1378);

00:22:58

STR_TO_DATE(str, format)

Converts a character string to a date.

SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

2004-04-31 

SUBDATE(date,INTERVAL expr unit)

Subtract days from a date.

SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

1997-12-02

SUBTIME(expr1,expr2)

Subtract a time from another time.

SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

-00:59:59.999999

SYSDATE()

Returns current date and time.

TIME(expr)

Returns a time from the given date and time.

SELECT TIME('2003-12-31 01:02:03.000123');

01:02:03.000123

TIMEDIFF(expr1,expr2)

Returns the difference between two times.

SELECT TIMEDIFF('1997-12-31 23:59:59.000001', '1997-12-30 01:01:01.000002');

46:58:57.999999

TIME_TO_SEC(time)

Converts a time to a second value.

SELECT TIME_TO_SEC('00:39:38');

2378

WEEKDAY(date)

Returns the weekday unit value of a given date.

SELECT WEEKDAY('1997-11-05');

2

YEAR(date)

Returns the year unit value of a given date.

SELECT YEAR('98-02-03');

1998

Expression units

MICROSECOND , SECOND , MINUTE , HOUR

DAY , WEEK , MONTH , QUARTER , YEAR

SECOND_MICROSECOND , MINUTE_MICROSECOND , HOUR_MICROSECOND ,

DAY_MICROSECOND

MINUTE_SECOND , HOUR_SECOND , DAY_SECOND

HOUR_MINUTE , DAY_MINUTE 

DAY_HOUR , YEAR_MONTH

Manipulating dates

ADDDATE(date, INTERVAL expr unit)

Add days to a date. Same as DATE_ADD()

SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

1998-02-02

SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

1998-02-02

SELECT ADDDATE('1998-01-02', 31);  // adds days 

1998-02-02

DATE_ADD(date, INTERVAL expr unit)

Add days to a date

select DATE_ADD(curdate(), INTERVAL 1 HOUR);

select DATE_ADD(curdate(), INTERVAL 1.1 MINUTE_SECOND);

DATE_SUB(date, INTERVAL expr unit)

Subtract days from a date.

select DATE_SUB(curdate(), INTERVAL 1 DAY);

Same expression units can be used as used in DATE_ADD

DATE_FORMAT(date, format)

Returns a character string representing a date.

SELECT first_name, DATE_FORMAT(start_date, '%d-%m-%Y') FROM employee;

Formatting patterns

%a  Abbreviated weekday name (Sun..Sat)

%b  Abbreviated month name (Jan..Dec)

%c  Month, numeric (0..12)

%D  Day of the month with English suffix (0th, 1st, 2nd, 3rd, )

%d  Day of the month, numeric (00..31)

%e  Day of the month, numeric (0..31)

%f   Microseconds (000000..999999)

%H  Hour (00..23)

%h  Hour (01..12)

%I   Hour (01..12)

%i   Minutes, numeric (00..59)

%j   Day of year (001..366)

%k  Hour (0..23)

%l   Hour (1..12)

%M Month name (January..December)

%m Month, numeric (00..12)

%p  AM or PM

%r   Time, 12-hour (hh:mm:ss followed by AM or PM)

%S  Seconds (00..59)

%s  Seconds (00..59)

%T  Time, 24-hour (hh:mm:ss)

%U  Week (00..53), where Sunday is the first day of the week

%u  Week (00..53), where Monday is the first day of the week

%V  Week (01..53), where Sunday is the first day of the week; used with %X

%v  Week (01..53), where Monday is the first day of the week; used with %x

%W Weekday name (Sunday..Saturday)

%w  Day of the week (0=Sunday..6= Saturday)

%X  Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x  Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y  Year, numeric, four digits

%y  Year, numeric (two digits)

%%  A literal '%' character

%x   x, for any 'x' not listed above

SQL Examples

Getting data of between 2 dates (using datetime field)

SELECT * FROM connections c

WHERE c.date > DATE('2010-07-01 00:00:00') AND c.date < DATE('2010-08-01 00:00:00')

    OR you can directly mention date in quotes without using DATE function

WHERE c.date > '2010-07-01 00:00:00' AND c.date < '2010-08-01 00:00:00'

Getting data of between 2 dates (using date field)

SELECT * FROM connections c

WHERE c.date > DATE('2010-07-01') AND c.date < DATE('2010-08-01')

Getting data of between 2 dates (using AND and BETWEEN)

SELECT * FROM connections c

WHERE c.date BETWEEN DATE('2010-07-01') AND DATE('2010-08-01')