1
00:00:00,150 --> 00:00:02,430
So if you're like me, you're probably a little sick of dates.

2
00:00:02,460 --> 00:00:03,600
We're almost done.

3
00:00:03,719 --> 00:00:08,160
We're going to talk about a handful of date math functions that are built in.

4
00:00:08,310 --> 00:00:16,079
So when I say math, I mean things like calculating how many days are between two dates or if we have

5
00:00:16,079 --> 00:00:17,640
somebody's day.

6
00:00:17,640 --> 00:00:22,460
They signed up for a website and we want to know when a year anniversary has passed.

7
00:00:22,470 --> 00:00:27,230
We need to add a year to that date and figure out if we've hit that date or not.

8
00:00:27,240 --> 00:00:34,440
So the first one of these functions I'll show you is called Date Diff Date Difference, and it expects

9
00:00:34,440 --> 00:00:39,390
us to provide two dates, although it also works with date times, it's not really going to work well

10
00:00:39,390 --> 00:00:45,720
with time, but with dates and date times it will subtract the second expression the second date from

11
00:00:45,720 --> 00:00:49,500
the first, and then return to us a number of days.

12
00:00:49,500 --> 00:00:52,470
So it is not going to work with the number of seconds.

13
00:00:52,470 --> 00:00:54,390
It's not going to tell us how many hours.

14
00:00:54,390 --> 00:00:56,460
It just tells us the number of days.

15
00:00:56,850 --> 00:00:58,740
So let's try an example.

16
00:00:59,190 --> 00:01:01,230
Why don't we work with our birth date?

17
00:01:01,230 --> 00:01:07,440
So select birth date from and then what is it, people?

18
00:01:08,490 --> 00:01:10,350
So that's our birth date.

19
00:01:10,380 --> 00:01:13,110
Let's say I want to know how many days ago that was.

20
00:01:13,350 --> 00:01:14,850
Each birth date was.

21
00:01:15,210 --> 00:01:17,070
I know how to get the current date.

22
00:01:17,070 --> 00:01:18,480
Remember, we can do this.

23
00:01:19,580 --> 00:01:21,260
Cur date.

24
00:01:22,750 --> 00:01:24,520
We don't even need to do from people.

25
00:01:24,520 --> 00:01:30,810
But anyway, we're getting the current date for every row and I can subtract the two.

26
00:01:30,820 --> 00:01:41,980
I can do select and then I'll write date diff and then I'll start with curr, date, comma and then

27
00:01:41,980 --> 00:01:43,990
birth date, curr date and birth date.

28
00:01:44,230 --> 00:01:50,440
So for every row in, people take the birth date and subtract that from the current date.

29
00:01:50,440 --> 00:01:56,650
And this is our number of days and it would be nice to also show the original birth date so we can compare

30
00:01:56,650 --> 00:01:59,800
and see if that seems I'm missing a comma there.

31
00:02:00,520 --> 00:02:04,360
So let's see, this is the one I added today.

32
00:02:04,390 --> 00:02:05,950
So zero days ago.

33
00:02:05,980 --> 00:02:07,450
This is from 2020.

34
00:02:07,450 --> 00:02:08,919
I'm recording this in 2022.

35
00:02:08,919 --> 00:02:11,080
So 781 days makes sense.

36
00:02:11,080 --> 00:02:16,660
1985, 13,000 days, 2000, almost 8000 days ago.

37
00:02:16,840 --> 00:02:19,540
So that's working great.

38
00:02:19,570 --> 00:02:25,390
We can do the same thing with date time, by the way, birth date, and we're going to get the same

39
00:02:25,390 --> 00:02:30,100
result because it's just taking the date portion from that date time.

40
00:02:30,220 --> 00:02:36,030
But if I tried to do birth time, as we discussed, it's not really going to work.

41
00:02:36,040 --> 00:02:36,970
It doesn't give us an error.

42
00:02:36,970 --> 00:02:38,170
It just doesn't work.

43
00:02:38,170 --> 00:02:43,090
We get zero because there's no date information included in birth time.

44
00:02:43,090 --> 00:02:44,910
It's only a time information.

45
00:02:45,010 --> 00:02:46,060
It's only time.

46
00:02:46,260 --> 00:02:47,950
Okay, so that one is date diff.

47
00:02:47,950 --> 00:02:49,150
Then we move on to one.

48
00:02:49,150 --> 00:02:53,950
That's it's a little trickier, but it's also more powerful date add.

49
00:02:53,950 --> 00:02:57,220
And along with that date, sub four subtract.

50
00:02:57,220 --> 00:03:02,500
Now you might think date sub that sounds just like date diff, but they actually serve different purposes.

51
00:03:02,500 --> 00:03:09,040
Date sub and date add allow us to take some date and then do math with some interval.

52
00:03:09,040 --> 00:03:09,910
It could be a day.

53
00:03:09,910 --> 00:03:13,450
It could be a year, it could be a second, it could be 50 seconds.

54
00:03:13,450 --> 00:03:19,420
We have a lot more flexibility where date diff just takes two dates and subtracts them and tells us

55
00:03:19,420 --> 00:03:21,250
the number of days between them.

56
00:03:21,550 --> 00:03:26,290
So with date ADD, I take a date let's say now, right now.

57
00:03:26,290 --> 00:03:29,080
And then I can use this weird syntax.

58
00:03:29,080 --> 00:03:31,810
We write interval and then an expression.

59
00:03:31,810 --> 00:03:38,440
This is called a temporal interval and it looks something like this interval one day interval, one

60
00:03:38,440 --> 00:03:41,080
year, whatever I want, I use this syntax.

61
00:03:41,080 --> 00:03:45,400
So interval has to go first and then whatever my expression and unit is.

62
00:03:45,490 --> 00:03:48,400
So this right here, this date, what is this?

63
00:03:48,400 --> 00:03:53,260
May 1st of 2018, You add one day, which is what we're doing here.

64
00:03:53,260 --> 00:03:55,630
We end up with May 2nd, 2018.

65
00:03:55,930 --> 00:03:58,600
So let's try it over here.

66
00:03:58,630 --> 00:04:08,980
Let's just work with Curr date again and I'm going to do a date add from cur date.

67
00:04:08,980 --> 00:04:10,090
I don't know, what should we add?

68
00:04:10,090 --> 00:04:13,960
How about interval of one year?

69
00:04:15,310 --> 00:04:19,240
And the result is 2023 ten five.

70
00:04:19,240 --> 00:04:19,410
Right.

71
00:04:19,450 --> 00:04:32,320
So October 5th of 2023 or I could do interval of 12 years or interval of one month and we get 1105 2022

72
00:04:32,320 --> 00:04:33,580
I don't know, I'm going in that order.

73
00:04:33,580 --> 00:04:38,260
That's just the American and me starting with the date, then with the month, then date, then year.

74
00:04:38,260 --> 00:04:40,180
But it works, right?

75
00:04:40,180 --> 00:04:42,850
We get one month further from where we are right now.

76
00:04:42,850 --> 00:04:48,040
We also have date sub so for subtract date sub.

77
00:04:49,640 --> 00:04:50,800
Just like that.

78
00:04:50,810 --> 00:04:54,530
And now we end up with 2020 20905.

79
00:04:54,560 --> 00:04:57,770
A month ago I was still working on this course a month ago.

80
00:04:58,370 --> 00:05:02,660
And we can do other things like work with seconds if we wanted to.

81
00:05:02,660 --> 00:05:05,270
We can work with minutes against day seconds.

82
00:05:05,270 --> 00:05:08,720
We can even work with quarters like quarters of the year.

83
00:05:09,140 --> 00:05:12,350
But for the sake of time, I'm not going to dive too much into that.

84
00:05:12,350 --> 00:05:17,600
So we have date, add and date, so we have to use that interval syntax.

85
00:05:17,600 --> 00:05:20,480
I guess I'll do one more example, this time working with our table.

86
00:05:20,480 --> 00:05:24,140
Let's take birth date for every person.

87
00:05:25,910 --> 00:05:30,350
And find out the date where they would have their 18th birthday.

88
00:05:30,350 --> 00:05:35,390
Let's say in the US, they could vote after this date, so we would do date add.

89
00:05:36,470 --> 00:05:40,250
And then we'll take their birth date and then we'll do interval.

90
00:05:40,400 --> 00:05:42,020
18 years.

91
00:05:42,740 --> 00:05:45,100
It's year, not year interval.

92
00:05:45,110 --> 00:05:46,130
18 year.

93
00:05:46,460 --> 00:05:52,900
Och And we can see right there, 2018, if you're born in 2000 you would be 18 and 2018.

94
00:05:52,910 --> 00:05:53,960
You're born this year.

95
00:05:53,960 --> 00:05:56,980
In 2022, you've got to wait until 2040.

96
00:05:56,990 --> 00:06:02,230
Geez, I feel an old now if you're born in 85 2003.

97
00:06:02,240 --> 00:06:03,290
So there we are.

98
00:06:03,290 --> 00:06:08,450
We can do some basic date math by adding an interval or subtracting an interval from a date.

99
00:06:08,450 --> 00:06:10,940
And we can do the same thing with date times.

100
00:06:11,420 --> 00:06:13,910
And we can also do the same thing with time.

101
00:06:14,030 --> 00:06:20,030
However, to work with just times, we have the same two functions for time.

102
00:06:20,030 --> 00:06:21,560
So time diff.

103
00:06:21,890 --> 00:06:30,360
And then we also have if we go up to the top, it's alphabetized ad time and at the bottom somewhere

104
00:06:30,360 --> 00:06:35,150
it will have sub time so we can find the difference between times.

105
00:06:35,150 --> 00:06:40,640
Because when we tried to find the difference between times using a dif date that didn't work.

106
00:06:40,880 --> 00:06:44,450
It's only going to work with dates because it returns a number of days.

107
00:06:44,450 --> 00:06:51,080
So if we work with time diff, it is going to return to us a value expressed as a time value.

108
00:06:51,080 --> 00:06:53,870
So we'll take two different times and subtract them.

109
00:06:53,870 --> 00:06:55,610
So let's try it over here in the shell.

110
00:06:55,610 --> 00:07:02,780
Let's start with the current time and maybe I'll select the difference of time diff.

111
00:07:02,780 --> 00:07:04,670
We're going to have to come up with some contrived thing.

112
00:07:04,670 --> 00:07:08,000
But how about between now and when I woke up?

113
00:07:08,000 --> 00:07:11,240
So cur time and then comma, I woke up.

114
00:07:11,240 --> 00:07:12,890
I'll use a string here.

115
00:07:13,860 --> 00:07:16,740
Seven in the morning just like that.

116
00:07:17,220 --> 00:07:22,620
And it tells me I've been up for 10 hours, 32 minutes and 13 seconds as of right now.

117
00:07:22,920 --> 00:07:25,140
So that's how we can do a time difference.

118
00:07:25,260 --> 00:07:32,100
Remember four dates we use date diff and that returns to us a value in a number of days time diff returns

119
00:07:32,100 --> 00:07:39,270
to us an actual time interval, and that's enough of the actual date functions for doing some basic

120
00:07:39,270 --> 00:07:39,790
math.

121
00:07:39,810 --> 00:07:45,210
The last thing I want to show you is that we can actually do math using the plus and minus operators

122
00:07:45,210 --> 00:07:45,890
as well.

123
00:07:45,900 --> 00:07:52,580
So if you remember, we can do weird things like select three minus four and use my SQL as a calculator.

124
00:07:52,590 --> 00:07:58,770
That minus sign in this situation is an operator that's working with two numbers, but I can actually

125
00:07:58,770 --> 00:07:59,700
do things like this.

126
00:07:59,700 --> 00:08:04,170
Select the current date time and subtract.

127
00:08:04,980 --> 00:08:13,740
Let's do an interval of 18 years and this is going to be 18 years ago from right now would be back in

128
00:08:13,770 --> 00:08:14,990
2004.

129
00:08:15,000 --> 00:08:15,810
Wow.

130
00:08:15,840 --> 00:08:17,220
Someone was born in 2004.

131
00:08:17,250 --> 00:08:18,300
That'd be 18.

132
00:08:18,930 --> 00:08:20,910
That makes you feel old.

133
00:08:21,360 --> 00:08:22,710
I am old, I guess.

134
00:08:23,010 --> 00:08:26,730
Oh, well, you can see, though, that it works.

135
00:08:26,730 --> 00:08:31,110
I don't need to use a function to do that if I prefer this syntax.

136
00:08:31,140 --> 00:08:35,010
Let's do one more example using the plus sign and our real data.

137
00:08:35,039 --> 00:08:41,130
It's not real data, but from our table, let's select the name and the birth date from people.

138
00:08:41,490 --> 00:08:50,250
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

139
00:08:50,250 --> 00:08:50,850
drink.

140
00:08:51,120 --> 00:08:58,290
And if Bouncer is going to look at each driver's license and add 21 years to see, compare that to the

141
00:08:58,290 --> 00:08:58,860
current date.

142
00:08:58,860 --> 00:09:02,850
So let's see what happens if we add 21 years to birth date.

143
00:09:03,240 --> 00:09:08,910
So we'll do a birth date plus the interval of 21 year.

144
00:09:08,910 --> 00:09:14,760
Just remember it's singular year and there we are, we see 21 years plus.

145
00:09:14,760 --> 00:09:19,770
This date is 20, 21, 12, 25, 21 years plus.

146
00:09:19,770 --> 00:09:20,140
Right.

147
00:09:20,160 --> 00:09:24,240
Today's date, 2022, ten five is all the way in 2043.

148
00:09:24,630 --> 00:09:27,720
And if I wanted to, I can get fancier.

149
00:09:28,860 --> 00:09:30,480
I can do that math.

150
00:09:32,130 --> 00:09:35,190
And then extract, let's say, just the year.

151
00:09:35,220 --> 00:09:37,290
Remember, we have this year function.

152
00:09:37,830 --> 00:09:39,540
So I can do this.

153
00:09:39,630 --> 00:09:45,390
I take the birth date, I add 21 years to it, and then I just take the year out.

154
00:09:47,120 --> 00:09:50,090
And we see 2021, 22,006.

155
00:09:50,090 --> 00:09:51,170
I would even get fancier.

156
00:09:51,170 --> 00:09:54,800
Maybe it's not fancy, but give it an alias like as.

157
00:09:57,020 --> 00:09:57,590
I don't know.

158
00:09:57,590 --> 00:10:01,250
We'll what should we call this will be 21.

159
00:10:04,290 --> 00:10:05,310
And there we are.

160
00:10:05,340 --> 00:10:07,840
This person will be 21 and 2021.

161
00:10:07,860 --> 00:10:11,760
Lulu in 2006, one, 2041.

162
00:10:11,760 --> 00:10:13,270
Hazel 2043.

163
00:10:13,290 --> 00:10:16,830
So if you're like me, you're probably sick of this date math stuff.

164
00:10:16,830 --> 00:10:18,480
But it is good to know how this works.

165
00:10:18,690 --> 00:10:24,720
We can use both this plus syntax or the minus syntax or date add and date sub.

166
00:10:24,720 --> 00:10:31,430
But remember, we definitely need to use this weird interval syntax to express any temporal intervals.

167
00:10:31,440 --> 00:10:35,720
One year, 25 years, one month, two quarters, whatever we want.

168
00:10:35,730 --> 00:10:40,290
There's a whole page on the docs that details the ins and outs of that syntax, but I've shown you the

169
00:10:40,290 --> 00:10:42,330
basics for years, days, months and so on.