Redshift DATEDIFF in Snowflake, Databricks and BigQuery
Redshift DATEDIFF counts date part boundaries crossed, exactly like Snowflake DATEDIFF. Databricks datediff(unit, start, end) counts whole elapsed units instead, and BigQuery DATE_DIFF puts the end date first.
Redshift function: DATEDIFF(datepart, date_or_time1, date_or_time2) · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | DATEDIFF(date_or_time_part, expr1, expr2) | Same argument order and the same boundary counting. | Snowflake docs |
| Databricks | Same name | datediff(unit, start, end) | Synonym of timestampdiff (Databricks Runtime 10.4 LTS and above); counts whole elapsed units. | Databricks docs |
| BigQuery | Rewrite | DATE_DIFF(end_date, start_date, granularity) | End date first; counts boundaries like Redshift. | BigQuery docs |
Behavior differences to check
Databricks
- Month, quarter and year differences can be 1 lower than in Redshift, because Databricks only counts a month once the day and time reach the start value.
BigQuery
- Swapping the arguments silently changes the sign of the result.
Examples
Days from signup to first order
SELECT user_id, DATEDIFF(day, signup_at, first_order_at) AS days FROM users;SELECT user_id, DATEDIFF(day, signup_at, first_order_at) AS days FROM users;SELECT user_id, datediff(DAY, signup_at, first_order_at) AS days FROM users;SELECT user_id, DATE_DIFF(DATE(first_order_at), DATE(signup_at), DAY) AS days FROM users;