How to Query Date and Time in Redshift

In this tutorial, we will walk through how to query date and time in Amazon Redshift, focusing on the available date/time types, SQL functions, and operators. Understanding how to properly work with dates and times is essential for efficiently analyzing time-based data, which is a common task in data analysis and reporting.

1. Redshift Date and Time Data Types

Redshift supports several date and time data types, each serving a unique purpose:

• DATE: Stores only the date in 'YYYY-MM-DD' format.
• TIME: Stores only the time in 'HH:MM:SS' format.
• TIMESTAMP: Stores both date and time in 'YYYY-MM-DD HH:MM:SS' format.
• INTERVAL: Represents a time span, which can be useful for calculating durations between dates or timestamps.

2. Querying Date and Time

Now let’s look at some examples of how to query date and time values in Redshift.

2.1. Getting the Current Date and Time

To retrieve the current date and time, you can use the CURRENT_DATE and CURRENT_TIMESTAMP functions. These will return the current date and timestamp, respectively, based on the system’s timezone.

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

2.2. Extracting Parts of a Date or Timestamp

You can extract specific parts of a date or timestamp (e.g., year, month, day) using the EXTRACT function.

SELECT EXTRACT(YEAR FROM CURRENT_DATE);

This will return the current year as an integer.

2.3. Date and Time Arithmetic

Redshift allows you to perform arithmetic on dates and times. For example, to find the date 10 days from today, you can use the DATEADD function:

SELECT DATEADD(day, 10, CURRENT_DATE);

2.4. Working with Time Zones

Redshift stores timestamps in UTC by default. If you need to work with time zones, you can use the CONVERT_TIMEZONE function to adjust timestamps to different time zones:

SELECT CONVERT_TIMEZONE('UTC', 'US/Pacific', CURRENT_TIMESTAMP);

3. Conclusion

Mastering date and time functions in Redshift is crucial for handling time-based data effectively. Whether you're performing simple date extraction or working with time zone adjustments, these tools can greatly enhance the precision of your queries and reports. We hope this guide helps you leverage the full power of Redshift’s date and time capabilities in your data analysis tasks.