DATE_DIFF()

Concepts covered: Date Differences

DATE_DIFF measures the distance between 2 moments in the unit you name. After the unit come the start and then the end, and the answer is end minus start: positive when the end is later, negative when the arguments arrive swapped. DATE_DIFF('day', DATE '2024-03-15', DATE '2024-03-18') is 3. Reverse the 2 dates and it is -3. The result is always a whole number of units, so a span of 5 hours and 30 minutes reports as 5 when asked in hours and as 330 when asked in minutes; the half hour is dropped, never rounded up.

Nights, from dates

Home-stay nights are the day difference between check-in and check-out. A guest who arrives March 15 and leaves March 18 sleeps there 3 nights, and DATE_DIFF('day', check_in, check_out) returns 3. Counting the calendar days the guest touched would give 4, the wrong number to bill. The animation computes the column for every booking; booking 5 crosses from February into March and still comes out at 3, because 2024 gives February a 29th day.

SELECT
  booking_id,
  check_in,
  check_out,
  DATE_DIFF('day', check_in, check_out) AS nights
FROM bookings

Nights, from timestamps

Now the stays from the opening. Suppose a stays table records arrival and departure as TIMESTAMPs, with 15:00 check-in and 11:00 check-out as house rules. Taken straight on those TIMESTAMPs, a day difference can report a night short, which is how the payout query arrived at 2; whenever the question is nights, cast both ends to DATE. Leave them as TIMESTAMPs when the question is elapsed time, and ask for hours. Switch between the 2 tabs and compare the last column row by row.

Nights

Cast both ends to DATE first and the stays report 3, 1 and 7 nights, the calendar answer a host bills.

SELECT
  stay_id,
  checkin_at,
  checkout_at,
  DATE_DIFF('day', CAST(checkin_at AS DATE), CAST(checkout_at AS DATE)) AS nights
FROM stays

Hours

Leave the TIMESTAMPs as they are and ask for hours to get elapsed time: 68, 17 and 168, with the extra 30 minutes of stay 2 dropped.

SELECT
  stay_id,
  checkin_at,
  checkout_at,
  DATE_DIFF('hour', checkin_at, checkout_at) AS hours_stayed
FROM stays

When the unit is day and the question is about calendar days, measure between DATEs. Measure between TIMESTAMPs when you want elapsed time at a finer grain than days, such as how long a host took to reply to a message.

Lead time and totals

DATE_DIFF returns a number, so it works anywhere a number does. You can sum or average it, and filter on it in WHERE or carry it across a join. Booking lead time is the day gap between the moment a booking was created and the day the stay starts, a calendar question, so created_at is cast to DATE first. Predict the 3 rows before running: the nights booked per listing and the average lead in days.

SELECT
  l.title,
  SUM(DATE_DIFF('day', b.check_in, b.check_out)) AS nights_booked,
  AVG(DATE_DIFF('day', CAST(b.created_at AS DATE), b.check_in)) AS avg_lead_days
FROM bookings AS b
INNER JOIN listings AS l
  ON b.listing_id = l.listing_id
GROUP BY l.title

The Lisbon loft has 2 bookings of 3 nights each. One was made 13 days ahead and the other 1 day ahead, so its lead averages 7.0. The Tahoe cabin sums 4 and 1 nights, with leads of 2 and 5 days averaging 3.5. The Osaka studio's single booking was made 27 days out. A pricing team watches numbers like these: a listing whose average lead keeps shrinking is filling at the last minute.

A guest message arrived at 2024-03-15 03:40:00 and the host replied at 2024-03-15 09:10:00. What does DATE_DIFF('hour', message_at, reply_at) return?

• 6, because the engine rounds 5 hours and 30 minutes to the nearest whole hour
• 330, because hour differences are counted in minutes and scaled afterwards
• 5, because the span is 5 hours and 30 minutes and the result keeps only whole hours
• -5, because the later moment was given as the second argument

Averages carry the sign of every difference inside them. Over the 5 bookings, DATE_DIFF('day', check_in, check_out) returns one night count per booking: 3, 3, 4, 1, 3. Swap the 2 columns and each value turns negative, so the Lisbon loft averages -3.0 nights instead of 3.0 and the Tahoe cabin -2.5, a report no host could read. Keep the earlier date second and the later date third inside the aggregate, as the SUM above does.

Build a query on bookings that returns each listing_id with the average number of nights its stays ran, as a column named avg_nights. One tile does not belong.

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.