Redshift Timestamp to Date: 5 Critical Aspects

Amazon Redshift is a petabyte-scale Cloud-based Data Warehouse service. It is optimized for datasets ranging from a hundred gigabytes to a petabyte can effectively analyze all your data by allowing you to leverage its seamless integration support for Business Intelligence tools.

This blog talks about how you can convert Redshift Timestamp to Date using 5 easy-to-understand examples. It also gives a brief overview of Redshift before diving into Redshift Timestamp to Date conversion.

Table of Contents

• Introduction to Amazon Redshift
• What is Redshift DATE_TRUNC Command?
• Redshift Timestamp to Date Conversion Examples
  • Redshift Timestamp to Date: datepart = day
  • Redshift Timestamp to Date: datepart = week
  • Redshift Timestamp to Date: datepart = month
  • Redshift Timestamp to Date: datepart = year
  • Redshift Timestamp to Date: datepart = hour
• Conclusion

Introduction to Amazon Redshift

Redshift is a fully managed data warehousing solution provided by AWS. A data warehouse, unlike a database, is used for OLAP (Online Analytical Processing) use cases, instead of OLTP (Online Transactions Processing). Therefore, it is optimized for complex and lengthy queries. It can contain several duplicates to aid parallel computation and provide faster results. 

To provide faster results, Redshift organizes data in columns instead of rows, which allows it to use its massive parallel processing (MPP) technology to speed up query execution. It claims to provide 10x better performance than any other data warehouse, scaling to petabytes of data. However, its performance takes a hit when it encounters semi-structured data formats like JSON. It is more suited for day-to-day business operations, like BI analysis and so on. It integrates seamlessly with several other AWS services like Aurora, S3, Quicksight, etc.

Key Features of Amazon Redshift

• Enhanced Scalability: Amazon Redshift is known for providing consistently fast performance, even in the face of thousands of concurrent queries. Amazon Redshift Concurrency Scaling supports nearly unlimited concurrent queries and users. By leveraging Redshift’s managed storage, capacity is added to support workloads of up to 8 PB of compressed data. Scaling is just a simple API call, or a few clicks in the console away.    
• Easy Management: Amazon Redshift automates oft-repeated maintenance tasks so that you can focus on gathering actionable insights from your data. It is fairly simple to set up and operate. A new Data Warehouse can be deployed with just a few clicks in the AWS console. Key administrative tasks like backup and replication are automated. Data in Amazon Redshift is automatically backed up to Amazon S3. Amazon Redshift can replicate your snapshots to Amazon S3 asynchronously in a different region for disaster recovery. The Automatic Table Optimization selects the best distribution keys and sort method to enhance the performance efficacy for the cluster’s workload. Amazon Redshift also gives you the flexibility to work with queries in the console, or Business Intelligence tools, libraries, and SQL client tools.    
• Robust Security: Amazon Redshift is known for providing robust data security features at no extra cost. Amazon Redshift allows you to configure firewall rules to take control of network access to a specific Data Warehouse Cluster. Amazon Redshift also specializes in granular column and row-level security controls that ensure that users can only view data with the right type of access. Apart from these, Amazon Redshift also delivers on its promise of reliability and compliance through tokenization, end-to-end encryption, network isolation, and auditing.  
• Data Lake and AWS Integrated: Amazon Redshift allows you to work with data in various open formats that can easily integrate with the AWS ecosystem. Amazon Redshift makes it exceptionally easy to query and write data to your Data Lake in open formats such as JSON, ORC, CSV, Avro to name a few. The federated query capability allows you to query live data across multiple Aurora PostgreSQL and Amazon RDS databases to get enhanced visibility into the business operations. This is carried out without the need for any undesired data movement. The AWS Analytics ecosystem allows you to handle end-to-end analytics workflows without any hiccups. You can also bring in data from various applications like Google Analytics, Facebook Ads, Salesforce to an Amazon Redshift Data Warehouse in a streamlined manner.     
• Flexible Performance: Amazon Redshift distinguishes itself by offering swift, industry-leading performance with a keen focus on flexibility. This is made possible through result caching, materialized views, efficient storage, RA3 instances, and high-performance query processing to name a few. Result Caching is used to deliver sub-second response times for repeat queries. Business Intelligence tools, dashboards, visualizations leveraging repeat queries experience a significant performance boost. At the time of execution, Amazon Redshift looks through the cache to see if there is a cached result for repeat queries. Amazon Redshift also uses sophisticated algorithms to classify and predict the incoming queries based on their run times and resource requirements to manage concurrency and performance dynamically. This helps users prioritize business-critical workloads.   

What is Redshift DATE_TRUNC Command?

We often encounter columns of type ‘timestamp’. It is the preferred format when storing raw data. However, during analysis and presentation, depending on the type of analysis performed, we may not care about the granular details (like the exact millisecond, second, hour, minute, etc.). Consider the example of the business transactions of a conglomerate. Each transaction will have the exact timestamp associated with it.

However, most of the analysis would answer questions like “ What is the average volume of transactions per day?”, or “How does this month compare to the previous month?”, etc. For such analyses, you may want to truncate the timestamp column to at least the date level, if not further. The DATE_TRUNC command lets you do exactly that. In this article, we will examine the use of this command with examples.

