obstkel.com logo

15 Redshift date functions – frequently used by most developers

redshift date functions

This post on Redshift date functions is intended to simplify the core list of date functions. The 15 date functions with examples are the most commonly used ones by Redshift developers. If you need to reference the full list of date and timestamp functions, click here

Before we get started, a few basics.

  • The default date format in Redshift is YYYY-MM-DD.
  • You convert a date to a string by using the to_char function as below.
to_char(current_date,'YYYYMMDD') = '20220407'
  • Similarly, you convert a string to date using the to_date function.
to_date('20200407','YYYYMMDD') = 2022-04-07


The table below lists all 15 Redshift date functions in alphabetic order.

1. add_months

Syntax: add_months(date, integer)

What it does: This function adds months, specified by integer to the date value, the first argument. Alternately, you could use this function to subtract months by specifying a negative integer.

When using this function, do not think in terms of days. For instance, if you add a month to the 31st of January, with add_months, the returned date will be the 28th of February. So once again, think in terms of number of months and not days.  

Example1: Add two months to a date
SELECT add_months('2022-03-01',2);
Output from SQL statement: 2022-05-01
Example2: Subtract two months from a date
SELECT add_months('2022-03-01',-2);
Output from SQL statement: 2022-01-01

2. current_date

Syntax: current_date

What it does: Returns todays date in the format YYYY-MM-DD from your session time zone. 

SELECT current_date;
Output from SQL statement: 2022-03-25

3. date_cmp

