Syntax:

DATE_DIFF(date_expression_1, date_expression_2, date_part)

What does it do?

DATE_DIFF() compares two dates and returns the difference in date part between the two dates.

For example, if date_part = DAY, then DATE_DIFF() returns the number of DAYs between the two dates. If date_part = MONTH, then DATE_DIFF() returns the number of MONTHs between the two dates.

DATE_DIFF('2021-01-02', '2021-01-01', DAY) = 1

When is it Useful?

  • DATE_DIFF() is useful when you want to compare two dates, for example, when a package was shipped and when a package was delivered, or when a user registered and when they canceled.
  • DATE_DIFF() is also useful in the WHERE clause if you want to filter dates that happened X periods ago (eg. 5 days ago, 2 weeks ago, last month).

Example(s)

Example 1: Suppose you wanted to get the time between when a package was shipped and when a package was delivered:

table: orders
SELECT order_id
, DATE_DIFF(date_received, date_shipped, DAY) as shipping_time
FROM orders

Example 2: Using the same table above, suppose you wanted to get all orders where the shipping time was less than 10 days:

SELECT order_id
, amount
FROM orders
WHERE DATE_DIFF(date_received, date_shipped, DAY) < 10

Continue reading: https://towardsdatascience.com/top-5-date-functions-in-sql-that-you-should-know-for-data-science-interviews-d56dd9378785?source=rss—-7f60cf5620c9—4

Source: towardsdatascience.com