Pre-requisites

A basic understanding of SQL is required. An AWS account with a provisioned Redshift cluster will help you execute what you learn. If you’ve never created a Redshift cluster in the past, then you are eligible for 2 month free trial of Redshift. You can find more details here.

Redshift Timestamp to Date Conversion Examples

The DATE_TRUNC function, as the name suggests, truncates a timestamp to the required resolution. Its syntax is as follows:

DATE_TRUNC('datepart', timestamp)

Here, timestamp corresponds to a column or a single timestamp value that you wish to truncate. You can think of datepart as the resolution to which you want to truncate the timestamp. It takes in several values (the list can be found here). Let’s look at some Redshift Timestamp to Date conversion examples to understand this better.

Examples:

When you first create a Redshift cluster, AWS provides you with some sample data. One of the tables in the sample data, called event, contains a column starttime. We will use that column for most of the examples:

• Redshift Timestamp to Date: datepart = day
• Redshift Timestamp to Date: datepart = week
• Redshift Timestamp to Date: datepart = month
• Redshift Timestamp to Date: datepart = year
• Redshift Timestamp to Date: datepart = hour

Redshift Timestamp to Date: datepart = day

This is one of the common use cases: truncating a timestamp to the day. This helps in evaluating the day-to-day trends in your data. Once the truncation to the day value has happened, you can group by the truncated column to evaluate aggregates.

Example Query:

SELECT MAX(starttime) as timestamp, DATE_TRUNC('day',starttime) as date from dev.public.event
GROUP by date
LIMIT 5

Output:

Redshift Timestamp to Date: datepart = week

This is a special case scenario. Over here, for any given timestamp, the output is the date corresponding to the Monday of that particular week.

Example Query:

SELECT MAX(starttime) as timestamp, DATE_TRUNC('week',starttime) as date from dev.public.event
GROUP by date
LIMIT 5

Output:

You can check that all the output dates correspond to Mondays. Why Monday and why not Sunday? Well, Redshift made that choice. Essentially, this helps when you want to see the week-on-week trends in your data. All the timestamps from Monday to Sunday will be converted to a common value, allowing for groupings.

Redshift Timestamp to Date: datepart = month

Over here, the output corresponds to the first day of the month in which that particular timestamp falls. Just like in the case of datepart = week, this facilitates the grouping of data.

Example Query:

SELECT MEDIAN(starttime) as timestamp, DATE_TRUNC('month',starttime) as date from dev.public.event
GROUP by date
LIMIT 5

Output:

As you can see, all the output values correspond to the 1st of the month.

Redshift Timestamp to Date: datepart = year

Over here, as you would have guessed already, the output corresponds to the first date of the year in which the timestamp falls.

Example Query:

SELECT starttime as timestamp, DATE_TRUNC('year',starttime) as date from dev.public.event
LIMIT 5

Output:

Through these examples, you must have guessed what century, millennium, etc. would do. Here is a list of them below.

datepart	description	sample input	output
century	returns the date corresponding to the first date of that century	1995-02-01 20:59:21	1901-01-01 00:00:00
millenium	returns the date corresponding to the first date of that millennium	1995-02-01 20:59:21	1001-01-01 00:00:00
decade	returns the date corresponding to the first date of that decade	1995-02-01 20:59:21	1990-01-01 00:00:00
quarter	returns the date corresponding to the first date of the corresponding quarter	1995-02-01 20:59:21	1995-01-01 00:00:00

Now let’s explore some higher resolution dateparts.

Redshift Timestamp to Date: datepart = hour

Here, the date gets truncated to the hour level in the Redshift Timestamp to Date conversion.

Example:

SELECT DATE_TRUNC('hour', '2021-01-01 20:59:21'::timestamp) as date 

Output:

What is interesting though, is the behavior if I have a timestamp with timezone as input.

SELECT DATE_TRUNC('hour', '2021-01-01 20:59:21+02'::timestamptz) as date 

Output:

As you can see, the output is in UTC format. Keep this in mind when using the date_trunc function with a column of type ‘timestamp with timezone’. If you don’t want the answer to be in UTC terms, you can simply cast the column as timestamp.

SELECT DATE_TRUNC('hour', '2021-01-01 20:59:21+02'::timestamp) as date 

Output:

I believe that after seeing all these examples, you would have guessed what ‘minute’, ‘second’, ‘millisecond’ and ‘microsecond’ would do. Listing down the examples for these:

datepart	description	sample input	output
minute	Truncate the timestamp to minutes resolution	1995-02-01 20:59:21.7898909	1995-02-01 20:59:00
second	Truncate the timestamp to seconds resolution	1995-02-01 20:59:21.7898909	1995-02-01 20:59:21
millisecond	Truncate the timestamp to milliseconds resolution	1995-02-01 20:59:21.7898909	1995-02-01 20:59:21.789
microsecond	Truncate the timestamp to microseconds resolution	1995-02-01 20:59:21.7898909	1995-02-01 20:59:21.789891

Conclusion

We saw the usage and applications of the DATE_TRUNC function in Redshift, through examples. We also saw how the handling of timestamps with timezone. I hope you liked this article. Thanks for reading.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like Amazon Redshift into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.