Date and Time Functions

Date and time functions operate on MySQL date-time data types such as DATE and DATETIME.

9.3.1. ADDTIME

 date1 =ADDTIME (date2 ,time_interval )

ADDTIME adds the specified time interval to the date-time provided and returns the amended date. Time intervals are specified in the format hh:mm:ss.hh, so you can add any time interval down to one-hundredth of a second.

 SET var1=NOW( );  2005-07-21 18:56:46
 SET var2=ADDTIME(NOW( ),"0:00:01.00");  2005-07-21 18:56:47
 SET var3=ADDTIME(NOW( ),"0:01:00.00");  2005-07-21 18:57:46
 SET var4=ADDTIME(NOW( ),"1:00:00.00") ;  2005-07-21 19:56:46

 

9.3.2. CONVERT_TZ

 datetime1 =CONVERT_TZ (datetime2 ,fromTZ ,toTZ )

This function converts a date-time value from one time zone to another. The valid time zone values can be found in the table mysql.time_zone_name.

You may have to load the MySQL time zone tables; for instructions, see the MySQL manual section "MySQL Server Time Zone Support."

9.3.3. CURRENT_DATE

 date =CURRENT_DATE ( )

CURRENT_DATE returns the current date. It does not show the time.

 SET var1=CURRENT_DATE( );  2005-07-21

 

9.3.4. CURRENT_TIME

 time =CURRENT_TIME ( )

CURRENT_TIME returns the current time. It does not show the date.

 SET var1=CURRENT_TIME( );  22:12:21

 

9.3.5. CURRENT_TIMESTAMP

 timestamp =CURRENT_TIMESTAMP ( )

CURRENT_TIMESTAMP returns the current date and time in the format yyyy-mm-dd hh:mm:ss.

 SET var1=CURRENT_TIMESTAMP( );  2005-07-21 22:15:02

 

9.3.6. DATE

 date =DATE (datetime )

DATE returns the date part of a date-time value.

 SET var1=NOW( );  2005-07-23 12:08:52
 SET var2=DATE(var1) ;  2005-07-23

 

9.3.7. DATE_ADD

 date1 =DATE_ADD (date2 , INTERVAL interval_value interval_type )

DATE_ADD returns the date-time that results from adding the specified interval to the date-time provided. Possible intervals are listed in Table 9-3.

 SET var1=NOW( );  2005-07-20 22:33:21
 SET var2=DATE_ADD(NOW( ), INTERVAL 7 DAY);  2005-07-27 22:33:21
 SET var3=DATE_ADD(NOW( ), INTERVAL 0623 DAY_HOUR) ;  2005-08-15 21:33:21
 SET var4=DATE_ADD(NOW( ), INTERVAL 06235959 DAY_SECOND) ;  2005-10-01 02:46:00
 SET var5=DATE_ADD(NOW( ), INTERVAL 2 MONTH);  2005-09-20 22:33:21
 SET var6=DATE_ADD(NOW( ), INTERVAL 10 YEAR);  2015-07-20 22:33:21
 SET var7=DATE_ADD(NOW( ), INTERVAL 3600 SECOND);  2005-07-20 23:33:21

Table 9-3. Date-time formats for DATE_ADD and DATE_SUB

Interval name	Interval format
DAY	dd
DAY_HOUR	ddhh
DAY_MINUTE	dd hh:mm
DAY_SECOND	dd hh:mm:ss
HOUR	hh
HOUR_MINUTE	hh:mm
HOUR_SECOND	hh:mm:ss
MINUTE	mm
MINUTE_SECOND	mm:ss
MONTH	mm
SECOND	ss
YEAR	yyyy

 

9.3.8. DATE_FORMAT

 string =DATE_FORMAT (datetime ,FormatCodes )

DATE_FORMAT accepts a date-time value and returns a string representation of the date in the desired format. Format codes are shown in Table 9-4.

 SET var1=NOW( );  2005-07-23 13:28:21
 SET var2=DATE_FORMAT(NOW( ),"%a %d %b %y");  Sat 23 Jul 05
 SET var3=DATE_FORMAT(NOW( ),"%W, %D %M %Y");  Saturday, 23rd July 2005
 SET var4=DATE_FORMAT(NOW( ),"%H:%i:%s") ;  13:28:21
 SET var5=DATE_FORMAT(NOW( ),"%T");  13:28:21
 SET var6=DATE_FORMAT(NOW( ),"%r");  01:28:22 PM

Table 9-4. Format codes for DATE_FORMAT

