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 1: Suppose you wanted to get the time between when a package was shipped and when a package was delivered:
, DATE_DIFF(date_received, date_shipped, DAY) as shipping_time
Example 2: Using the same table above, suppose you wanted to get all orders where the shipping time was less than 10 days:
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