1
00:00:00,210 --> 00:00:00,690
All right.

2
00:00:00,690 --> 00:00:01,470
Welcome back.

3
00:00:01,680 --> 00:00:04,820
So in this video, we're going to talk about date math.

4
00:00:04,830 --> 00:00:11,010
How do we add dates, subtract them and figure out how many days are between two dates, for example,

5
00:00:11,460 --> 00:00:20,070
or how many minutes or take a current date and add 10 minutes into the future or something like that.

6
00:00:20,820 --> 00:00:26,970
It's a pretty common thing to do, especially when you're dealing with comments or blog posts or some

7
00:00:26,970 --> 00:00:34,290
sort of information where it makes sense to display how long ago something was created on Facebook.

8
00:00:34,290 --> 00:00:38,400
I think I've been over this before, but Facebook will tell you this.

9
00:00:38,550 --> 00:00:42,760
You know, you commented on this 10 minutes ago and then you come back the next day.

10
00:00:42,780 --> 00:00:46,170
We'll say one day ago and then a week later you'll say a week ago.

11
00:00:46,170 --> 00:00:51,510
And then at some point it transitions over just to the date, we'll say December 5th or something.

12
00:00:52,470 --> 00:00:58,320
So you can accomplish that by using some of the tools I'm going to show you in this video.

13
00:00:58,890 --> 00:01:03,510
Now, we're not going to spend a ton of time on date math because it's not the most exciting thing.

14
00:01:04,170 --> 00:01:06,570
Also, date math sort of sounds like a.

15
00:01:07,460 --> 00:01:10,970
You know, like at the end of a date, fighting for the check.

16
00:01:11,000 --> 00:01:13,130
Figuring out who pays for what.

17
00:01:14,130 --> 00:01:14,880
Anyways.

18
00:01:14,880 --> 00:01:17,070
Yeah, it sounds like a bad band name or something.

19
00:01:17,340 --> 00:01:18,360
Date math though.

20
00:01:18,360 --> 00:01:22,410
In our context, there's two main functions we're going to talk about.

21
00:01:22,410 --> 00:01:23,610
And the first one is pretty simple.

22
00:01:23,610 --> 00:01:24,870
It's called date diff.

23
00:01:25,500 --> 00:01:28,890
No underscore, it's just date diff stands for date difference.

24
00:01:28,890 --> 00:01:32,130
And what it will do is it takes two dates.

25
00:01:32,700 --> 00:01:33,840
So here's an example.

26
00:01:33,840 --> 00:01:41,670
One, a date time, which I should highlight pretty much everything in my SQL that has date in the name

27
00:01:41,670 --> 00:01:43,410
will also accept date time.

28
00:01:44,160 --> 00:01:50,070
So here's a date a date time excuse me, and then here's a date.

29
00:01:50,250 --> 00:01:54,390
And when you subtract them, it turns out they're one day apart.

30
00:01:54,390 --> 00:01:57,660
So this always tells you the number of days.

31
00:01:58,080 --> 00:02:01,350
So it's not going to give you minutes or seconds or months or something.

32
00:02:01,350 --> 00:02:02,160
It gives you days.

33
00:02:02,160 --> 00:02:05,100
And then if you wanted to, you could turn that into months or something.

34
00:02:05,760 --> 00:02:09,750
However, it's just it's primary purpose is to give you the number of days.

35
00:02:09,750 --> 00:02:10,919
So let's try it.

36
00:02:11,610 --> 00:02:13,710
We could do something like this.

37
00:02:14,430 --> 00:02:18,900
Let's work with our star from people.

38
00:02:18,930 --> 00:02:23,730
Let's work with that and let's figure out how many days ago each one of these was.

39
00:02:23,730 --> 00:02:30,210
Each person here was born, so it would be something like select date diff.

40
00:02:31,690 --> 00:02:35,170
And then we want to take now.

41
00:02:36,530 --> 00:02:37,850
And subtract.

42
00:02:38,820 --> 00:02:39,450
Birth.

43
00:02:40,140 --> 00:02:40,830
Date.

44
00:02:42,090 --> 00:02:44,280
Hopefully that makes sense we're taking now.

45
00:02:44,280 --> 00:02:45,800
We could also do curb date.

46
00:02:45,810 --> 00:02:51,030
It wouldn't make a difference because we're not working with a time part of now but I like now it's

47
00:02:51,030 --> 00:02:53,550
a short easy to remember thing.