Code	Explanation
%%	The % sign
%a	Short day of the week (Mon-Sun)
%b	Short month name (Jan-Feb)
%c	Month number (1-12)
%d	Day of the month (1-31)
%D	Day of the month with suffix (1st, 2nd, 3rd, etc.)
%e	Day of the month, numeric (1-31)
%h	12-hour clock hour of the day (1-12)
%H	24-hour clock hour of the day (00-23)
%i	Minute of the hour (00...59)
%I	12-hour clock hour of the day (1-12)
%j	Day of the year (1-365)
%k	24-hour clock hour of the day (00-23)
%l	12-hour clock hour of the day (1-12)
%m	Month of the year (1-12)
%M	Long month name (January-December)
%p	AM/PM
%r	Hour, minute, and second of the day, 12-hour format (hh:mm:ss AM|PM)
%s	Seconds within a minute (0-59)
%S	Seconds within a minute (0-59)
%T	Hour, minute, and second of the day, 24-hour format (HH:mm:ss)
%u	Week of the year (0-52) (Monday is the first day of the week)
%U	Week of the year (0-52) (Sunday is the first day of the week)
%v	Week of the year (1-53) (Monday is the first day of the week)
%V	Week of the year (1-53) (Sunday is the first day of the week)
%w	Numeric day of the week (0=Sunday, 6=Saturday)
%W	Long weekday name (Sunday, Saturday)
%y	Year, numeric, 2 digits
%Y	Year, numeric, 4 digits

 

9.3.9. DATE_SUB

 date1 =DATE_SUB (date2 , INTERVAL interval_value interval_type )

DATE_SUB returns the date-time resulting from subtracting the specified interval from the date-time provided. Possible intervals are listed in Table 9-3.

Example 9-10 shows a stored procedure that determines if an employee's date of birth indicates an age of greater than 18 years. DATE_SUB is used to create a date 18 years earlier than the current date. This date is compared to the date of birth and, if it is earlier, we can conclude that the employee is less than 18 years old.

Example 9-10. Using DATE_SUB

CREATE PROCEDURE validate_age
 (in_dob DATE,
 OUT status_code INT,
 OUT status_message VARCHAR(30))
BEGIN

 IF DATE_SUB(now( ), INTERVAL 18 YEAR) 

9.3.10. DATEDIFF

 days =DATEDIFF (date1 ,date2 )

DATEDIFF returns the number of days between two dates. If date2 is greater than date1, then the result will be negative; otherwise, it will be positive.

Example 9-11 uses DATEDIFF to calculate the number of days that have elapsed since a bill due date, and returns appropriate status and messages if the bill is more than 30 or 90 days old.

Example 9-11. Using DATEDIFF

CREATE PROCEDURE check_billing_status
 (in_due_date DATE,
 OUT status_code INT,
 OUT status_message VARCHAR(30))
BEGIN
 DECLARE days_past_due INT;

 SET days_past_due=FLOOR(DATEDIFF(now( ),in_due_date));
 IF days_past_due>90 THEN
 SET status_code=-2;
 SET status_message='Bill more than 90 days overdue';
 ELSEIF days_past_due >30 THEN
 SET status_code=-1;
 SET status_message='Bill more than 30 days overdue';
 ELSE
 SET status_code=0;
 SET status_message='OK';

 END IF;
END;

9.3.11. DAY

 day =DAY (date )

DAY returns the day of the month (in numeric format) for the specified date.

 SET var1=NOW( );  2005-07-23 13:47:13
 SET var2=DAY(NOW( ));  23

 

9.3.12. DAYNAME

 day =DAYNAME (date )

DAYNAME returns the day of the weekas in Sunday, Monday, etc.for the specified date.

 SET var1=NOW( );  2005-07-23 13:50:02
 SET var2=DAYNAME(NOW( ));  Saturday

 

9.3.13. DAYOFWEEK

 day =DAYOFWEEK (date )

DAYOFWEEK returns the day of the week as a number, where 1 returns Sunday.

 SET var1=NOW( );  2005-07-23 13:53:07
 SET var2=DATE_FORMAT(NOW( ),"%W, %D %M %Y");  Saturday, 23rd July 2005
 SET var3=DAYOFWEEK(NOW( ));  7

 

9.3.14. DAYOFYEAR

 day =DAYOFYEAR (date )

DAYOFYEAR returns the day of the year as a number, where 1-JAN returns 1 and 31-DEC returns 365 (except in leap years, where it returns 366).

 SET var1=NOW( );  2005-07-23 13:55:57
 SET var2=DAYOFYEAR(NOW( ));  204

 

9.3.15. EXTRACT

 date_part =EXTRACT (interval_name  FROM date )

