It right.
Welcome back.
So in this video we're going to talk about date math.
How do we add dates subtract them figure out how many days are between two dates for example or how
many minutes or take a current date and add you know 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 or say one day ago
and then a week later I'll say a week ago.
And then at some point it transitions over just to the date you will 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 you know like at the end of a date.
Fighting for the check.
Figuring out who pays for what anyways.
You know it sounds like a bad 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 Dith no underscores just date diff stands for date difference.
And what it will do is it takes two dates.
So here's an example want a date time which I should highlight.
Pretty much everything in my as well that has a date in the name will also except date time.
So OK here's a date the date time excuse me and then here's a date and when you subtract them.
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 gives you days.
And then if you wanted to you could turn that into months or something.
However it's just its 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 words each person here was born.
So it would be something like select date dif.
And then we want to take it now and subtract
hopefully that makes sense for taking.
Now we could also do curred date.
It wouldn't make a difference because we're not working with the time part of now.
But I like now it's short easy to remember thing.
So so like date diff now or comma birth date.
So that will take current date subtract birth date find the number of days.
Let's try it.
OK.
So let's make this a little bit clearer.
Let's do so like date def And we'll also display name and birth date.
Here we go.
So padrón born in 1983 12000 215 days ago Larry.
For the longest to go 1943 is 26000 781 days old and then toaster was just born today is zero days old.
All right.
So that's all there is to date.
If the next thing we're going to take a look at is date add and there's a little bit more to discuss
there.
We just looked at the docs date and conveniently right below date death.
We get another table here like we did when we had a 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 ad for now.
So we give it a date or date time and then a comma and then this sort of weird syntax it basically specifies
what we want to add.
Do we want to add 10 days one year four quarters or three quarters.
How about 18 months 10 microseconds whatever it is.
The way that we specify it is using that 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 our actions take a look at this example here.
Here here's a date time and if you want to add one second to it.
You have to say interval and then second which is the unit and then how many seconds.
So it follows that forment interval one day and then it gets kinda weird when there's things like minute
second.
So this will do one minute one second.
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 1 and if you can't remember can go over here a month that's pretty straightforward.
OK.
So as you can see here we go from 1983 11 11 293 12 11 or here 1943 12:25.
Here's a significant month.
We went from Christmas of 43 to January 25th 44 so we can also change it over and do things like rather
than one month.
What about 10 seconds.
It was have to trust that works.
We can look at it but I won't spend time in the video doing that.
We could also do things like three quarter
and you might be wondering OK that's great but if I wanted to do multiple things like I wanted to add
one month and a year where I wanted to add I don't know 10 years and two 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.
You know two second thing 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 docks you can actually see that rather than using Date add or dates up which
I didn't go over but it works the same way as Date ad 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 I go back here.
This one more we added one month.
All I need to do if I get rid of the date add is just do birth date time plus sign interval 1 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
right.
So the only other thing that will 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 10.
Let's do two hours.
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 twenty four to 14.
So we're adding 10 hours successfully.
So this is the end.
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.
Okay.
So that's pretty much it for date math at this point.
The 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 up to do that.
So we'll see that in just a sec.