Amazon Redshift DATEDIFF Function: Cheat Sheet & FAQ
This how-to guide explains Redshift's DATEDIFF function including arguments, examples, related functions, and frequently asked questions.
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:00We 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
15Examples 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:
Matt Palmer
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 + timeAn example of the function:
SELECT
DATE '2022-07-22' + ‘12:00:00’ AS ts
ts
---------------------
2022-07-22 12:00:00How 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:15PMHow 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!
Team Zuar
Matt Palmer