Amazon Redshift DATEDIFF and DATEADD Functions Simplified 101

Redshift has various types of built-in functions to perform operations on data for example:

• String functions to perform operations on strings
• Numeric functions to perform operations on numeric data
• Date functions to manipulate date data types in Redshift

In this post, we will look at Date functions in Redshift — specifically the Redshift DATEDIFF and DATEADD functions with syntax and practical use cases of using these functions. Date functions work with date values. They either take a date as an input or show a date as an output.

There are other functions in Redshift relating to DateTime, the reason being that, unlike other data types, with DateTime you have many different parts like Day, Week, Month, Quarter, Year, Hour, Minutes, Seconds, etc. and this makes it a little bit more complex compared to other data types.

Table of Contents

• Prerequisites
• Introduction to Amazon Redshift
  • Key Features of Amazon Redshift
• Redshift DATEADD Function
  • Redshift DATEADD Function: Syntax
  • Redshift DATEADD Function: Example
  • Redshift DATEADD Function: Use-case
• Redshift DATEDIFF Function
  • Redshift DATEDIFF Function: Syntax
  • Redshift DATEDIFF Function: Example
  • Redshift DATEDIFF Function: Use-case
• Conclusion

Prerequisites

• A successfully set-up Redshift Data Warehouse.
• Working knowledge of querying databases.

Introduction to Amazon Redshift

Amazon Redshift is a fully managed Cloud Data Warehouse service with petabyte-scale storage. It is a key component of the Amazon Web Services (AWS) cloud platform. Amazon Redshift allows you to store and analyze all of your data in order to gain actionable business insights.

Furthermore, in the past, sales predictions and other forecasts had to be done manually. And now you can focus on other things while Amazon Redshift does the majority of the data processing for you. It also enables you to examine your business data using the most up-to-date predictive analytics. You’ll be able to make better decisions that will help your business grow and prosper.

Key Features of Amazon Redshift

Amazon Redshift is one of the most popular Cloud Data Warehouses. Some of the key features of Amazon Redshift include:

• Massively Parallel Processing (MPP): Massive Parallel Processing (MPP) is a distributed design paradigm that divides and conquers huge data tasks across several processors. A large processing job is broken down into smaller jobs and distributed among a cluster of Compute Nodes. The below image depicts the architecture of Amazon Redshift.

• Fault Tolerance: Data accessibility and reliability are crucial for any Database or Data Warehouse user. When a node or cluster goes down, Amazon Redshift replicates all of the data to other nodes or clusters that are still operational.
• Amazon Redshift ML (Machine Learning): Amazon Redshift ML is a feature that allows Database Engineers and Data Analysts to quickly build, train, and deploy Amazon SageMaker models using SQL.

To know more about Amazon Redshift, visit this link.

Redshift DATEADD Function

The DATEADD function returns a new DateTime value by adding an interval to the specified date part of the specified date or timestamp. It returns the date after a certain time or date interval has been added. The DATEADD function is typically used when you have a date in mind and you’re trying to project the future or even trying to go back in the past. Let’s look at the syntax, examples, and use cases of the Redshift DATEADD function:

• Redshift DATEADD Function: Syntax
• Redshift DATEADD Function: Example
• Redshift DATEADD Function: Use-case

1) Redshift DATEADD Function: Syntax

The syntax of the Redshift DateAdd function is as follows:

DATEADD (datepart, interval, {date|timestamp})

2) Redshift DATEADD Function: Example

Here, you will find examples of how the DateAdd function can be used to add days, months, or weeks to a literal date value

Adding 5 days to a literal date value:

select dateadd(day, 5, '2020-02-25') as date_add;

Output:

date_add
---------------------
2020-03-01 00:00:00.000
(1 row)

Adding 3 weeks to a literal date value:

select dateadd(week, 3, '2020-02-25') as date_add;

Output:

date_add
---------------------
2020-03-17 00:00:00.000
(1 row)

Adding 10 months to a literal date value:

select dateadd(month, 18, '2020-02-25') as date_add;

Output:

date_add
---------------------
2020-12-25 00:00:00
(1 row)

Adding 6 quarters to a literal date value:

select dateadd(quarter, 6, '2020-02-25') as date_add;

Output:

date_add
---------------------
2021-08-25 00:00:00.000
(1 row)

Adding 1 year to a literal date value:

Select dateadd(year, 1, '2015-08-15') as date_add;

Output:

date_add
---------------------
2016-08-15 00:00:00.000
(1 row)

3) Redshift DATEADD Function: Use-case

This section is aimed at solving a simple problem of adding 20 days to each date in January that exists in the DATE table:

Step 1: Add 20 years to the date in the BirthDate column, then return the date:

select dateadd(day, 20, caldate) as janplus20
from date
where month='JAN'
order by dateid;

Output:

janlus20
---------------------
2019-10-01 00:00:00
2019-10-02 00:00:00
2019-10-03 00:00:00
...
(30 rows)

Step 2: Select LastName, BirthDate, dateadd(year, 10, BirthDate) as date_add from students

date_add
---------------------
LastName	BirthDate	DateAdd
Manolo	1988-08-03	2008-08-03
Fischer	1972-05-29	1992-05-29
Loraine	1990-10-30	2010-10-30
Tuchel	1978-09-30	1998-09-30
Ramsey	1991-12-14	2011-12-14
Kante 	1985-06-10	2005-06-10
Trippier    1982-01-14	2002-01-14
Noah  	1969-01-05	1989-01-05
Dier  	1969-07-02	2012-11-17
Victor	1984-06-27	2004-06-27

(11 rows)

Redshift DATEDIFF Function

The Redshift DATEDIFF function is used to calculate the difference between two date values based on the interval specified using either Years, Months, Days, Weeks, Hours, Minutes, Seconds, Milliseconds, et al. Let’s look at the syntax, examples, and use cases of Redshift DATEDIFF function:

• Redshift DATEDIFF Function: Syntax
• Redshift DATEDIFF Function: Example
• Redshift DATEDIFF Function: Use-case

1) Redshift DATEDIFF Function: Syntax

The syntax of the Redshift DATEDIFF function is as follows

DATEDIFF (datepart, {date|time}, {date|timestamp})

2) Redshift DATEDIFF Function: Example

Find the difference in the number of weeks between two date values.

select datediff(week, '2019-01-01', '2019-12-31');

Output; 52

Find the difference in the number of months between two date values.

select datediff(month, '2019-01-01', '2019-12-31');

Output; 12

Find the difference in the number of weeks between two date values.

select datediff(quarter, '2019-01-01', '2019-12-31');

Output; 4

3) Redshift DATEDIFF Function: Use-case

Suppose we want to find the age of a person who was born ‘2000-01-01’ and we want to find his/her age in ‘2020-05-01’

select datediff(year, '2000-01-01', '2020-05-01');

Output; 20

Suppose that we have an online store selling physical products and we want to calculate the shipping time for each order with the shipping method. This example joins the ORDERS and SHIPPING tables to calculate the delivery time for packages based on the shipping method.

select shipping_module,
datediff(day, shipment_date, delivery_date) as shipping_time
from orders, shipment where orders.shipment_id = shipment.shipment_id
and orders.shipment_id between 5000 and 5010
order by shipping_time desc, shipping_module desc;

Output:

shipping_module | shipping_time
----------------|-----+--------
 USPS           |      10
 UPS            |       1
 FedEx          |       5
 DHL            |       3
 China Post     |      15
 ePacket        |      20
 DHL            |       3
 UPS            |       1
 DHL            |       3
 FedEx          |       5
(10 rows)

This example calculates the average delivery time in hours that customers waited for their packages after ordering.

Conclusion

This article gave you a brief introduction to Amazon Redshift along with its key features. It also provided in-depth knowledge about Amazon Redshift DATEDIFF and DATEADD functions. You got to know about the syntax, example, and use-case of Amazon Redshift DATEDIFF and DATEADD functions. You may now utilize these Amazon Redshift functions with ease. Utilize Redshift’s date functions like DATE_SUB, DATEADD, and DATEDIFF to efficiently manipulate date values in your queries.

Visit our Website to Explore Hevo

Businesses can use automated platforms like Hevo Data to set the integration and handle the ETL process. It helps you directly transfer data from Data Warehouses like Amazon Redshift, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you with a hassle-free experience.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Now that you understand the Redshift DATEDIFF and DATEADD functions, we are keen to know how you have been able to use these in your queries. Let us know in the comments below?