All right.
Welcome back.
So in this video, we're going to talk about date math.
How do we add dates, subtract them and figure out how many days are between two dates, for example,
or how many minutes or take a current date and add 10 minutes into the future or something like that.
It's a pretty common thing to do, especially when you're dealing with comments or blog posts or some
sort of information where it makes sense to display how long ago something was created on Facebook.
I think I've been over this before, but Facebook will tell you this.
You know, you commented on this 10 minutes ago and then you come back the next day.
We'll say one day ago and then a week later you'll say a week ago.
And then at some point it transitions over just to the date, we'll say December 5th or something.
So you can accomplish that by using some of the tools I'm going to show you in this video.
Now, we're not going to spend a ton of time on date math because it's not the most exciting thing.
Also, date math sort of sounds like a.
You know, like at the end of a date, fighting for the check.
Figuring out who pays for what.
Anyways.
Yeah, it sounds like a bad band name or something.
Date math though.
In our context, there's two main functions we're going to talk about.
And the first one is pretty simple.
It's called date diff.
No underscore, it's just date diff stands for date difference.
And what it will do is it takes two dates.
So here's an example.
One, a date time, which I should highlight pretty much everything in my SQL that has date in the name
will also accept date time.
So here's a date a date time excuse me, and then here's a date.
And when you subtract them, it turns out they're one day apart.
So this always tells you the number of days.
So it's not going to give you minutes or seconds or months or something.
It gives you days.
And then if you wanted to, you could turn that into months or something.
However, it's just it's primary purpose is to give you the number of days.
So let's try it.
We could do something like this.
Let's work with our star from people.
Let's work with that and let's figure out how many days ago each one of these was.
Each person here was born, so it would be something like select date diff.
And then we want to take now.
And subtract.
Birth.
Date.
Hopefully that makes sense we're taking now.
We could also do curb date.
It wouldn't make a difference because we're not working with a time part of now but I like now it's
a short easy to remember thing.
So select date diff now or comma birth date so that will take current date, subtract birth date and
find the number of days.
Let's try it.
So let's make this a little bit clearer.
Let's do select date diff and we'll also display name and birth date.
Here we go.
So, Padma, born in 1983.
12,215 days ago.
Larry born the longest ago.
1943 is 26,781 days old.
And then toaster was just born.
Today is zero days old.
All right.
So that's all there is to date diff.
The next thing we're going to take a look at is date ad and there's a little bit more to discuss there.
If we just look to the docs date add conveniently right below date diff.
We get another table here like we did when we had date format.
There's a bit to discuss.
So what this allows us to do.
It is kind of the bread and butter for date arithmetic.
So we specify a date.
So date add and there's also a date sub for subtract.
But we're just going to work with date add for now.
So we give it a date or a date time and then a comma and then this sort of weird syntax that basically
specifies what we want to add.
Do we want to add ten days, one year, four quarters or three fourths?
How about 18 months, ten microseconds?
Whatever it is?
The way that we specify is using the interval keyword and then we pair it with one of these units and
then put it all together.
It looks something like this.
So if we have this date time or actually let's take a look at this example here, date add here's a
date time.
And if you want to add 1/2 to it, you have to say interval and then second, which is the unit and
then how many seconds?
So it follows that format interval one day.
And then it gets kind of weird when there's things like minute second.
So this will do one minute, 1/2.
Add that to this date time.
Pretty nuanced thing.
I've never had to use these ever, but good to know that they exist.
So let's give it a shot.
Let's work with.
Let's do.
Why not?
Let's do months.
So we'll go here.
We'll do a select and let's do date time this time.
So it's like birth date time from people.
Just take a look at what we have.
All right.
And now let's try also doing a date add birth date time and we want to add one month.
So we have to say interval one.
And if you can't remember, you can go over here a month.
It's pretty straightforward.
Okay.
So as you can see, here we go from 1983, 1111 to 1983, 1211 or here, 1943, 1225.
Here's a significant month where we went from Christmas of 43 to January 25th, 44.
So we can also change that over and do things like rather than one month.
What about 10 seconds?
He'll just have to trust that that works.
We can look at it, but I won't spend time in this video doing that.
We could also do things like three quarters
and you might be wondering, okay, that's great.
What if I wanted to do multiple things, like I wanted to add one month and a year or I wanted to add,
I don't know, ten years and 2 seconds.
Well, the easiest way to do that is with two separate date ads.
There's no you can't just chain them together like this 2/2 or something that doesn't work.
However, there's a nice shortcut that I'll show you right now, which brings us to our third way of
doing date arithmetic, which is just with the plus and minus sign.
So if we go back to the docs, you can actually see that rather than using date add or date sub, which
I didn't go over, but it works the same way as date add except it subtracts things.
You can just use a date and then the plus sign and then the interval expression and a date and a minus
sign so we could rewrite what we did.
As if I go back here, this one where we added one month.
All I need to do if I get rid of the date add it's just do birth date time plus sign interval one month.
As you can see there, it still works.
Or I could do that.
Subtract five months.
And this takes us back five months, five months and five months.
All right.
So the only other thing that'll show is that you can chain them together this way so that you could
do something like take a date time and add interval 15 month plus interval ten.
Let's do let's do ours.
And if you take a look, we're not only adding 15 months, but if you look at the time portion, we
went from 10 to 20 4 to 14, 19 to 5.
So we're adding 10 hours successfully.
So this is the this is my preferred way for doing date math.
I will say, though, it's not that often that you're doing things like adding 15 months or adding 10
hours.
Often, more often than not.
I'm actually using date diff and finding how long ago things were relative to now relative to current
time.
So that's pretty much it for date math at this point.
The last thing that we're going to talk about in the next video is how we set up timestamps.
So a new topic, how do we automatically store when a row is updated, what time it was updated at?
So we'll see that in just a sec.