15 Redshift date functions frequently used by developers

Updated on: December 7, 2022

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 Redshift date format is YYYY-MM-DD.
To convert a date to a string use the Redshift to_char function as below.

to_char(current_date,'YYYYMMDD') = '20220407'

Similarly, to convert a string to date use the Redshift to_date function.

to_date('20200407','YYYYMMDD') = 2022-04-07

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

Importance of datepart in Redshift date functions

You will see datepart mentioned in more than a few Redshift date functions. They all refer to the same argument. Though simple, its syntax and usage can get confusing. So, let’s clear things up.

Date part is an argument used in Redshift date functions. It is a single lowercase word (datepart) used to denote a part of a date. This could be day, month, year and so on.

The value for the datepart argument is specified without quotes and in lowercase. For example, month is specified as mon.

The table below lists the different date parts and values in alphabetic order. The values listed are not the complete list. They were chosen for being intuitive and unique to remember.

You can also get the complete date part list from the Redshift documentation.

*Date part (datepart)*	Value to use in date function
Century	c
Day	d
Day of Week	dow
Day of Year	doy
Decade	dec
Epoch	epoch
Hour	hr
Microsecond	microsec
Millennium	mil
Millisecond	millisec
Minute	min
Month	mon
Quarter	qtr
Second	sec
Week	w
Year	yr

Another point to clarify, Redshift datepart is not the same as date_part. The first (datepart) is an argument, while the second(date_part) is a date function in Redshift.

1. add_months

Syntax: add_months(date, integer)

What it does: The Redshift add_months function adds months, specified by integer to a date value.

You can also use add_months 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: The Redshift current date function returns today’s 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, the date_part_year function returns the year portion of the date in the format YYYY.

You can also use the date_part function to get year from a date. Unlike the date_part function, the date_part_year function only requires you to specify a date.

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

5. Redshift dateadd

Syntax: dateadd(datepart, interval, date)

What it does: The Redshift dateadd function returns a date plus number specified by interval for a given datepart.

It helps to think of the syntax as

dateadd( "What to add?", "How many to add?", "Which date to add to?")

Following the above simplified pseudo syntax, adding 30 days to the current date looks like this.

dateadd( days, 30, current_date)

Now let’s look at some real examples for Redshift dateadd.

     
  Example1: Add 5 days to a date using Redshift dateadd.

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

     
  Example2: Subtract 5 days from a date using dateadd in Redshift.

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

     
  Example3: Add 6 months to a date using dateadd function.

  SELECT date_add(month, 6, '2020-08-16');
  Output from SQL statement: 2021-02-16

6. Redshift datediff

Syntax: datediff (datepart, date1, date2)

What it does: The Redshift datediff function returns the difference between two dates (date1 and date2) in the format specified by datepart.

The below 4 points are important if you want to use the Redshift datediff function correctly.

If the first date is less than the second date, the result will be a negative number.
Similarly, if the first date is greater 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).
Redshift datediff does not return the cumulative difference between two dates. Rather, it returns the difference between the dates specified by datepart.

For example, if date1 equals 2022-July-04 and date2 equals 2021-July-04, you would expect a difference of one year when you use the datediff function.
However, this is not the case.
The difference in dates returned depends on the value provided to datepart as shown below.
- ```
datediff(day,'2022-07-04', '2021-07-04') = 0 days
```
- ```
datediff(month,'2022-07-04', '2021-07-04') = 0 months
```
- ```
datediff(year,'2022-07-04', '2021-07-04') = 1 year
```

The table below lists the most commonly used datepart formats for the Redshift datediff function. Note, you have to specify the datepart without quotes as listed in the syntax column.

Date part	Syntax
Day	d, day
Week	w, week
Month	month
Quarter	qtr
Year	yr, year
Epoch	epoch
Decade	decade
Century	c, cent, century
Millennium	mil, 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: The Redshift date_part function returns a part of the date specified by datepart.

The date_part function is frequently used to get a month from date or year from date.

I’ve mentioned this before but will do it again. Do not confuse date_part and datepart to be the same. They are not!

One is a function (date_part), while the other(datepart) is an argument in a function.

     
  Example1: Get day(s) from date using Redshift date_part.

  SELECT date_part(d, '2022-07-06');
  Output from SQL statement: 6

     
  Example2: Redshift get month from date using date_part.

  SELECT date_part(mon, '2022-07-07');
  Output from SQL statement: 7

     
  Example3: Redshift get year from date using date_part function.

  SELECT date_part(yr, '2022-07-07');
  Output from SQL statement: 2022

8. Redshift 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.

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.

By default, you should use the current_date function for Redshift current date. However, you could also use the trunc function on either the getdate() or sysdate. The trunc function will get rid of the time part. Example3 below illustrates how to extract the current date.

An important Redshift sysdate vs getdate() difference 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

Example3: How to get Redshift Current date 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 does the below:

Returns 1: If interval1 is greater than interval2.
Returns -1: If interval1 is less than interval2.
Returns 0: If interval1 equals interval2.

An interval literal is used to denote a specific quantity of time. For example, 6 days, 9 weeks, 3 years.

You specify an interval in quotes with a space between the quantity and the datepart.

As an example, an interval of 6 months would be specified as ‘6 mon’.

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: The Redshift next_day function returns the first occurrence of the day after the specified date.

The day portion of the function can be specified in the below formats:

Day	Minimum string length for day	Example
Monday, Wednesday, Friday	1	M or Monday W or Wednesday F or Friday
Tuesday, Thursday, Saturday, Sunday	2	Tu 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

Redshift materialized views: The good, the bad and the ugly

redshift coalesce cover image of girl on computer

Redshift Coalesce: What you need to know to use it correctly

What is Amazon Redshift explained in 10 minutes or less

Interested in our services ?

email us at : info@obstkel.com

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.

Necessary

Always Enabled

Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.