MySQL ADDTIME() function

MySQL ADDTIME() function adds the specified time intervals to the given date and time. It returns the date or DateTime value after adding the time interval.

Syntax

The MySQL ADDTIME() function syntax is:

ADDTIME(expr1, expr2)

Parameter

ADDTIME() function accepts two parameters.

• expr1: The given datetime or time that we want to modify.
• expr2: The time interval which we want to add to a given datetime. It can be both positive and negative.

MySQL ADDTIME() Function Example

Let’s look at some examples of the ADDTIME() function in MySQL. Learning MySQL ADDTIME function with examples, help in understanding the concept better.

Example 1

In this example, we are adding 15 seconds to the specified time using the ADDTIME function.

SELECT ADDTIME("11:34:21", "15") 
AS Updated_time ;

Output :

Example 2

In this example, we are adding 10 minutes to the specified time using the ADDTIME function.

SELECT ADDTIME("10:54:21", "00:10:00") 
AS Updated_time ;

Output :

Example 3

In this example, we are adding 12 hours with the specified datetime using ADDTIME Function.

SELECT ADDTIME("2009-02-20 18:04:22.333444", "12:00:00") 
AS Updated_time ;

Output :

Using ADDTIME function on Table Column Values

In this example, we will use the ADDTIME function on the values of columns.

First, let’s create a table named ScheduleDetails

CREATE TABLE ScheduleDetails(
TrainId INT NOT NULL,
StationName VARCHAR(20) NOT NULL,
TrainName VARCHAR(20) NOT NULL,
ScheduledlArrivalTime TIME NOT NULL,
PRIMARY KEY(TrainId )
);

Now inserting values in the ScheduleDetails table. We will use ADDTIME function, which will denote delay in arrival timing. The value in the ExpectedArrivalTime column will be the value given by the ADDTIME function.

INSERT INTO  
ScheduleDetails (TrainId, StationName, TrainName, ScheduledlArrivalTime )
VALUES
(12345, 'NJP', 'Saraighat Express', "17:04:22");

Now, checking the ScheduleDetails table :

SELECT *, ADDTIME(ScheduledlArrivalTime, "00:10:00") 
AS ExpectedArrivalTime FROM ScheduleDetails;

Output :

Important Points About MySQL ADDTIME() Function

• The ADDTIME() function adds a time interval to a time or datetime value and returns the resulting time or datetime value.
• The ADDTIME() function is available in various versions of MySQL, including MySQL 5.7, 5.6, 5.5, 5.1, 5.0, and 4.1.1
• You can subtract time intervals by using negative values.
• If either expr1 or expr2 evaluates to NULL, the function returns NULL.
• While it can work with time and datetime values, ADDTIME() primarily focuses on modifying datetime values.

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql