Amazon Redshift DATEDIFF Function: Cheat Sheet & FAQ

What is the Amazon Redshift DATEDIFF function?

The Amazon Redshift DATEDIFF function returns the difference between the date parts of two date or time expressions.

It is constructed with this syntax:

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

The DATEDIFF function is most commonly used in analytic queries for filtering, cohort analyses, and grouping by tenure, but it’s incredibly versatile and has a wide array of use cases.

Arguments

datepart

The datepart argument accepts the date or time value that the function operates on. Some examples are seconds, days, weeks, years. See Amazon’s documentation for a complete list of dateparts.

The DATEDIFF function determines the number of date part boundaries crossed between two date/time expressions. This is perhaps unintuitive; for example, if we pass DATEDIFF(year, ‘2021-12-31’, ‘2022-01-01’) the function will return one year, even though there’s only a single day difference. It’s important to understand this behavior for a function that is often used in reporting.

date|time|timetz|timestamp

The other argument accepted is a column or expression that converts to a DATE, TIME, TIMETZ, or TIMESTAMP. The DATEDIFF function subtracts the second argument from the first, hence if the second argument occurs after the first, then the result is positive. If the second argument precedes the first, the result is negative.

Return Type

BIGINT - The DATEDIFF function returns a Redshift type BIGINT value.

Examples

Examples With DATE Column

This query calculates the number of weeks between the first date of 2021 and 2022:

SELECT
DATEDIFF(week, ‘2021-01-01’, ‘2022-01-01’) as numweeks;
numweeks
----------
52
(1 row)

We can do the same for quarters:

SELECT
DATEDIFF(qtr, ‘2021-01-01’, ‘2022-01-01’) as numquarters;
numquarters
----------
4
(1 row)

Examples With a TIME Column

Our example table TIME_TEST has a column TIME_VAL of type TIME with three values:

SELECT
time_val
FROM time_test
time_val
---------------------
20:00:00
00:00:00.5550
00:58:00

We can find the difference, in hours, between each value in our table and the time ‘15:24:45’:

SELECT
DATEDIFF(hour, time_val, time '15:24:45')
FROM time_test;
date_diff
-----------
-5
15
15

Examples With a TIMETZ Column

Consider a similar table TIMETZ_TEST with values of type TIMETZ

SELECT
  timetz_val 
FROM timetz_test;
            
We can calculate the difference from ‘20:00:00 PST’ using the following query

timetz_val
------------------
04:00:00+00
00:00:00.5550+00
05:58:00+00

SELECT 
  DATEDIFF(hours, timetz '20:00:00 PST', timetz_val) as numhours 
FROM timetz_test;

numhours 
---------- 
0
-4
1

Related Functions

Now that we’ve introduced the Redshift DATEDIFF function, we recommend exploring a few other, related Redshift date/time functions:

• DATE_PART: extracts date part values from an expression. Equivalent to the PGDATE_PART function.
• DATEADD: Increments (adds or subtracts) DATE, TIME, TIMETZ, or TIMESTAMP values by a specified interval.
• DATE_TRUNC: Truncates a timestamp expression or literal based on the supplied datepart.

Want to learn more about Redshift? Check out this article:

Redshift FAQ

How do I get the system date in Redshift?

Amazon Redshift has a CURRENT_DATE function, which returns the start date for the current transaction (UTC) in the format YYYY-MM-DD. An example:

SELECT CURRENT_DATE;   
date
------------
2022-07-22
(1 row)

How do I combine two columns in Redshift?

The Amazon Redshift CONCAT function can be used to combine columns. You can also use the concatenate operator (| |) to join expressions. The syntax for CONCAT is:

CONCAT ( expression1, expression2 )

An example:

SELECT
  CONCAT('December 25, ', '2022');

concat
-------------------
December 25, 2022
(1 row)

The following is equivalent:

SELECT
  'December 25, ' || '2022';

concat
-------------------
December 25, 2022
(1 row)

How do you combine date and time in Redshift?

To combine date and time in Redshift, use the datetime concatenation operator (+). The order of the arguments is reversible, but the syntax is:

date + time

An example of the function:

SELECT
 DATE '2022-07-22' + ‘12:00:00’ AS ts
            
ts
---------------------
2022-07-22 12:00:00

How do I change the date format in Redshift?

The Redshift function TO_CHAR converts a timestamp or date to a character-string data format. It accepts the syntax:

TO_CHAR (timestamp_expression | numeric_expression , 'format')

Where the format argument is constructed using Redshift Datetime format strings. An example of TO_CHAR:

SELECT 
  TO_CHAR(TIMESTAMP '2009-12-31 23:15:59', 'MONTH-DY-DD-YYYY HH12:MIPM');

to_char
-------------------------
DECEMBER -THU-31-2009 11:15PM

How do I get the last day of the month in Redshift?

The Redshift LAST_DAY function returns the date of the last day of the month that contains a given date. The return type is always DATE, regardless of the data type of the argument. The syntax is:

LAST_DAY ( { date | timestamp } )

An example:

SELECT
  LAST_DAY(‘2022-07-22’);

  last_day
------------
 2022-07-31
(1 row) 

How do you find the current date in Redshift?

Amazon Redshift has a CURRENT_DATE function, which returns the start date for the current transaction (UTC) in the format YYYY-MM-DD. An example:

SELECT CURRENT_DATE;   
date
------------
2022-07-22
(1 row)

How do you extract month and year from Redshift?

The Redshift EXTRACT function may be used to extract month and year. The function returns a date or time part from a valid timestamp expression and accepts the syntax:

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp | time | timetz } )

To extract month and year:

SELECT 
  EXTRACT(MONTH from ‘2022-07-22’) as month,
  EXTRACT(YEAR from ‘2022-07-22’) as year

month | year
--------+---------
 7       |   2022
(1 rows)

Expert Help

Learn how Zuar can help you manage your Redshift data. With our Runner solution, you can easily integrate your Redshift data (or other Amazon data) into your data pipeline. Automate ETL/ELT processes and get data flowing from hundreds of potential data sources to a single destination for analytics. Schedule a free data strategy assessment with one of our experts and we'll show you how!