DATEDIFF is used to get difference between the two date/timestamp in unit of year, month, or day, hour, minute, second, millisecond or microsecond in Redshift database..
Syntax of DATEDIFF
DATEDIFF(unit_of_difference, date_from/timestamp_from, date_to/timestamp_to)
Arguments of DATEDIFF
unit_of_difference:- The specific unit of the date or time value like year, month, or day, hour, minute, second, millisecond, or microsecond
It returns the difference between two date in BIGINT datatype. Like if you apply DATEDIFF on 01-01-2008 & 01-02-2008 with month unit it will return 1. Please use unit of date based on your business requirement.
date_from/timestamp_from:- “From date or timestamp” for which you want to calculate difference. It supports DATE, TIME, TIMETZ, or TIMESTAMP. If your input date is in string or number, please convert into date or timestamp as per format of it.
date_to/timestamp_to:- “To date or timestamp” for which you want to calculate difference.
DATEDIFF Redshift Function with DATE
Now let’s look at some examples how you can find different between 2 dates in year, month or day.
The following Query gives the difference between 2008-01-01 & 2010-01-01 dates in number of years which gives 2 as output as year difference shown in screen-shot.
SELECT DATEDIFF(year, CAST('2008-01-01' as DATE), CAST('2010-01-01' as DATE)) as DATEDIFF;
The following example finds the difference between 2008-01-01 & 2010-01-01 dates in number of months which gives 24 as output as month difference shown in screen-shot.
SELECT DATEDIFF(month, CAST('2008-01-01' as DATE), CAST('2010-01-01' as DATE)) as DATEDIFF;
The following Query gives the difference between 2008-01-01 & 2010-01-01 dates in number of days which gives 731 as output as day difference shown in screen-shot.
SELECT DATEDIFF(day, CAST('2008-01-01' as DATE), CAST('2010-01-01' as DATE)) as DATEDIFF;
DATEDIFF Redshift Function with TIMESTAMP
Now let’s look at some examples how you can find different between 2 timestamps in hour, minute, second, millisecond.
The following example finds the difference between 2008-01-01 01:00:00 & 2008-01-01 05:05:00 timestamps in number of hours which gives 4 as output as hour difference shown in screen-shot.
SELECT DATEDIFF(hour, CAST('2008-01-01 01:00:00' as TIMESTAMP), CAST('2008-01-01 05:05:00' as TIMESTAMP)) as DATEDIFF;
The following Query gives the difference between 2008-01-01 01:00:00 & 2008-01-01 05:05:00 timestamps in number of minutes which gives 245 as output as minute difference shown in screen-shot.
SELECT DATEDIFF(minute, CAST('2008-01-01 01:00:00' as TIMESTAMP), CAST('2008-01-01 05:05:00' as TIMESTAMP)) as DATEDIFF;
The following example finds the difference between 2008-01-01 01:00:00 & 2008-01-01 05:05:00 timestamps in number of seconds which gives 14,700 as output as hour difference shown in screen-shot.
SELECT DATEDIFF(hour, CAST('2008-01-01 01:00:00' as TIMESTAMP), CAST('2008-01-01 05:05:00' as TIMESTAMP)) as DATEDIFF;
The following Query gives the difference between 2008-01-01 01:00:00 & 2008-01-01 05:05:00 timestamps in number of milliseconds which gives 14,700,000 as output as millisecond difference shown in screen-shot.
SELECT DATEDIFF(millisecond, CAST('2008-01-01 01:00:00' as TIMESTAMP), CAST('2008-01-01 05:05:00' as TIMESTAMP)) as DATEDIFF;
DATEDIFF Redshift Function with TIME
You can also get hour/minute/second from TIME datatype using DATEDIFF.
The following example finds the difference between 03:00:00 & 13:05:00 time in number of hour which gives 10 as output as hour difference shown in screen-shot.
SELECT DATEDIFF(hour ,CAST('03:00:00' as TIME),CAST('13:05:00 ' as TIME)) as DATEDIFF;
Same as above example you can use minute, second, millisecond, or microsecond as time difference unit.
DATEDIFF Redshift Function with TIMETZ
DATEDIFF can be used to get difference between TIMETZ datatype columns as well.
Following Query finds the differences between PST & IST time-zone in number of hours.
SELECT DATEDIFF(hour ,timetz '20:00:00 PST',timetz '20:00:00 IST') as DATEDIFF;
Please don’t forget to leave your feedback in below comments section!!