ADDTIME Function - SQL
Overview
The ADDTIME function in SQL adds a specified time interval to a time or datetime expression. It is useful for time arithmetic, such as calculating future or past times.
Example:
SELECT
ADDTIME('2000-01-01 00:00:00', '02:15:00')
Syntax:
SELECT
ADDTIME(time, interval)
time is the starting time or datetime to which you want to add the interval.
interval is the time interval to add, expressed as a string in the format 'HH:MM:SS'.
Sample Data:
| first_name | birthday |
|---|---|
| Frank | 1994-03-15 00:00:00 |
| Jane | 1980-06-14 00:00:00 |
| Ashley | 1975-12-05 00:00:00 |
| Glenn | 1988-03-23 00:00:00 |
| Kelly | 1979-10-11 00:00:00 |
| Richard | 1985-08-30 00:00:00 |
| George | 1992-02-18 00:00:00 |
| Kyle | 1983-04-07 00:00:00 |
| James | 1978-09-27 00:00:00 |
| Gustavo | 1987-11-21 00:00:00 |
interval: All available inputs
YEAR: Represents the year component of a date.
MONTH: Represents the month component of a date.
DAY: Represents the day component of a date.
HOUR: Represents the hour component of a time.
MINUTE: Represents the minute component of a time.
SECOND: Represents the second component of a time.
MICROSECOND: Represents the microsecond component of a time.
QUARTER: Represents the quarter component of a date.
WEEK: Represents the week component of a date.
DAY_MICROSECOND: Represents a combination of days and microseconds.
DAY_SECOND: Represents a combination of days and seconds.
DAY_MINUTE: Represents a combination of days and minutes.
DAY_HOUR: Represents a combination of days and hours.
YEAR_MONTH: Represents a combination of years and months.
HOUR_MICROSECOND: Represents a combination of hours and microseconds.
HOUR_SECOND: Represents a combination of hours and seconds.
HOUR_MINUTE: Represents a combination of hours and minutes.
MINUTE_MICROSECOND: Represents a combination of minutes and microseconds.
MINUTE_SECOND: Represents a combination of minutes and seconds.
SECOND_MICROSECOND: Represents a combination of seconds and microseconds.
Example: Hard-coded value
In this example, we are adding 2 hours and 15 minutes to the ‘2000-01-01 00:00:00’ datetime for a final output of ‘2000-01-01 02:15:00.’
Example: Query without the WHERE Statement
In this example, we are adding 2 hours and 15 minutes to the birthday column in the company.employees table. You could see the side by side differences in the output.
Example: Query with the WHERE Statement
In this example, we are adding 2 hours and 15 minutes to the birthday column where the first_name column equals ‘Kelly.’ You could see the side by side differences in the output.
