So if you're like me, you're probably a little sick of dates.
We're almost done.
We're going to talk about a handful of date math functions that are built in.
So when I say math, I mean things like calculating how many days are between two dates or if we have
somebody's day.
They signed up for a website and we want to know when a year anniversary has passed.
We need to add a year to that date and figure out if we've hit that date or not.
So the first one of these functions I'll show you is called Date Diff Date Difference, and it expects
us to provide two dates, although it also works with date times, it's not really going to work well
with time, but with dates and date times it will subtract the second expression the second date from
the first, and then return to us a number of days.
So it is not going to work with the number of seconds.
It's not going to tell us how many hours.
It just tells us the number of days.
So let's try an example.
Why don't we work with our birth date?
So select birth date from and then what is it, people?
So that's our birth date.
Let's say I want to know how many days ago that was.
Each birth date was.
I know how to get the current date.
Remember, we can do this.
Cur date.
We don't even need to do from people.
But anyway, we're getting the current date for every row and I can subtract the two.
I can do select and then I'll write date diff and then I'll start with curr, date, comma and then
birth date, curr date and birth date.
So for every row in, people take the birth date and subtract that from the current date.
And this is our number of days and it would be nice to also show the original birth date so we can compare
and see if that seems I'm missing a comma there.
So let's see, this is the one I added today.
So zero days ago.
This is from 2020.
I'm recording this in 2022.
So 781 days makes sense.
1985, 13,000 days, 2000, almost 8000 days ago.
So that's working great.
We can do the same thing with date time, by the way, birth date, and we're going to get the same
result because it's just taking the date portion from that date time.
But if I tried to do birth time, as we discussed, it's not really going to work.
It doesn't give us an error.
It just doesn't work.
We get zero because there's no date information included in birth time.
It's only a time information.
It's only time.
Okay, so that one is date diff.
Then we move on to one.
That's it's a little trickier, but it's also more powerful date add.
And along with that date, sub four subtract.
Now you might think date sub that sounds just like date diff, but they actually serve different purposes.
Date sub and date add allow us to take some date and then do math with some interval.
It could be a day.
It could be a year, it could be a second, it could be 50 seconds.
We have a lot more flexibility where date diff just takes two dates and subtracts them and tells us
the number of days between them.
So with date ADD, I take a date let's say now, right now.
And then I can use this weird syntax.
We write interval and then an expression.
This is called a temporal interval and it looks something like this interval one day interval, one
year, whatever I want, I use this syntax.
So interval has to go first and then whatever my expression and unit is.
So this right here, this date, what is this?
May 1st of 2018, You add one day, which is what we're doing here.
We end up with May 2nd, 2018.
So let's try it over here.
Let's just work with Curr date again and I'm going to do a date add from cur date.
I don't know, what should we add?
How about interval of one year?
And the result is 2023 ten five.
Right.
So October 5th of 2023 or I could do interval of 12 years or interval of one month and we get 1105 2022
I don't know, I'm going in that order.
That's just the American and me starting with the date, then with the month, then date, then year.
But it works, right?
We get one month further from where we are right now.
We also have date sub so for subtract date sub.
Just like that.
And now we end up with 2020 20905.
A month ago I was still working on this course a month ago.
And we can do other things like work with seconds if we wanted to.
We can work with minutes against day seconds.
We can even work with quarters like quarters of the year.
But for the sake of time, I'm not going to dive too much into that.
So we have date, add and date, so we have to use that interval syntax.
I guess I'll do one more example, this time working with our table.
Let's take birth date for every person.
And find out the date where they would have their 18th birthday.
Let's say in the US, they could vote after this date, so we would do date add.
And then we'll take their birth date and then we'll do interval.
18 years.
It's year, not year interval.
18 year.
Och And we can see right there, 2018, if you're born in 2000 you would be 18 and 2018.
You're born this year.
In 2022, you've got to wait until 2040.
Geez, I feel an old now if you're born in 85 2003.
So there we are.
We can do some basic date math by adding an interval or subtracting an interval from a date.
And we can do the same thing with date times.
And we can also do the same thing with time.
However, to work with just times, we have the same two functions for time.
So time diff.
And then we also have if we go up to the top, it's alphabetized ad time and at the bottom somewhere
it will have sub time so we can find the difference between times.
Because when we tried to find the difference between times using a dif date that didn't work.
It's only going to work with dates because it returns a number of days.
So if we work with time diff, it is going to return to us a value expressed as a time value.
So we'll take two different times and subtract them.
So let's try it over here in the shell.
Let's start with the current time and maybe I'll select the difference of time diff.
We're going to have to come up with some contrived thing.
But how about between now and when I woke up?
So cur time and then comma, I woke up.
I'll use a string here.
Seven in the morning just like that.
And it tells me I've been up for 10 hours, 32 minutes and 13 seconds as of right now.
So that's how we can do a time difference.
Remember four dates we use date diff and that returns to us a value in a number of days time diff returns
to us an actual time interval, and that's enough of the actual date functions for doing some basic
math.
The last thing I want to show you is that we can actually do math using the plus and minus operators
as well.
So if you remember, we can do weird things like select three minus four and use my SQL as a calculator.
That minus sign in this situation is an operator that's working with two numbers, but I can actually
do things like this.
Select the current date time and subtract.
Let's do an interval of 18 years and this is going to be 18 years ago from right now would be back in
2004.
Wow.
Someone was born in 2004.
That'd be 18.
That makes you feel old.
I am old, I guess.
Oh, well, you can see, though, that it works.
I don't need to use a function to do that if I prefer this syntax.
Let's do one more example using the plus sign and our real data.
It's not real data, but from our table, let's select the name and the birth date from people.
And let's say that we are a bouncer at a bar in the US where you have to be 21 years old or older to
drink.
And if Bouncer is going to look at each driver's license and add 21 years to see, compare that to the
current date.
So let's see what happens if we add 21 years to birth date.
So we'll do a birth date plus the interval of 21 year.
Just remember it's singular year and there we are, we see 21 years plus.
This date is 20, 21, 12, 25, 21 years plus.
Right.
Today's date, 2022, ten five is all the way in 2043.
And if I wanted to, I can get fancier.
I can do that math.
And then extract, let's say, just the year.
Remember, we have this year function.
So I can do this.
I take the birth date, I add 21 years to it, and then I just take the year out.
And we see 2021, 22,006.
I would even get fancier.
Maybe it's not fancy, but give it an alias like as.
I don't know.
We'll what should we call this will be 21.
And there we are.
This person will be 21 and 2021.
Lulu in 2006, one, 2041.
Hazel 2043.
So if you're like me, you're probably sick of this date math stuff.
But it is good to know how this works.
We can use both this plus syntax or the minus syntax or date add and date sub.
But remember, we definitely need to use this weird interval syntax to express any temporal intervals.
One year, 25 years, one month, two quarters, whatever we want.
There's a whole page on the docs that details the ins and outs of that syntax, but I've shown you the
basics for years, days, months and so on.