Redshift Datepart Function 101: Syntax and Usage Simplified

With the emergence of Cloud Data Warehouses, enterprises are gradually moving towards Cloud storage leaving behind their On-premise Storage systems. This shift is observed owing to the high computing power and on-demand scalability offered by them. Amazon Web Services is one such Cloud Computing platform that offers Amazon Redshift as their Cloud Data Warehouse product.

Users can seamlessly transfer their data from various SaaS applications and other databases to Amazon Redshift. Owing to its amazing speed and performance, you can run multiple Queries using SQL and generate insightful reports from your BI Tools in real-time.

One of the important data points required for generating meaningful reports is the date and time at which an event occurred. You can use the Redshift Datepart command to query data based on a specific date and time.

In this article, you will learn how to work with Redshift Datepart in detail with the help of a few easy-to-understand examples.  

Introduction to Redshift

Amazon Redshift is a brilliant Cloud-based Data Warehouse run by Amazon Web Services that allows companies to store petabytes of data in scalable storage units known as “Clusters” which can be queried in parallel. Inside these Clusters are the computing resources called Nodes. You can scale your Cluster within minutes and analyse your data with great speed.

Key Features of Redshift

• AWS Ecosystem Integration: Redshift seamlessly integrates with other Amazon services like S3, allowing easy data transfer for further analysis with tools like Athena and SageMaker.
• Unbeatable Performance: Utilizes machine learning capabilities, R3 instances, and AQUA for superior speed across varying workloads. Repeated queries benefit from saved results for faster performance.
• Flexible Pricing Options: Offers hourly, yearly, or query-based pricing, allowing users to pay for what they need. Free Concurrent Scaling credits enable easy scaling and cost prediction.
• Scalability: Fully managed storage enables easy scaling with clicks or API calls. Built-in compression encoding saves storage space, allowing concurrent queries against petabytes of data without loading or transformation.
• Manageability: Simple setup and operation with automatic optimization for fluctuating workloads, providing clear instructions from the start.
• Complete Security: Provides SSL security for data in transit and AES 256-bit encryption for data at rest, ensuring safe data sharing across clusters.

Redshift Datepart Function: Syntax and Examples

The date and time for an event are the most commonly recorded data that is stored by many applications in various organisations. The date data types are complex and generally consist of Century, Year, Month, Day, Hour, Minute, and Second.

To query a specific part of the date and time data in an SQL environment, you can use the Redshift Datepart function.

1) Redshift Datepart Syntax

You can execute the Redshift Datepart in the form given below.

DATE_PART ( datepart, {date|timestamp} )

The above syntax has 2 parameters:

• Datepart: This is the interval/range we require from a certain date. The Redshift Datepart function returns the interval value as an integer.
• Date/Timestamp: This could either simply be a date/time or an expression to pinpoint that date from which the datepart is retrieved. 

The table below provides the standard Redshift Datepart parameter intervals: 

Date Part or Time Part Intervals	Abbreviations	Usage and Integer Values
millennium, millennia	mil, mils	Full 1000 year periods starting from “0001-01-01 00:00:00 AD”
century, centuries	c, cent, cents	Full 100 year periods
decade, decades	dec, decs	Full 10 year periods
epoch	epoch	Number of seconds from 00:00:00 UTC on 1 January 1970
year, years	y, yr, yrs	4 digit year
quarter, quarters	qtr, qtrs	Quarters of a year, from 1 to 4
month, months	mon, mons	Months of an as 1 to 12
week, weeks	w	The week number in the year of the specific date. (1-53)
day of week	dayofweek, dow, dw, weekday	Day of the week from 0 to 6, starting with Sunday.
day of year	dayofyear, doy, dy, yearday	Day of the year(1-366)
day, days	d	Day of the Week from 1 to 7  where Sunday is 1
hour, hours	h, hr, hrs	Hour of the day, from 0 to 23
minute, minutes	m, min, mins	Minute of the hour, from 0 to 59
second, seconds	s, sec, secs	Second of the minute, from 0 to 59
millisecond, milliseconds	ms, msec, msecs, msecond, mseconds, millisec, millisecs, millisecond	Second of a minute with 3 decimal place accuracy multiplied by 1000 (0 to 59999)
microsecond, microseconds	microsec, microsecs, microsecond, usecond, useconds, us, usec, usecs	Second of a minute with 6 decimal place accuracy multiplied by 1000000 (0 to 59999999)

The above table can be used as a reference when executing the Redshift Datepart command.

2) Redshift Datepart Examples

Let’s understand this function from the following examples:

A) Extract the century from a literal date value

Output: 

pgdate_part
-------------
21
(1 row)

Note: the default name “pgdate_part” will be shown when the column is unnamed.

B) Extract a quarter from the date

Output: 

pgdate_part
-------------
1
(1 row)

C) Extract the day of the week and the day of the year

SELECT date_part('dow',TIMESTAMP '2021-03-19 10:20:30') dow,date_part('doy',TIMESTAMP '2021-03-19 10:20:30') doy;

Output: 

dow | doy
----+-----
 7  | 78
(1 row)

D) Extract hour, minute, and seconds from the timestamp

SELECT date_part( hour,TIMESTAMP '2021-06-20 09:11:44') h,
date_part( minute ,TIMESTAMP '2021-06-20 09:11:44') m,
date_part( second ,TIMESTAMP '2021-06-20 09:11:44') s;

   Output: 

h   | m | s
----+---+----
 09 | 11 | 44
(1 row)

D) Using Where Clause

SELECT date_part(dow, arrivaltime) as dow,
arrivaltime from event
where date_part(dow, arrivaltime)=5
order by 2,1;

Output: 

dow | arrivaltime
----+---------------------
   5 | 2021-01-01 13:00:00
   5 | 2021-01-01 13:00:00   
   5 | 2021-01-01 13:00:00
   5 | 2021-01-01 13:00:00

Conclusion

In this article, you have learned to employ the Redshift Datepart command to query the desired results based on the specific part of date and time. The Redshift Datepart function provides smart parameters such as “quarter”, “day of week”,” epoch”, etc. which provides direct answers that otherwise require time-consuming manual calculation.

To address the growing storage and computation requirements of this data, you would be required to invest a section of your Engineering Bandwidth to integrate the data from all your sources and store it in a Cloud Data Warehouse such as Amazon Redshift for further business analytics. All of these challenges can be comfortably handled by a Cloud-Based ETL Tool like Hevo Data. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

Frequently Asked Questions