Working with Redshift SYSDATE Function: Made Easy 101

Amazon Redshift provides Date and Time functions among other functions that provide functional access to the date and time module in the data warehouse. These Date and Time functions are Scalar functions that can be used to query timeline data as well as update them in Redshift. But, why are these functions needed?

Date and Time functions are used to generate sample data based on a specified timeline and also report the timeline of an operation. They are also used to provide input values in the date format that can serve as arguments to other functions when needed during query execution or for system diagnosis.

In this article, you would learn briefly about Date and Time functions in Redshift, how to use the Redshift SYSDATE Function, and also see sample queries where the Redshift SYSDATE function is implemented.

Table of Contents

• Understanding Date & Time Functions in Redshift
• Types Of Date & Time Functions
• Understanding the Redshift SYSDATE Function
• Working with the Redshift SYSDATE Function
  • Get Timestamp
  • Get Date
  • Query Tables with Date Values
• Conclusion

Understanding Date & Time Functions in Redshift

Date and Time functions in Redshift are used for accessing the date and time module. They serve the primary purpose of enabling the database users the functionality of time-based queries. Time-based operations in this case could be concerning when certain tables were created and manipulating the date/time values in the tables.

However, these functions can be used specifically. They contain date-only functions that can be used for the execution of date-specific queries. They can also be modified in terms of output. Examples of these functions include CENTURY, EPOCH, and DECADE. These functions will be highlighted in more detail in the succeeding section.

Another key point to note is that these functions are not the same. They serve different purposes and require different levels of access. In the next section, we would go over the two groups of Date and Time functions in Redshift.

Types Of Date & Time Functions

• Date and Time functions in Transactions: These are also called Transactional functions. They are used during the execution of queries. They operate in real-time as the query is being executed and give their results in real-time. Examples include:
  • TIMESYSTEMYSDATE
  • CURRENT_DATE
  • GETDATE
  • TIMEOFDAY

• Deprecated Leader Node-Only Functions: Usually, Amazon Redshift queries are distributed and run either on the compute nodes or exclusively on the leader node. The leader node runs queries that either reference catalog tables(tables with the PG naming prefix) or do not reference any table. The leader node on the other hand distributes queries that reference user-created tables or system tables to the compute nodes. Examples include:
  • AGE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • LOCALTIME
  • ISFINITE
  • NOW

Of all these functions, only a few render their results in more than a timestamp. This provides an added advantage because timestamps are mutable(i.e they can be manipulated). To read more about Date & Time functions, refer to AWS Docs and Redshift Date Functions Blog.

Understanding the Redshift SYSDATE Function

The Redshift SYSDATE Function returns the current date and time in the current session time zone(in UTC by default). The current session started refers to the date and time during a transaction in the current transaction. Syntactically, this function takes no argument.

Syntax: SYSDATE

Argument: The Redshift SYSDATE function takes no arguments.

Return Type:  The execution of the Redshift SYSDATE function returns the start date or time of the current transaction as a  Timestamp. The timestamp is represented in the format YYYY-MM-DD H:M:S except it is modified.

Working with the Redshift SYSDATE Function

The Redshift SYSDATE function can be used in a number of ways depending on the user’s need. Let’s understand the queries you can implement to get the information required using the Redshift SYSDATE function.

• Get Timestamp
• Get Date
• Query Tables with Date Values

1) Get Timestamp

Query to get Timestamp:

 SELECT SYSDATE;

The query above would return the timestamp in the format YYYY-MM-DD H:M:S. This timestamp can serve as an input to another Redshift function. 

2) Get Date

To return the current date with the time excluded, the Redshift SYSDATE function is placed inside the TRUNC function.

Query to get only date:

SELECT TRUNC(SYSDATE);

This query would return only the current date in the format YYYY-MM-DD.

3) Query Tables with Date Values

The Redshift SYSDATE function can be used to get results based on the date values present. In the example query below, we would return a result set based on the sale time of products between the current date and 100 days earlier.

Query using date values and TRUNC:

SELECT PRODUCTID, PRICE, TRUNC(SALETIME) as SALETIME, TRUNC(SYSDATE) AS NOW
WHERE
SALETIME BETWEEN TRUNC(SYSDATE) - 100 AND TRUNC(SYSDATE) ORDER BY SALETIME DESC;

The query above would return a result set(table specifically) containing the sales information of products in the format – productid, price, saletime between now and 100 hundred days earlier. Notice the use of the TRUNC function to return only the date as needed and also the NOW statement to use the current date.

As seen in the sample queries above, the Redshift SYSDATE Function can be used for analysis based on timeline period as well as for system diagnostics by the system administrator.

Additionally, apart from using the TRUNC function to return only the date, other functions such DATE_PART and EXTRACT can be used to achieve almost the same. For example, the query below demonstrates the use of DATE_PART and EXTRACT to extract the minute for the final result.

Query to get timestamp using EXTRACT and DATE_PART:

SELECT  EXTRACT(minute from sysdate);  
SELECT DATE_PART(minute, sysdate);

The queries above will return a timestamp containing hour, seconds, day, month, and year but not the minute. 

The queries written above show the flexibility of the Date and Time functions in Redshift and the versatility of the Redshift SYSDATE Function. To read more about the Redshift SYSDATE Function, refer to AWS Documentation.

Conclusion

In this article, you have learned briefly about Date and Time functions in Redshift and how to work with the Redshift SYSDATE function. Amazon Redshift provides several functions that work for both transactional processes and system diagnostics if the need arises. Redshift SYSDATE is one of such functions. 

However, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, Marketing Platforms to your Amazon Redshift can be quite challenging. This can be quite challenging. This is where a simpler alternative like Hevo can save your day!

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Data Warehouse such as Amazon Redshift 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.

Share your experience with the Redshift SYSDATE Function in the comments section below!