1. Overview of basic arithmetic operators
Now that you have learned the fundamentals of PostgreSQL data types, it's time to learn how to manipulate these data types to assist with your data cleansing and transformation tasks.
2. Topics
In this chapter, you'll dive into some built-in date and time functions and operators and learn how to use them to manipulate date and time data.
First in this video, you'll learn how to add and subtract date and time values and understand the expected behavior of each operation.
Next, you'll learn how to use these functions to retrieve the current date and time.
You'll then look at how to use the AGE function to calculate the difference between two timestamps.
And finally you'll learn how to use the EXTRACT, DATE_PART, and DATE_TRUNC functions to manipulate timestamps to retrieve subfields of date and time values.
Let's get started.
3. Adding and subtracting date / time data
Performing basic arithmetic operations on date and time data types will become a useful skill in practice but it's important to understand how the return values for these operations vary depending on the type of date and time data types you are working with.
For example, when you subtract date values, the result that is returned is an integer data type.
4. Adding and subtracting date / time data
You can also add integer values to date data types. In this example, we are adding the whole number three to a date value and getting a result that is three days greater than the original date. When adding integers to date values, the implied precision is days.
5. Adding and subtracting date / time data
However, when we perform the same operation on timestamp data types as you see in this example, we get an INTERVAL as the result.
6. Calculating time periods with AGE
The AGE function allows us to calculate the difference between two timestamps. The AGE function takes two timestamp arguments and subtracts the first argument from the second and returns an INTERVAL as a result.
You'll notice in this example that the result is identical to what we calculated on the previous slide.
7. DVDs, really??
I'm sure that at this point in the course you've questioned why we are using a fictional DVD rental store as our sample dataset. And it would be a valid question.
As we talked about earlier in the course, the Sakila database is a widely used sample dataset for working with and learning about relational databases. In fact, it's been more than 13 years since the data in this database was created and the `AGE()` function is a great way to highlight this.
8. Date / time arithmetic using INTERVALs
Learning how to use INTERVALs for your date and time calculations is a very useful skill to develop with real world applications. Using an INTERVAL is a great technique when you need to complete relative date and time calculations.
If you recall in the first chapter, we calculated the expected_return date by adding an INTERVAL of 3 days to the rental_date column.
9. Date / time arithmetic using INTERVALs
You can also perform multiplication and division on date and time data types using intervals which is another useful tool when you have relative date and time data.
For example, let's say we wanted to add 21 days to a date value. As you recall from earlier in this video, when you are working with a date data type, you can just add an integer to the date. However, what if you need to perform this calculation with a timestamp? This is where INTERVALs come in handy as you see in this example.
10. Let's practice!
Great work! Now let's practice performing basic arithmetic operations on date and time values in the exercises.