Adding and subtracting date and time data
1. Adding and subtracting date and time data
In this video, we will learn how to calculate differences in time series data and how to add to or subtract from time series data.2. Time differences
Let’s begin with the AGE function. It takes either one or two timestamp arguments and returns the time difference between the two. It subtracts the second argument from the first argument, or if there is only one argument, it finds the difference between that argument and the current date. The result of AGE will be in years, months, and days. Here is an example finding the time between two timestamps.3. Misinterpreting differences
We need to take care about how we interpret the results of AGE. Take a look at this example, one of these years, 2020, contains a leap year with one extra day in it! The query result is correct, but not all years have this duration.4. The subtract operator
We can use arithmetic to help us determine which years are leap years. Here we use a combination of subtract and cast operators to find out which year is the leap year. We can see 2020 has 366 days.5. Using the subtract operator
The subtract operator provides us with an INTERVAL data type. Whereas DATE and TIMESTAMP allow us to store or manipulate a point in time, the INTERVAL data type allows us to store and manipulate a period of time. Here we subtract two times that differ by one minute and 30 seconds.6. Time intervals
Here is an example where we calculate the difference in arrival times between two stations for a commuter train. Note the use of the WITH clause. The query following this clause is known as a Common Table Expression, or CTE. A CTE is a way of defining a temporary table that exists just for one query. Here, we use the CTE to create a temporary table that we alias once as millbrae, and again as hillsdale.7. Time intervals
The result is the difference in arrival times between the two stations.8. Subtracting an interval
We can also subtract an interval from a date. As shown here where we subtract a one month interval from a given date.9. Converting an interval to a specified unit of time
Recall that we can use EXTRACT to retrieve a specific part of a datetime data type. By combining it with the subtract operator, we can get the number of seconds in an interval. Using epoch as the first argument tells our query that the first date time date is the start of the interval and the second is the end.10. Converting an interval to a specified unit of time
To get the minutes, we can adapt the previous query to divide by 60, thus returning the number of minutes.11. Adding time
Calculating one date by adding an interval to another date is a very common operation when dealing with date and time data. Here's how to add 28 days to a date. If we did this calculation with a leap year date, we would get the correct result. As shown here with the result of Feb 29th, 2020.12. Adding a month to a date
Similarly, we can add one month to the date. And again, for a leap year. Note that this time it gives the same result.13. Let's practice!
Let's practice what we just learned.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.