48
00:02:53,970 --> 00:03:03,090
So select date diff now or comma birth date so that will take current date, subtract birth date and

49
00:03:03,090 --> 00:03:04,740
find the number of days.

50
00:03:05,970 --> 00:03:06,900
Let's try it.

51
00:03:09,750 --> 00:03:12,900
So let's make this a little bit clearer.

52
00:03:12,930 --> 00:03:20,340
Let's do select date diff and we'll also display name and birth date.

53
00:03:22,460 --> 00:03:23,210
Here we go.

54
00:03:23,990 --> 00:03:26,450
So, Padma, born in 1983.

55
00:03:26,750 --> 00:03:29,090
12,215 days ago.

56
00:03:29,120 --> 00:03:31,010
Larry born the longest ago.

57
00:03:31,010 --> 00:03:35,990
1943 is 26,781 days old.

58
00:03:36,440 --> 00:03:38,510
And then toaster was just born.

59
00:03:38,520 --> 00:03:40,940
Today is zero days old.

60
00:03:41,450 --> 00:03:41,930
All right.

61
00:03:41,930 --> 00:03:43,850
So that's all there is to date diff.

62
00:03:43,880 --> 00:03:49,730
The next thing we're going to take a look at is date ad and there's a little bit more to discuss there.

63
00:03:49,940 --> 00:03:55,040
If we just look to the docs date add conveniently right below date diff.

64
00:03:56,590 --> 00:04:02,290
We get another table here like we did when we had date format.

65
00:04:02,950 --> 00:04:04,210
There's a bit to discuss.

66
00:04:04,210 --> 00:04:06,160
So what this allows us to do.

67
00:04:06,190 --> 00:04:09,880
It is kind of the bread and butter for date arithmetic.

68
00:04:10,000 --> 00:04:12,400
So we specify a date.

69
00:04:13,060 --> 00:04:16,120
So date add and there's also a date sub for subtract.

70
00:04:16,120 --> 00:04:17,970
But we're just going to work with date add for now.

71
00:04:17,980 --> 00:04:25,390
So we give it a date or a date time and then a comma and then this sort of weird syntax that basically

72
00:04:25,390 --> 00:04:27,130
specifies what we want to add.

73
00:04:27,160 --> 00:04:32,710
Do we want to add ten days, one year, four quarters or three fourths?

74
00:04:32,710 --> 00:04:36,640
How about 18 months, ten microseconds?

75
00:04:36,640 --> 00:04:37,600
Whatever it is?

76
00:04:38,410 --> 00:04:46,210
The way that we specify is using the interval keyword and then we pair it with one of these units and

77
00:04:46,210 --> 00:04:48,010
then put it all together.

78
00:04:48,010 --> 00:04:49,900
It looks something like this.

79
00:04:51,000 --> 00:04:57,300
So if we have this date time or actually let's take a look at this example here, date add here's a

80
00:04:57,300 --> 00:04:58,080
date time.

81
00:04:58,530 --> 00:05:04,680
And if you want to add 1/2 to it, you have to say interval and then second, which is the unit and

82
00:05:04,680 --> 00:05:05,880
then how many seconds?

83
00:05:06,120 --> 00:05:09,390
So it follows that format interval one day.

84
00:05:10,130 --> 00:05:13,540
And then it gets kind of weird when there's things like minute second.

85
00:05:13,550 --> 00:05:15,590
So this will do one minute, 1/2.

86
00:05:16,100 --> 00:05:18,170
Add that to this date time.

87
00:05:18,800 --> 00:05:20,170
Pretty nuanced thing.

88
00:05:20,180 --> 00:05:24,770
I've never had to use these ever, but good to know that they exist.

89
00:05:25,130 --> 00:05:26,600
So let's give it a shot.

90
00:05:26,630 --> 00:05:27,830
Let's work with.

91
00:05:28,670 --> 00:05:29,540
Let's do.

92
00:05:30,020 --> 00:05:30,670
Why not?

93
00:05:30,680 --> 00:05:31,880
Let's do months.

94
00:05:32,900 --> 00:05:34,460
So we'll go here.

95
00:05:34,610 --> 00:05:38,960
We'll do a select and let's do date time this time.

96
00:05:38,960 --> 00:05:42,400
So it's like birth date time from people.

97
00:05:42,410 --> 00:05:44,060
Just take a look at what we have.

98
00:05:44,540 --> 00:05:45,200
All right.

99
00:05:45,380 --> 00:05:53,960
And now let's try also doing a date add birth date time and we want to add one month.