EXTRACT returns a specified portion of a date-time. The applicable intervals are shown in Table 9-3.

 SET var1=NOW( );  2005-07-23 14:01:03
 SET var2=EXTRACT(HOUR FROM NOW( ));  14
 SET var3=EXTRACT(YEAR FROM NOW( ));  2005
 SET var4=EXTRACT(MONTH FROM NOW( ));  7
 SET var5=EXTRACT(HOUR_SECOND FROM NOW( ));  140103
 SET var6=EXTRACT(DAY_MINUTE FROM NOW( ));  231401

 

9.3.16. GET_FORMAT

 format =GET_FORMAT (datetime_type ,locale )

GET_FORMAT returns a set of date formatting codesuitable for use with DATE_FORMATfor various date-time types and locales.

Format type can be one of the following:

• DATE
• TIME
• DATETIME
• TIMESTAMP

Format code can be one of the following:

• INTERNAL
• ISO
• JIS
• USA
• EUR

 SET var1=GET_FORMAT(DATE,"USA");  %m.%d.%Y
 SET var2=GET_FORMAT(DATE,"ISO");  %Y-%m-%d
 SET var3=GET_FORMAT(DATETIME,"JIS") ;  %Y-%m-%d %H:%i:%s
 SET var4=NOW( );  2005-07-24 13:27:58
 SET var5=DATE_FORMAT(NOW( ),GET_FORMAT(DATE,"USA"));  07.24.2005

 

9.3.17. MAKEDATE

 date =MAKEDATE (year ,day )

MAKEDATE takes the year (YYYY) and day-of-year arguments and converts them to a date value. The day-of-year argument is in the form that would be returned by DAYOFYEAR.

 SET var1=MAKEDATE(2006,1);  2006-01-01
 SET var2=MAKEDATE(2006,365);  2006-12-31
 SET var3=MAKEDATE(2006,200);  2006-07-19

 

9.3.18. MAKETIME

 time =MAKETIME (hour ,minute ,second )

MAKETIME takes the hour, minute, and second arguments and returns a time value.

 SET var4=MAKETIME(16,30,25);  16:30:25
 SET var5=MAKETIME(0,0,0);  00:00:00
 SET var6=MAKETIME(23,59,59);  23:59:59

 

9.3.19. MONTHNAME

 monthname =MONTHNAME (date )

MONTHNAME returns the full name of the month corresponding to the provided date.

 SET var1=NOW( );  2005-07-24 13:44:54
 SET var2=MONTHNAME(NOW( ));  July

 

9.3.20. NOW

 datetime =NOW ( )

NOW returns the current date and time. We have used this function in many previous examples as input to date and time functions.

9.3.21. SEC_TO_TIME

 time =SEC_TO_TIME (seconds )

SEC_TO_TIME returns a time value for a given number of seconds. The time is shown in hours, minutes, and seconds.

 SET var1=SEC_TO_TIME(1);  00:00:01
 SET var2=SEC_TO_TIME(3600);  01:00:00
 SET var3=SEC_TO_TIME(10*60*60);  10:00:00

 

9.3.22. STR_TO_DATE

 date =STR_TO_DATE (string ,format )

STR_TO_DATE takes a string representation of a date (as might be returned by DATE_FORMAT) and returns a standard date data type in the format specified by the format argument. The format string is the same as that used in DATE_FORMAT; possible values are listed in Table 9-4.

 SET var1=STR_TO_DATE("Sun 24 Jul 05","%a %d %b %y");  2005-07-24
 SET var2=STR_TO_DATE("Sunday, 24th July 2005","%W, %D %M %Y");  2005-07-24
 SET var3=STR_TO_DATE("3:53:54","%H:%i:%s");  03:53:54
 SET var4=STR_TO_DATE("13:53:54","%T");  13:53:54
 SET var5=STR_TO_DATE("01:53:54 PM","%r");  13:53:54

 

9.3.23. TIME_TO_SEC

 seconds =TIME_TO_SEC (time )

TIME_TO_SEC returns the number of seconds in the specified time value. If a date-time is provided, TIME_TO_SEC provides the number of seconds in the time part of that date only.

 SET var1=NOW( );  2005-07-24 14:05:21
 SET var2=TIME_TO_SEC("00:01:01");  61
 SET var3=TIME_TO_SEC(NOW( ));  50721

 

9.3.24. TIMEDIFF

 time =TIMEDIFF (datetime1 ,datetime2 )

TIMEDIFF returns the time difference between two arguments specified as date-time data types.

 SET var1=TIMEDIFF("2005-12-31 00:00:01","2005-12-31 23:59:59");  -23:59:58

 

9.3.25. TIMESTAMP

 datetime =TIMESTAMP (date ,time )

TIMESTAMP returns a date-time value from a specified date and time.

 SET var2=TIMESTAMP("2005-12-31","23:30:01");  2005-12-31 23:30:01

 

