DATE_ADD()
Concepts covered: Date Math
DATE_ADD moves a date or a timestamp forward or back by a whole number of units. It takes 3 arguments in a fixed order: the unit as a quoted string, the amount as an integer, and the value to move. DATE_ADD('day', 14, check_out) is 14 days after check-out. A negative amount moves backward, so DATE_ADD('day', -7, CURRENT_DATE) is 2024-03-08, a week before today. The result keeps the type of the value you pass in: a DATE comes back a DATE and a TIMESTAMP comes back a TIMESTAMP, clock intact.
| Unit | Amount | Result from TIMESTAMP '2024-03-15 14:30:45' |
|---|---|---|
| 'second' | 30 | 2024-03-15 14:31:15 |
| 'minute' | 90 | 2024-03-15 16:00:45 |
| 'hour' | 10 | 2024-03-16 00:30:45 |
| 'day' | 20 | 2024-04-04 14:30:45 |
| 'week' | 2 | 2024-03-29 14:30:45 |
| 'month' | 1 | 2024-04-15 14:30:45 |
| 'year' | -1 | 2023-03-15 14:30:45 |
A DATE carries no clock, so it accepts only units of a day or longer, up to year. Ask it for hours and the engine refuses, reporting that hour is not a valid DATE field. When a day-typed column needs hour arithmetic, cast it to TIMESTAMP first, and the clock begins at midnight.
Deadlines from a date
Airbnb gives guests 14 days after checkout to write a review, so every booking's deadline is a single call on check_out. Play the animation: each date walks into the new column with 14 days added, and 3 of the 5 land in April though no row mentions April. The engine handles the month rollover, including the 31 days in March.
SELECT
booking_id,
check_out,
DATE_ADD('day', 14, check_out) AS review_deadline
FROM bookings| booking_id | listing_id | check_in | check_out | created_at |
|---|---|---|---|---|
| 1 | 101 | 2024-03-14 | 2024-03-17 | 2024-03-01 09:12:00 |
| 2 | 101 | 2024-03-15 | 2024-03-18 | 2024-03-14 00:00:00 |
| 3 | 102 | 2024-03-16 | 2024-03-20 | 2024-03-14 18:45:10 |
| 4 | 102 | 2024-03-20 | 2024-03-21 | 2024-03-15 08:05:30 |
| 5 | 103 | 2024-02-28 | 2024-03-02 | 2024-02-01 12:00:00 |
Windows that slide with the clock
The most common DATE_ADD in analytics never shows its result. It sits in a WHERE clause and defines a window that moves with the date: the last 7 days, the last 30 days, the last 24 hours. DATE_ADD('day', -7, CURRENT_DATE) evaluates to 2024-03-08, and >= against it keeps everything on or after that day. Because the cutoff is built from CURRENT_DATE, the same query run next week keeps next week's rows without an edit.
SELECT
booking_id,
created_at
FROM bookings
WHERE CAST(created_at AS DATE) >= DATE_ADD('day', -7, CURRENT_DATE)| booking_id | listing_id | check_in | check_out | created_at |
|---|---|---|---|---|
| 1 | 101 | 2024-03-14 | 2024-03-17 | 2024-03-01 09:12:00 |
| 2 | 101 | 2024-03-15 | 2024-03-18 | 2024-03-14 00:00:00 |
| 3 | 102 | 2024-03-16 | 2024-03-20 | 2024-03-14 18:45:10 |
| 4 | 102 | 2024-03-20 | 2024-03-21 | 2024-03-15 08:05:30 |
| 5 | 103 | 2024-02-28 | 2024-03-02 | 2024-02-01 12:00:00 |
| booking_id | created_at |
|---|---|
| 2 | 2024-03-14 00:00:00 |
| 3 | 2024-03-14 18:45:10 |
| 4 | 2024-03-15 08:05:30 |
Of the 5 bookings, numbers 2 through 4 were created on or after March 8. Booking 1, created March 1, misses the cutoff by a week, and booking 5 by over a month. Two cutoffs that sound alike behave differently. DATE_ADD('day', -1, CURRENT_DATE) is 2024-03-14, so >= keeps all of yesterday and all of today, up to 2 full calendar days. DATE_ADD('hour', -24, CURRENT_TIMESTAMP) is 2024-03-14 14:30:45, the start of the 24 hours ending now. A row stamped yesterday at 09:00 passes the first and fails the second.
- Calendar window
- DATE_ADD('day', -1, CURRENT_DATE)
- Cutoff: 2024-03-14
- Keeps all of yesterday and today
- Clock window
- DATE_ADD('hour', -24, CURRENT_TIMESTAMP)
- Cutoff: 2024-03-14 14:30:45
- Keeps the 24 hours ending now
Month ends
Adding months runs into a calendar fact: the target month may lack the day you started from. The engine keeps the result inside that month by clamping to its last day. January 31 plus 1 month is February 29 in 2024, and February 29 plus 1 year is February 28 in 2025. Predict all 4 rows of each column before you run the query.
SELECT
start_day,
DATE_ADD('month', 1, start_day) AS plus_one_month,
DATE_ADD('year', 1, start_day) AS plus_one_year
FROM month_ends| start_day |
|---|
| 2024-01-30 |
| 2024-01-31 |
| 2024-02-29 |
| 2024-03-31 |
| start_day | plus_one_month | plus_one_year |
|---|---|---|
| 2024-01-30 | 2024-03-01 | 2025-01-30 |
| 2024-01-31 | 2024-03-02 | 2025-01-31 |
| 2024-02-29 | 2024-03-29 | 2025-03-01 |
| 2024-03-31 | 2024-05-01 | 2025-03-31 |
The clamp means month arithmetic can lose information. Adding 1 month to January 30 gives February 29, the same day January 31 reaches; subtract the month again and both return to January 29 instead of where they began. Billing systems that charge on 'the same day next month' decide this case on purpose, because the engine's answer for a January 31 signup is February 29 in one year and February 28 in the next.
What does DATE_ADD('month', 1, DATE '2024-01-31') return?
- 2024-03-02, because the extra days spill 2 days past the end of February
- 2024-02-29, because the result is clamped to the last day of February in a leap year
- 2024-02-31, because DATE_ADD keeps the day number and leaves validation to you
- 2024-03-01, because February has no 31st so the engine rolls to the next month
A 30-day window is written in days. DATE_ADD('day', -30, CURRENT_DATE) is 2024-02-14, exactly 30 days back. A month back looks similar and drifts: DATE_ADD('month', -1, CURRENT_DATE) is 2024-02-15, which spans 29 days this March, while from April 15 the same call reaches March 15, 31 days back. Use the day unit when a report promises a fixed count of days, and pair a DATE column with CURRENT_DATE so both sides of the comparison are days.
About this lesson
This section is part of the Dates: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.