100
00:05:53,960 --> 00:05:57,740
So we have to say interval one.

101
00:05:59,410 --> 00:06:03,310
And if you can't remember, you can go over here a month.

102
00:06:03,340 --> 00:06:04,510
It's pretty straightforward.

103
00:06:06,230 --> 00:06:06,830
Okay.

104
00:06:07,490 --> 00:06:18,580
So as you can see, here we go from 1983, 1111 to 1983, 1211 or here, 1943, 1225.

105
00:06:18,590 --> 00:06:28,040
Here's a significant month where we went from Christmas of 43 to January 25th, 44.

106
00:06:28,880 --> 00:06:33,420
So we can also change that over and do things like rather than one month.

107
00:06:33,440 --> 00:06:36,350
What about 10 seconds?

108
00:06:38,140 --> 00:06:39,750
He'll just have to trust that that works.

109
00:06:39,760 --> 00:06:43,000
We can look at it, but I won't spend time in this video doing that.

110
00:06:43,510 --> 00:06:47,680
We could also do things like three quarters

111
00:06:50,320 --> 00:06:52,680
and you might be wondering, okay, that's great.

112
00:06:52,690 --> 00:07:02,110
What if I wanted to do multiple things, like I wanted to add one month and a year or I wanted to add,

113
00:07:02,200 --> 00:07:04,960
I don't know, ten years and 2 seconds.

114
00:07:05,680 --> 00:07:09,280
Well, the easiest way to do that is with two separate date ads.

115
00:07:09,460 --> 00:07:16,310
There's no you can't just chain them together like this 2/2 or something that doesn't work.

116
00:07:16,330 --> 00:07:21,940
However, there's a nice shortcut that I'll show you right now, which brings us to our third way of

117
00:07:21,940 --> 00:07:26,170
doing date arithmetic, which is just with the plus and minus sign.

118
00:07:26,350 --> 00:07:33,010
So if we go back to the docs, you can actually see that rather than using date add or date sub, which

119
00:07:33,010 --> 00:07:38,170
I didn't go over, but it works the same way as date add except it subtracts things.

120
00:07:38,980 --> 00:07:44,680
You can just use a date and then the plus sign and then the interval expression and a date and a minus

121
00:07:44,680 --> 00:07:47,860
sign so we could rewrite what we did.

122
00:07:48,310 --> 00:07:53,050
As if I go back here, this one where we added one month.

123
00:07:54,130 --> 00:08:03,490
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.

124
00:08:05,620 --> 00:08:07,570
As you can see there, it still works.

125
00:08:08,830 --> 00:08:11,140
Or I could do that.

126
00:08:11,140 --> 00:08:13,960
Subtract five months.

127
00:08:18,390 --> 00:08:23,490
And this takes us back five months, five months and five months.

128
00:08:26,180 --> 00:08:26,990
All right.

129
00:08:26,990 --> 00:08:31,520
So the only other thing that'll show is that you can chain them together this way so that you could

130
00:08:31,520 --> 00:08:44,480
do something like take a date time and add interval 15 month plus interval ten.

131
00:08:45,020 --> 00:08:46,640
Let's do let's do ours.

132
00:08:48,590 --> 00:08:54,350
And if you take a look, we're not only adding 15 months, but if you look at the time portion, we

133
00:08:54,350 --> 00:08:59,000
went from 10 to 20 4 to 14, 19 to 5.

134
00:08:59,240 --> 00:09:01,370
So we're adding 10 hours successfully.

135
00:09:02,030 --> 00:09:06,620
So this is the this is my preferred way for doing date math.

136
00:09:06,650 --> 00:09:12,170
I will say, though, it's not that often that you're doing things like adding 15 months or adding 10

137
00:09:12,170 --> 00:09:12,560
hours.

138
00:09:12,710 --> 00:09:15,110
Often, more often than not.

139
00:09:15,290 --> 00:09:21,890
I'm actually using date diff and finding how long ago things were relative to now relative to current

140
00:09:21,890 --> 00:09:22,400
time.

141
00:09:23,360 --> 00:09:26,000
So that's pretty much it for date math at this point.

142
00:09:26,660 --> 00:09:30,980
The last thing that we're going to talk about in the next video is how we set up timestamps.

143
00:09:30,980 --> 00:09:38,570
So a new topic, how do we automatically store when a row is updated, what time it was updated at?

144
00:09:38,690 --> 00:09:40,190
So we'll see that in just a sec.