There are several approaches to this.
Using the ‘-‘ operator
Syntax: SELECT 'date1'::date - 'date2'::date
This approach usually returns the difference in days. There are some scenarios where sometimes the difference is needed in months, or years, or seconds even. With that we use the DATEDIFF function.
Using the DATEDIFF function
Syntax: DATEDIFF(interval, date1, date2)
The interval parameter can be:
- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear = Day of the year
- day, dy, y = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
Redshift also extends this with the DATE_PART which refers to the specific part of the date or time that the function operates on. With it you can also get the millennium, epoch, century.