Syntax: date_cmp(date1, date2

What it does: Redshift date_cmp compares 2 dates and returns 1 if date1 is greater than date2, -1 if date1 is less than date2 and 0 if both dates are equal. This function is a simplified version of the interval_cmp function.

Example1: date1 greater than date2
SELECT date_cmp('2022-03-25', '2022-03-10');
Output from SQL statement: 1
Example2: date1 less than date2
SELECT date_cmp('2022-03-10', '2022-03-25');
Output from SQL statement: -1
Example2: date1 equals date2
SELECT date_cmp(current_date, trunc(sysdate));
Output from SQL statement: 0

4. date_part_year

Syntax: date_part_year(date)

What it does: For a given date, this function returns the year portion of the date in the format YYYY.

SELECT date_part_year(current_date);
Output from SQL statement: 2022

5. dateadd

Syntax: date_add(start_date, num_days)

What it does: Returns the date plus number of days specified by integer num_days

SELECT date_add('2020-08-16',5);
Output from SQL statement: 2020-08-21

6. datediff - an important Redshift date function

Syntax: datediff (datepart, date1, date2)

What it does: The Redshift datediff function returns the difference between two dates in the format specified by datepart. The table below lists the most commonly used datepart formats for the datediff function. For the full list of dateparts check out the interval_cmp function.

As usual, if the first date is less than the second date, the result will be a negative number. Similarly, if the first date is larger than the second date you get a positive number. If you do not care for the signed part, then use the absolute value function (abs).

Now for the most important part. Redshift datediff does not return the cumulative difference between two dates. It only returns the difference between the specified date part. Don’t worry, this Redshift date function is one most of us get wrong.

Example 2 below highlights this point.

Date partSyntax
Dayd, day
Weekw, week
Monthmonth
Quarterqtr
Yearyr, year
Epochepoch
Decadedecade
Centuryc, cent, century
Millenniummil, millennium
Example1: Difference between two dates in days
SELECT datediff(day, '2020-08-16', 2020-08-26');
Output from SQL statement: -10
Example2: Difference between two dates in days across multiple years and months
SELECT datediff(day, '2020-08-26', 2019-07-16');
Output from SQL statement: 10

7. date_part

Syntax: date_part (datepart, date)

What it does: Returns the datepart (d for day, w for week, mon for month, y for year) from a given date field.

For a full list of the datapart abbreviations, review the interval_cmp function.

SELECT date_part(year, current_date);
Output from SQL statement:2022 

8. date_trunc

Syntax: date_trunc (‘datepart’, timestamp)

What it does: For a given timestamp, this function truncates the part specified by datepart. Note, the datapart for this function needs to be enclosed in single quotes.

SELECT date_trunc('YEAR', '2020-08-15T06:25:10.234'); 
Output from SQL statement: 2020-01-01 00:00:00

9. extract

Syntax: extract (datepart FROM timestamp)

What it does: Number 9 on the list of Redshift date functions is the extract function. This is a versatile function and one I use frequently.

This function returns the extracted portion of day, month, week, year or time specified by datepart from a given timestamp.  The table below lists frequently used dateparts with the extract function. 

Date partSyntax
seconds, sec, secs, second, seconds
minutem, min, mins, minute, minutes
hourh, hr, hrs, hour, hours
dayd, day, days
day of weekdayofweek, dow, dw, weekday, day of week
day of yeardayofyear, doy, dy, yearday, day of year
Weekw, week, weeks
Monthmon, mons, month, months
Quarterqtr, qtrs, quarter, quarters
Yeary, yr, yrs, year, years
Example: Extract Day from current date
SELECT extract(day from current_date); 
Output from SQL statement: 27

10. getdate() or sysdate

Syntax: getdate() / sysdate

What it does: Returns the current date and time from the session time zone. If you only need the date portion, use the trunc function.

Redshift sysdate vs getdate() to keep in mind. Even though both functions return date and time information, sysdate returns the date and time for the transaction being executed vs getdate() returns the date and time information of the current statement within the transaction. 

For example, use getdate() if you need to determine the execution time between different sql statements in a transaction. If you do not care for the time portion, then it does not matter if you use getdate() or sysdate. +

Example1: Using sysdate 
SELECT sysdate;
Output from SQL statement: 2011-07-21 10:32:38.248109
Example2: Using getdate() 
SELECT getdate();
Output from SQL statement: 2011-07-21 10:32:38.248109
Example2: Using trunc function to extract date portion from getdate() or sysdate
SELECT trunc(getdate());
Output from SQL statement: 2011-07-21

SELECT trunc(sysdate);
Output from SQL statement: 2011-07-21

11. interval_cmp

Syntax: interval_cmp(interval1, interval2)

What it does: This Redshift date function returns 1(one) if interval1 is greater than interval2, -1 (minus one) if interval1 is less than interval2 and 0 (zero) if interval1 equals interval2.

An interval literal is used to denote a specific quantity of time. For example, 6 days, 9 weeks, 3 years. These are all examples of date interval literals. The table below lists all the date interval literal keywords and syntax. You could also check out the official Redshift documentation for datepart.

 

Date partSyntax
Seconds, sec, secs, second, seconds
Minutem, min, mins, minute, minutes
Hourh, hr, hrs, hour, hours
Dayd, day, days
Day of Weekdayofweek, dow, dw, weekday, day of week
Day of Yeardayofyear, doy, dy, yearday, day of year
Weekw, week, weeks
Monthmon, mons, month, months
Quarterqtr, qtrs, quarter, quarters
Yeary, yr, yrs, year, years
Epochepoch
Decadedec, decs, decade, decades
Centuryc, cent, cents, century, centuries
Millenniummil, mils, millennium, millennia
Example1: Interval1 greater than interval2
SELECT interval_cmp('3 years', '1 year');
Output from SQL statement: 1
Example2: Interval1 less than interval2 using date part abbreviation 
SELECT interval_cmp('1 y', '3 yrs');
Output from SQL statement: -1
Example2: Interval1 equals interval2 
SELECT interval_cmp('7 days', '1 week');
Output from SQL statement: 0

12. last_day

Syntax: dayofweek(date)

What it does: Returns the day of week for a given date or timestamp. This can get a bit tricky if you don’t get the basics right. The 15th of September, 2020 for example falls on the third week of the month, and is the third day of the week.

SELECT dayofweek('2020-09-15');
Output from SQL statement: 3

13. months_between

Syntax: months_between (date1, date2)

What it does: Returns the number of months between 2 dates.
Keep in mind, if the first date is earlier than the second date, then a negative number is returned. You can avoid this by specifying the later date first or by using the Redshift absolute value function (abs).

Example 1:
SELECT months_between('2022-03-20', '2022-02-20');
Output from SQL statement: 1
Example 2: Case where the lesser date is first and greater date is second.
SELECT months_between('2022-02-20', '2022-03-20');
Output from SQL statement: -1

 

Workaround option:
SELECT abs(months_between('2022-02-20', '2022-03-20'));
Output from SQL statement: 1

14. next_day

Syntax: next_day(date, day)

What it does: Returns the first occurrence of the day after the specified date. 
The day portion of the function can be specified in the below formats

 

DayMinimum string length for dayExample
Monday, Wednesday, Friday1M or Monday
W or Wednesday
F or Friday
Tuesday, Thursday, Saturday, Sunday2Tu or Tuesday
Th or Thursday
Sa or Saturday
Su or Sunday
Example1
SELECT next_day('2022-03-20', 'M'); 
or
SELECT next_day('2022-03-20', 'Monday');
Output from SQL statement: 2022-03-21
Example2
SELECT next_day('2022-03-20', 'Tu');
Output from SQL statement: 2022-03-22

15. trunc

Syntax: trunc(timestamp)

What it does: The last on the list of Redshift date functions, this function returns the date portion of a given timestamp. Simple !

SELECT trunc('2022-02-21 11:21:42.248017');
Output from SQL statement: 2022-02-21

Additional Amazon Redshift links

Redshift Create table examples

10 examples on how to create tables in Redshift

Amazon Redshift Database Developer Guide

Link to the official current version from AWS

Interested in our services ?

email us at : info@obstkel.com

Copyright 2022 © OBSTKEL LLC. All rights Reserved