![datediff redshift datediff redshift](https://i.stack.imgur.com/98nMt.jpg)
It will always return an integer, so it's very useful for grouping date differences together.
![datediff redshift datediff redshift](https://programmaticponderings.files.wordpress.com/2020/03/latency-1.png)
This function take a time unit and two dates, and counts the number of date boundaries crossed between them. This gist creates a function in Postgres that implements the DATEDIFF function found in Snowflake, BigQuery, and Redshift.
Datediff redshift code#
I'll jump straight to the code for those who like to see the answer first, and further down explain how it works Why is this even a function? Why not just use date_part? Because time rolls over: the last month of the year is 12 and the first is 1, so naively using date part would give us -11. A great blog by sqlines suggests an implementation, but I found it didn't quite match the functionality of most data warehouses. If you want to use Postgres as a data warehouse you'll probably want it. For operational data it's probably not often used, but if you do analytical queries it can be pretty helpful. Unfortunately Postgres simply doesn't have it. This function can be used to bucket times together, like when doing a cohort analysis.
![datediff redshift datediff redshift](https://s3.cn-north-1.amazonaws.com.cn/awschinablog/amazon-aws-redshift-modify-methods11.jpg)
Data warehouses like Redshift and Snowflake have a super useful DATEDIFF function – given two timestamps and a date part (hour, year, week, etc) it'll return how far apart they are.