If your input date is in string or number, please convert into date or timestamp as per format of it.ĭate2|timestamp2:- “To date or timestamp” for which you want to calculate difference. It supports DAT E, TIME, TIMETZ, or TIMESTAMP. Please use unit of date based on your business requirement.ĭate1|timestamp1:- “ From date or timestamp” for which you want to calculate difference. Like if you apply DATEDIFF on 01-01-2008 & 01-02-2008 with month unit it will return 1. It returns the difference between two date in BIGINT datatype. Unit_of_difference:- The specific unit of the date or time value like year, month, or day, hour, minute, second, millisecond, or microsecond Syntax of DATEDIFF DATEDIFF(unit_of_difference, date1|timestamp1, date2|timestamp2) I stopped at seconds.DATEDIFF is used to get difference between the two date/timestamp in unit of year, month, or day, hour, minute, second, millisecond or microsecond. If you need to handle milliseconds or below it's easy to extend the code. Seconds is also the same, using minutes * 60. Minutes is the same calculation, but it uses hours * 60 since we already have it. We extract the hour as an integer (0-23) for each timestamp, subtract from each other, and multiply by the days expressed as hours. Hours = days * 24 + (DATE_PART('hour', end_t) - DATE_PART('hour', start_t)) Hours, minutes, and seconds are built out just like months and quarters. Instead of the start of the week we truncate to the beginning of the day to ensure the interval is an exact number of days. Since this is always divisible by 7, we now have our number of weeks.ĭays is computed the same way, without dividing by 7. Subtracting the days returns an interval, so we use DATE_PART to get an integer number of days. RETURN DATE_PART('day', (DATE_TRUNC('week', end_t) - DATE_TRUNC('week', start_t)) / 7) Note that the 'first day of the week' is not uniform across databases. That will give us an integer that's a multiple of 7. The way to count weeks is to truncate the start and end timestamps to the first day of the week, then subtract days. Weeks are special because they don't fit evenly into months, years or anything bigger. Weeks and days use a slightly different approach. Quarters is effectively the same as months (since DATE_PART gives us quarters as an integer from 1-4). Subtract the two and add in the year expressed in months to handle rollover.Ģ021/2 - 2020/11 would be (2 - 11) + 1 * 12 = 3 Months – just use DATE_PART to get the month as an integer (1-12). It computes weeks and below by truncating. It computes year, quarter, and month boundaries by subtracting integers. This isn't consistent across databases – Redshift uses Sunday, while in Snowflake it's configurable. Similarly DateDiff('week', '05-02-2021', '05-03-2021') is 1, because 5/02/21 is a Sunday and 5/03/21 is a Monday Note that Postgres uses ISO 8601 week numbering, so weeks will always start on Mondays. So what does counting date boundaries mean? It's best illustrated with an exampleĭATEDIFF('year', '12-31-2020', '01-01-2021') returns 1 because even though the two dates are a day apart, they've crossed the year boundary. It will always return an integer, so it's very useful for grouping date differences together. 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. 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. 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.
0 Comments
Leave a Reply. |