MySQL Time Functions

Time functions work along with MySQL Date functions and are used to manipulate time part from date data in SQL queries. MySQL Time functions include functions to add or subtract time values, find current time and find interval between two time values. Timestamp is an important value to create log information for different application activities.

Note: In all the examples discussed the time values are used with SELECT statements.  You can use these functions with table columns defined with datetime as datatype

Commonly used MySQL Time Functions

CURTIME(), CURRENT_TIME() or CURRENT_TIME             

These three MySQL Time Functions allow you to get the current time. No arguments are required when these functions are used in queries.

Example

SELECT CURTIME();
SELECT CURRENT_TIME();
SELECT CURRENT_TIME;
MySQL Time Functions Current time

ADDTIME()    

To get new time after or before a specific time you can use ADDTIME() function. Its syntax is

ADDTIME(time_expr1,time_expr2)

The two expressions are the string representing time in ‘yyyy-mm-dd hour:min:sec’ format. The second argument is added in the first time expression. The output is string representing date-time. If the second argument is negative, you will get a prior time and if it is positive you get a future time from the time expression given in first argument

Example

SELECT ADDTIME(CURRENT_TIME,'1:1:1:0:0:0');
SELECT ADDTIME(CURRENT_TIME,'-1:1:1:0:0:0');
Add Time

TIME()

There can be tasks where you will get date-time expression as input and you need only the time part of it. TIME function will be useful in such cases. Its syntax is

TIME(time_time_expression)   

Example

SELECT TIME(CURRENT_TIME);
SELECT TIME('2022-02-08 21:34:54.0020');
MySQL Time Functions

TIMEDIFF()    

This function is used when you need to get time interval between two time values. The syntax is

TIMEDIFF(time_expr1, time_expr2)

If first time expression is earlier than the second time interval, the value will be returned as negative time value else as positive time value.

Example

SELECT CURTIME(),TIMEDIFF(CURTIME(), '21:34:54.0020');
TimeDiff

MAKETIME() 

If you have input values as hours, minutes and seconds you can convert these values into time by using MAKETIME function.The syntax is

MAKETIME(hour_value,minute_value, second_value)

Example

SELECT MAKETIME(23,09,20);
Make Time

SUBTIME()

This function is similar to TIMEDIFF(). It returns the time difference between two time values. Its syntax is

SUBTIME (time_expr1, time_expr2)

The first expression is the string representing date-time in ‘yyyy-mm-dd hour:min:sec’ format. The second argument is the time value. The output is a string representing time. If the second time expression is afterwards than the first expression, you will get output as negative else it is positive.

Example

SELECT CURTIME(),SUBTIME(CURTIME(),MAKETIME(23,09,20));
SELECT CURTIME(),SUBTIME(CURTIME(),MAKETIME(13,09,20));
sub time

NOW(), CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIME, LOCALTIMESTAMP, LOCALTIMESTAMP()               

These all are no argument functions which return timestamp at the time the query is executed. These are commonly used functions to get the current time for storing as timestamp in log type  data.

Example

SELECT NOW();
SELECT CURRENT_TIMESTAMP ();
SELECT LOCALTIME ();
SELECT LOCALTIMESTAMP ();
time stamp values

TIMESTAMP()

This function returns the evaluated timestamp. Its syntax is

TIMESTAMP (time_expr)

TIMESTAMP (time_expr1, time_expr2)

When the function is called with one argument of datetime value it returns its datetime value. When the function is called with two arguments, the output is sum as datetime of time_expr2 with the date or datetime time_expr1.

Example

SELECT TIMESTAMP(CURTIME());
SELECT TIMESTAMP(CURTIME(),'2:2:2');
TimeStamp

TIMESTAMPADD()   

If you want to add an interval to a timestamp you will use TIMESTAMPADD function.  Its syntax is

TIMESTAMPADD(unit, value, date-timestamp)

Unit is the unit you want to add. Its value can be MICROSECOND/ SECOND/ MINUTE/ HOUR/ DAY/ WEEK/ MONTH/ QUARTER/ YEAR. Value is the integer value depending on the value of unit argument.

Example

SELECT CURTIME(),TIMESTAMPADD(HOUR, 5, CURTIME());
SELECT CURTIME(),TIMESTAMPADD(MICROSECOND, 5, CURTIME());
SELECT CURTIME(),TIMESTAMPADD(SECOND, 5, CURTIME());
SELECT CURTIME(),TIMESTAMPADD(DAY, 5, CURTIME());
Time Stamp Add

TIMESTAMPDIFF()   

If you want to subtract a timestamp from other timestamp you can use TIMESTAMPDIFF function.  Its syntax is

TIMESTAMPDIFF(unit, date-time expr1, date-time expr1)

Unit is the unit you want to get the difference of timestamps in. Its value can be MICROSECOND/ SECOND/ MINUTE/ HOUR/ DAY/ WEEK/ MONTH/ QUARTER/ YEAR. Second and third arguments are date-time stamps for which the interval is defined as Unit is to be found.

Example

SELECT CURTIME(),TIMESTAMPDIFF(WEEK, '2022-01-02', CURTIME());
SELECT CURTIME(),TIMESTAMPDIFF(DAY, '2022-01-02', CURTIME());
SELECT CURTIME(),TIMESTAMPDIFF(HOUR, '2022-01-02', CURTIME());
TimeStamp Diff