Redshift Extract Function Usage and Examples

  • Post author:
  • Post last modified:April 3, 2023
  • Post category:Redshift
  • Reading time:11 mins read

Redshift extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. This function is equivalent to Redshift date_part() function.

Redshift Extract Function Usage and Examples

Page Contents

Introduction to String Functions

The extract function is very useful when you are working with different heterogeneous data sources. You may have requirement to extract part of timestamp values and pass it to an calling application. For example, extract time part of the current datetime and append it to file name to create unique file name. Likewise, there are so many applications that use extract function whenever you require.

Redshift Extract Function

The EXTRACT function returns a date part, such as a day, month, or year, from a time stamp value or expression. The extract function is synonymous to DATE_PART function. Both function provides similar functionality.

Redshift Extract Function Syntax

Below is the Extract function syntax that is available postgreSQL:

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp } );

Redshift Extract Function Datepart or Timepart and Abbreviations

Below table represents the descriptions of the different datepart or timepart used in extract function. These values are sometimes referred to as a units in extract function:

Datepart or Timepart Abbreviations
millennium, millennia mil, mils
century, centuries c, cent, cents
decade, decades dec, decs
epoch epoch
year, years y, yr, yrs
quarter, quarters qtr, qtrs
month, months mon, mons
week, weeks w
day of week dayofweek, dow, dw, weekday. Returns an integer from 0–6, starting with Sunday.
day of year dayofyear, doy, dy, yearday.
day, days d
hour, hours h, hr, hrs
minute, minutes m, min, mins
second, seconds s, sec, secs
millisecond, milliseconds ms, msec, msecs, msecond, mseconds, millisec, millisecs, millisecon
microsecond, microseconds microsec, microsecs, microsecond, usecond, useconds, us, usec, usecs
timezone, timezone_hour, timezone_minute Time zone parts.

Note that, date_part options are same as option available in Redshift Date_Trunc function.

Amazon Redshift Extract Function Examples

Redshift Date Extract Examples

Following are some of the examples on how to use extract function.

Redshift extract epoch from date:

 select extract(epoch from now());
    date_part
------------------
 1566834827.66205
(1 row)

Amazon Redshift extract millennium from date:

select extract(millennium from now());
 date_part
-----------
         3
(1 row)

Redshift extract century from date:

 select extract(century from now());
 date_part
-----------
        21
(1 row)

Redshift extract decade from date:

select extract(decade from now());
 date_part
-----------
       202
(1 row)

AWS Redshift extract year from date:

select extract(year from now());
 date_part
-----------
      2019
(1 row)

Redshift extract quarter from date:

select extract(quarter from now());
 date_part
-----------
         3
(1 row)

Redshift extract month from date:

select extract(month from now());
 date_part
-----------
         8
(1 row)

Amazon Redshift extract week from date:

select extract(week from now());
 date_part
-----------
        35
(1 row)

Redshift extract day from date:

select extract(day from now());
 date_part
-----------
        26
(1 row)

Redshift extract day of the week from date:

 select extract(dow from now());
 date_part
-----------
         1
(1 row)

Amazon Redshift extract day of the year from date:

 select extract(doy from now());
 date_part
-----------
       238
(1 row)

Redshift extract hours from date:

 select extract(hour from now());
 date_part
-----------
        15
(1 row)

Redshift extract seconds from date:

 select extract(second from now());
 date_part
-----------
 14.159083
(1 row)

Amazon Redshift extract milliseconds from date:

select extract(millisecond from now());
 date_part
-----------
 39414.346
(1 row)

Redshift extract microseconds from date:

 select extract(microsecond from now());
 date_part
-----------
   1534107
(1 row)

Benefits of Extract Function in Amazon Redshift

The Extract function in Amazon Redshift is a SQL string function that allows you to extract parts of a date or time value such as year, month, day, hour, minute, and second. The benefits of using the Extract function in Amazon Redshift include:

  1. Simplify data analysis: The Extract function makes it easy to extract specific parts of a date or time value, allowing you to focus on the data that is most relevant to your analysis and reporting.
  2. Improved performance: By extracting only the necessary parts of a date or time value, you can reduce the amount of data that needs to be processed, which can result in improved query performance.
  3. Increased flexibility: The Extract function allows you to extract a wide range of date and time values, giving you the flexibility to work with different data types and formats.

Overall, the Extract function is a useful function for simplifying data analysis, improving performance, increasing flexibility, and ensuring consistency when working with date and time values in Amazon Redshift.

Conclusion

The Extract function also provides flexibility by allowing you to extract different parts of a date/time value and work with various date/time formats. Furthermore, Extract ensures consistency by consistently extracting the same parts of a date/time value across different queries and datasets, making it easier to compare and analyze data. However, there are some limitations to consider when using the Extract function, such as the inability to extract time zones or milliseconds.

Related Articles

Hope this helps 🙂

Tags: ,