Luckily, there’s a handy DATEDIFF function that can do that for you. “What is the average number of days to conversion?”īusiness users will have these questions, data people will have to answer these questions, and the only way to solve them is by calculating the time between two different dates. Specifically, this means you could successfully run the same code across multiple databases without having to worry about the finicky differences in syntax.“How long has it been since this customer last ordered with us?” Using the DATEDIFF macro, you can calculate the difference between two dates without having to worry about finicky syntax. With dbt v1.2, adapters now support cross-database macros to help you write certain functions, like DATE_TRUNC and DATEDIFF, without having to memorize sticky function syntax. Luckily, dbt-core has your back! dbt Core is the open source dbt product that helps data folks write their data transformations following software engineering best practices. What happens when you switch to a different one for a new job or a new data stack? Remembering if there’s an underscore in the function name or which argument the is passed in as is… no fun and leads to the inevitable, countless “datediff in bigquery” Google searches. You may be able to memorize the syntax for the DATEDIFF function for the primary data warehouse you use. BigQuery also supports a separate DATE_DIFF function that will return the difference between two date types, unlike the DATETIME_DIFF that only supports the datetime type.Ī hero in the shadows: The DATEDIFF dbt macro! The DATETIME_DIFF arguments are datetimes, not dates Snowflake, Redshift, and Databricks’ DATEDIFF functions support multiple date types such as dates and timestamps.This is on par with Google BigQuery’s preference to have underscores in function names. Google BigQuery also calls the function DATETIME_DIFF with an additional underscore separating the function name.Unlike in Snowflake, Amazon Redshift, and Databricks where the is passed as the first argument, the is passed in as the last argument in Google BigQuery.The syntax for using the DATEDIFF function in Google BigQuery looks like the following: The function will always return the difference between two dates in days. The syntax for using the DATEDIFF function in Snowflake and Amazon Redshift, and Databricks looks like the following:Ī note on Databricks: Databricks additionally supports a separate DATEDIFF function that takes only two arguments: a start date and an end date. SQL DATEDIFF function syntax in Snowflake, Databricks, and Redshift There may be some minor differences between the argument order and function name for DATEDIFF across data warehouses, but the functionality very much remains the same.īelow, we’ll outline some of the slight differences in the implementation between some data warehouses. Most, if not all, modern cloud data warehouses support some type of the DATEDIFF function. The DATEDIFF function can be used in SELECT statements and WHERE clauses. The date part: This is the days/months/weeks/years (unit) of the difference calculated.How to use the DATEDIFF function įor the DATEDIFF function, there are three elements, or arguments, passed in: This page will go over how to use the DATEDIFF function across different data warehouses and how to write more standardized DATEDIFF functions using a dbt macro (or successfully find your socks as a pair in one go). DATEDIFF is this pair of socks-you’ll inevitably find yourself Googling the syntax almost every time you use it, but you can’t go through your day without using it. But for some reason, the matching sock requires a little digging in the drawer. It’s a simple and widely used function that you’ll find yourself using more often than you expect.ĭATEDIFF is a little bit like your favorite pair of socks you’ll usually find the first one easily and feel like the day is going to be great. days, weeks, years) between a start date/time and an end date/time. The DATEDIFF function will return the difference in specified units (ex. “How long has it been since this customer last ordered with us?”
0 Comments
Leave a Reply. |