Commonly used Redshift Date Functions and Examples

Redshift is a data warehousing solution provided by Amazon Web Services (AWS). It supports a variety of date and time functions that can be used to perform operations on date and time data stored in Redshift tables.

This article is about detailed descriptions and examples of the commonly used Amazon Redshift date functions that you can use to manipulate date data types in Resdshift SQL.

In the real word scenarios many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. Each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same.

Redshift Date Functions

Amazon Redshift provides many functions to handle the date data types. The list also includes AWS Redshift time functions which you can use to handle time data type. These Redshift datetime functions allow you to perform date manipulation. These functions are sometime called as Redshift date arithmetic functions.

Following are the commonly used Redshift date data types.

Date Function	Descriptions
current_date	Returns the current system date
add_months(date/timestamp,n)	Returns the date that corresponds to date plus the number of months indicated by the integer n. +ve n determines the addition and -ve n represent date substraction
DATE_PART_YEAR (date)	Extracts the year from a date. The function returns year in the form of Integer.
DATEADD (datepart,interval, {date\|timestamp})	Increments a date or time by a specified interval.
DATEDIFF (datepart, {date\|time},{date\|timestamp})	Returns the difference between two dates or times for a given date part, such as a day or month.
DATE_PART (datepart, {date\|time})	Extracts a date part value from date or time.
GETDATE()	Returns the current date and time in the current session time zone.
EXTRACT (datepart FROM {TIMESTAMP ‘literal‘ \|timestamp})	Extracts a date part from a timestamp or literal. Read: Redshift Extract Function Usage and Examples
LAST_DAY(date)	Returns the date of the last day of the month that containsdate.
SYSDATE	Returns the date and time in the current session time zone.
TO_TIMESTAMP (‘timestamp‘, ‘format‘)	Returns a time stamp with time zone for the specified time stamp and time zone format.
LAST_DAY(date)	Returns the date of the last day of the month that contains date.
TRUNC(timestamp)	Truncates a time stamp and returns a date. Read: Redshift Trunc and Date_Trunc Functions
TO_DATE(‘current_date’, ‘format’)	Convert string containing date value to date format.
TO_CHAR(‘current_date’, ‘format’)	Convert date format to char format.

Redshift Date Function Examples

Following are the some of the examples of Redshift date functions:

Read:

current date and timestamp in Redshift:

To get the current date and timestamp in Redshift, you can use the following SQL functions

select current_date;
select current_timestamp; 
--Sysdate is similar to Oracle
select sysdate;
select getdate();

Add months and convert date to YYYYMMDD Format in Redshift:

You can add months to a date in Redshift using the add_months function and the use to_char to convert date to YYYYMMDD format.

select to_char(add_months(to_date(current_date, 'YYYY-MM-DD'), -1), 'YYYYMMDD');

Add months and convert date to integer:

You can add months to a date in Redshift using the add_months function and the use to_number to convert to integer format.

select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999');

Substract Month from date in Redshift

To subtract months from a date in Redshift, you can use the add_months function with a negative interval or directly substract interval from date.

select date(current_date - cast('1 month' as interval));

Extract year,day,month part from the current date in Redshift

You can extract the year, day, and month parts from a date in Redshift using the date_part or extract function. The following query returns the current year, day, and month:

select extract (year from current_date); 
--Use date_trunc function
select date_trunc('year', current_date); 
--Use Extract function
select extract (day from current_date);
select extract (month from current_date);

The extract function will return the same result as the date_part. Both functions allow you to extract various parts from a date, but extract is more commonly used in SQL and might be more familiar to some users.

Redshift get first and last day of previous month :

To get the first and last day of the previous month in Redshift, you can use the last_day with date arithmetic operations.

For examples,

select last_day(current_date - cast('2 month' as interval)) + cast('1 day' as interval) as first_day;
select last_day( current_date - cast('1 month' as interval)) as last_day;

Read:

Hope this helps 🙂