obstkel.com logo

15 Redshift date functions frequently used by 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 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
Centuryc
Dayd
Day of Weekdow
Day of Yeardoy
Decadedec
Epochepoch
Hourhr
Microsecondmicrosec
Millenniummil
Millisecondmillisec
Minutemin
Monthmon
Quarterqtr
Secondsec
Weekw
Yearyr


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.

  1. If the first date is less than the second date, the result will be a negative number.

  2. Similarly, if the first date is greater than the second date you get a positive number.

  3. If you do not care for the signed part, then use the absolute value function (abs).

  4. 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 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: 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:

 

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