1. Date and time functions
During this lesson, we'll delve into working with dates and times within Amazon Redshift.
2. Getting current date and time
One of the first things we often want to do is use the current date and time in queries. The SYSDATE function returns the date and time at the start of the transaction, as shown here. NOTE: it doesn't require parenthesis. Additionally, the GETDATE function returns the date and time at the start of the statement it's in. Make sure you use parentheses when using GETDATE.
3. Date and time function behavior
As we mentioned previously, some functions, such as NOW, are designed exclusively for leader nodes and should be avoided so they don't cause issues. Consistently using the DATEDIFF, GETDATE, and SYSDATE functions would be better than their leader node-only counterparts.
4. Truncating dates and times
Often, we want to take a date or time and truncate it to just a particular portion. The TRUNC function truncates a timestamp down to just a date. For example, truncating the SYSDATE will return today's date, as shown here. Additionally, we can use DATE-TRUNC to truncate a timestamp to a specific datepart, such as an hour or a day. Here, we use DATE-TRUNC on the SYSDATE to truncate it to the minute. The AWS documentation provides a complete list of dateparts.
5. Getting parts of dates and timestamps
The DATE-PART function extracts specific portions from dates and timestamps. It uses dateparts to specify what part of the date or timestamp we want. For example, if we use the date-part function to extract the month from SYSDATE, we will get the number one. The DATE-PART function always returns a number. The function can do far more than just day, month, and year for extractions, including less standard units such as the day of the week, quarter, and timezone. Here, we're extracting the day of the week, day six, or Saturday since Redshift uses zero through six, starting with Sunday.
6. Comparing dates and times
To compare dates and times, we have the DATE-CMP function, which compares two values and determines their relative positions. If the first date value is earlier, the function returns a negative one; if the first value is later, it returns one. If the values are equal, it returns 0. For example, we have a table with historical and projected data and dates. In that case, we can use the date comparison to see where the switch from historical to projected data occurs, assuming it's updated daily. Additionally, other type-specific comparison functions work in the same manner.
7. Calculating differences
The DATEDIFF function calculates differences between two values considering the specified datepart. It supports multiple data types. However, it requires a data type with the datepart we are using. For example, we can't use the hour datepart on just a date. DATEDIFF will return a negative number if the second date value is earlier and a positive value if it is later. If the date values are equal, it returns a 0.
8. Using DATEDIFF
Here's an example showcasing the use of DATEDIFF to determine the number of days until the end of the first quarter based on a specific date, which in this case is 64 days.
9. Incrementing dates and times
In addition to interval literals, Redshift provides a DATEADD function that takes a datepart, quantity, and date-time value. If the quantity is negative, it will subtract it from the date-time value. For example, we can repeat finding next week's date from the SYSDATE using the DATEADD function, as shown here.
10. Incrementing dates and times... gotchas
Be careful when using interval literals and the DATEADD function, as the datepart we choose can have different results based on its intention. For example, adding twelve months or a year to a leap year leads to different results, as we can see here.
11. Let's practice!
Now it's your turn to become a master of time.