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:
- Redshift Set Operators: UNION, EXCEPT/MINUS and INTERSECT
- Amazon Redshift WITH Clause Syntax, Usage and Examples
- Amazon Redshift Architecture
- Redshift Analytic Functions and Examples
- How to Alter Redshift Table column Data type? Explanation
- Amazon Redshift Validate Date – isdate Function
Hope this helps 🙂