9.3.26. TIMESTAMPADD

 date_time =TIMESTAMPADD (interval_type ,interval_value ,date_time )

TIMESTAMPADD adds the specified interval_value, which is of the interval_type data type, to the datetime provided and returns the resulting date-time.

Possible values for interval_type are listed in Table 9-3.

 SET var1=NOW( );  2005-07-31 16:08:18
 SET var2=TIMESTAMPADD(YEAR,100,NOW( ));  2105-07-31 16:08:18
 SET var3=TIMESTAMPADD(HOUR,24,NOW( ));  2005-08-01 16:08:18

 

9.3.27. TIMESTAMPDIFF

 interval_value =TIMESTAMPDIFF (interval_type ,date_time1 ,date_time2 )

TIMESTAMPDIFF returns the difference between two date-times, expressed in terms of the specified interval_type.

 SET var1=NOW( );  2005-07-31 16:12:30
 SET var2=TIMESTAMPDIFF(YEAR,NOW( ),"2006-07-31 18:00:00");  1
 SET var3=TIMESTAMPDIFF(HOUR,NOW( ),"2005-08-01 13:00:00");  20

 

9.3.28. WEEK

 number =WEEK (date_time [,start_of_week ])

WEEK returns the number of weeks since the start of the current year. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

 SET var1=NOW( );  2005-07-31 16:20:09
 SET var2=WEEK(NOW( ));  31

 

9.3.29. WEEKDAY

 number =WEEKDAY (date )

WEEKDAY returns the number for the current day of the week, with Monday returning a value of 0.

 SET var1=NOW( );  2005-07-31 16:22:05
 SET var2=DAYNAME(NOW( ));  Sunday
 SET var3=WEEKDAY(NOW( ));  6

 

9.3.30. YEAR

 number =YEAR (datetime )

YEAR returns the year portion of the datetime argument, which is specified in date-time format.

 SET var1=NOW( );  2005-07-31 16:27:12
 SET var2=YEAR(NOW( ));  2005

 

9.3.31. YEARWEEK

 YearAndWeek =YEARWEEK (datetime [,StartOfWeek ])

YEARWEEK returns the year and week of the year for the given date. Weeks are considered to start on Sunday unless you specify an alternative start day (1=Monday) in the second argument.

 SET var1=NOW( );  2005-07-31 16:30:24
 SET var2=DAYNAME(NOW( ));  Sunday
 SET var3=YEARWEEK(NOW( ));  200531
 SET var4=YEARWEEK(NOW( ),1);  200530

 

9.3.32. Other Date and Time Functions

Table 9-5 lists date and time functions not discussed in previous sections. Some of these are synonyms for functions we have discussed above, while others are rarely required in MySQL programming.

Table 9-5. Additional date-time functions

Function	Syntax	Description
ADDDATE	datetime=ADDDATE(date,interval_value, intervaltype)	Synonym for DATE_ADD.
CURDATE	datetime=CURDATE( )	Alias for NOW.
CURTIME	time=CURTIME( )	Current time.
DAYOFMONTH	day=DAYOFMONTH(datetime)	Day of the month.
FROM_DAYS	days=FROM_DAYS(datetime)	Number of days since the start of the current calendar.
HOUR	number=HOUR(datetime)	Hour of the day for the given date.
LAST_DAY	date=LAST_DAY(date)	Returns the last day of the month for the given date.
LOCALTIME	datetime=LOCALTIME( )	Synonym for NOW.
LOCALTIMESTAMP	datetime=LOCALTIMESTAMP( )	Synonym for NOW.
MICROSECOND	microseconds=MICROSECOND(datetime)	Microsecond portion of the provided time.
MINUTE	minute=MINUTE(datetime)	Minute part of the given time.
MONTH	month=MONTH(datetime)	Month part of the given time.
PERIOD_ADD	date=PERIOD_ADD(year_month, months)	Adds the specified number of months to the provided year_month value.
PERIOD_DIFF	date=PERIOD_DIFF( year_month_1,year_month_2)	Returns the number of months between the two year_month values provided.
QUARTER	quarter=QUARTER(datetime)	Returns the quarter of the given date.
SECOND	seconds=SECOND(datetime)	Returns the seconds portion of the provided datetime.
SUBDATE	date1=SUBDATE(date2, interval_value, interval_type)	Synonym for DATE_SUB.
SUBTIME	datetime1=SUBTIME(datetime2, time)	Subtracts the time from the datetime.
SYSDATE	datetime=SYSDATE( )	Synonym for NOW.
TO_DAYS	datetime=TO_DAYS(days)	Adds the days argument to the start of the standard calendar.
WEEKOFYEAR	week=WEEKOFYEAR(datetime)	Synonym for WEEK.