How to Add Time to a Datetime Value in MySQL

Problem:

You’d like to add a certain amount of time to a datetime value in a MySQL database.

Example:

Our database has a table named flight_schedule with data in the columns flight, aircraft, and arrival_datetime.

For each flight, let’s get the flight code, aircraft code, and a new arrival date and time. To calculate the new_arrival_datetime, we’ll add 2 hours and 10 minutes to the current time for each arrival – that’s how much these flights have been delayed.

Solution:

We’ll use the ADDTIME() function. Here’s the query you would write:

SELECT flight,
       aircraft,
       ADDTIME(arrival_datetime, ‘2:10’) 
         AS new_arrival_datetime
FROM flight_schedule;

Here’s the result of the query:

Discussion:

Use the ADDTIME() function if you want to select a new datetime by adding a given time to a datetime/timestamp/time value.

This function takes two arguments. The first argument is the datetime/time to which we’re adding time; this can be an expression that returns a time/datetime/timestamp value or the name of a time/datetime/timestamp column. In our example, we use the arrival_datetime column, which is of the datetime data type.

The second argument is a string containing the amount of time to add to the first argument (in our example, ‘2:10’, or 2 hours and 10 minutes).

You can also add fractional seconds and even days to a datetime/time value. The query below adds 3 days, 1 hour, 1 minute, 1 second and 111 fractional seconds to a date and time:

SELECT ADDTIME('2019-02-05 10:12:11', '3 1:1:1.111'); 

It returns:

2019-02-08 11:13:12.111000

ADDTIME() returns a string with the new time. In our example, the new arrival time for flight ‘EK10’ is ‘2019-04-20 17:25:00’ – two hours and ten minutes after its original datetime of

‘2019-04-20 15:15:00’.