Updated March 4, 2023
Definition of Redshift DateDiff
Redshift DateDiff is used to find the difference between the specified dates. The usage of the datediff is similar in all the databases except for the parameters that we use in the syntax. The DateDiff function used in redshift has three arguments in it, we specify the dates for which we want to find the difference. The arguments are <date1> and <date2>. The third parameter is datepart. The datepart is nothing but the date or time value I.e., year, month, day, hour, minute, second, millisecond, or microsecond.
In this session we will see how the DateDiff works individually and in the table along with the examples: –
Syntax:
Now let us see the syntax for the DateDiff as below: –
SELECT DATEDIFF (‘datepart’, <date | time | timetz | timestamp>, <date | time | timetz | timestamp>);
Here above syntax is used to find the difference between the specified dates wised columns in a table.
SELECT DATEDIFF (‘datepart’, <COLUMN_NAME_1>, <COLUMN_NAME_2>) FROM TABLE_NAME;
Here above syntax is used to find the difference between specified column dates.
How does Redshift DateDiff works?
Now let us see how the DateDiff works in the Redshift.
select
DATEDIFF( 'qtr','2019-01-31 00:00:00', '2020-01-01 11:00:00'
) AS "DIFFERENCE";
Here in the above query, it returns the quarter difference between the dates. Here only the quater difference will be displayed. Screenshot is for the same.
select
DATEDIFF('day', '2020-01-31 ', '2020-01-01 11:00:00'
) AS "DATE DIFFERENCE";
/ * - - - DateDiff- - - * /
Here in the above query, it returns the day difference between the two dates. The starting datepart argument based on which the difference is calculated. It returns -30 even in the above DateDiff statement. Screenshot is for the same.
select
DATEDIFF('hour', '2020-01-31 12:00:00', '2020-01-01 '
) AS "DATE DIFFERENCE";
/ * - - - DateDiff- - - * /
Here in the above query, it returns the hour difference between the two mentioned date. The starting date argument datepart has been mentioned as the hour by which the datediff function will calculate the hour difference between those two dates. It returns -732 hours has the difference between the above two dates. Screenshot is for the same.
Using CURRENT_DATE function in the DATEDIFF () Function
Here now let us use the CURENT_DATE Function and try to get the Day difference.
select
DATEDIFF('day', current_date, '2020-07-01 11:00:00'
) AS "DATE DIFFERENCE";
/ * - - - DateDiff- - - * /
Here we have mentioned the in-built function in the statement. Here first argument is ‘Day’ which means datediff will calculate the day difference between the current date and the mentioned date and returns the output.
Screenshot is for the same:
Here the output of the DateDiff has been specified in the day difference between the dates.
Examples
Now let us see how the DateDiff works on the table columns. Let us create a table as below: –
CREATE TABLE DUE_CUSTOMERS
(
CUST_ID INT,
INITIATION_DATE DATETIME,
DUE_DATE datetime
);
The above table consists of the data about the customers whose due date is pending. This can be calculated as Current date – DUE_DATE.
Let us insert the below rows into the above table: –
INSERT INTO DUE_CUSTOMERS VALUES ( 1, '2020-01-01 12:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 2, '2019-03-01 11:00:00', '2019-05-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 3, '2019-08-01 13:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 4, '2019-06-01 18:00:00', '2020-01-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 5, '2020-02-01 16:00:00', '2020-04-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 6, '2020-03-01 10:00:00', '2020-07-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 7, '2019-09-01 19:00:00', '2020-02-01 12:00:00' );
INSERT INTO DUE_CUSTOMERS VALUES ( 8, '2020-01-01 15:00:00', '2020-08-01 12:00:00' );
Now let us perform the DateDiff function on the mentioned table: –
Here we are planning to get the customers who have breached the due_date. The values that are positive represent that the customer still has those many days to breach the due. If the values are in negative values, then it shows that the customer has already breached the due with the mentioned days. In our case, all the customers have breached the due date.
SELECT
*, datediff('day', DUE_DATE, CURRENT_DATE) AS BREACHED_COUNT_OF_DAYS
FROM
DUE_CUSTOMERS;
/ * - - - DateDiff- - - * /
Screenshot is for the same:
Here the values that are negative values means that customer has breached the due date. Now all the record has a positive value which means all the customer has breached the due date.
We can get the duration of due time that has been given to the customers.
SELECT
*, datediff( 'MONTH',DUE_DATE, INITIATION_DATE
) AS Duration
FROM
DUE_CUSTOMERS;
/ * - - - DateDiff- - - * /
The above query gets the duration of time given to the customer DUE_DATE – INITIATION_DATE.
A screenshot is for the same:
Conclusion
Things that need to be remembered by the above session are as below:
- Redshift DateDiff is used to find the difference between the specified dates. The usage of the datediff is similar in all the databases except for the parameters that we use in the syntax.
- The DateDiff function used in redshift has three arguments in it, we specify the dates for which we want to find the difference. The arguments are <date1> and <date2>. The third parameter is datepart. The datepart is nothing but the date or time value I.e., year, month, day, hour, minute, second, millisecond, or microsecond.
- Remember the syntax for the DateDiff as below: –
SELECT DATEDIFF (‘datepart’, <date | time | timetz | timestamp>, <date | time | timetz | timestamp>);
Here above syntax is used to find the difference between the specified dates wised columns in a table.
SELECT DATEDIFF (‘datepart’, <COLUMN_NAME_1>, <COLUMN_NAME_2>) FROM TABLE_NAME;
Here above syntax is used to find the difference between specified column dates.
Recommended Articles
This is a guide to Redshift DateDiff. Here we discuss the definition, How does Redshift DateDiff works, and examples with code implementation. You may also have a look at the following articles to learn more –
