WEBVTT 00:00.180 --> 00:00.690 It right. 00:00.690 --> 00:01.550 Welcome back. 00:01.830 --> 00:04.900 So in this video we're going to talk about date math. 00:04.950 --> 00:11.760 How do we add dates subtract them figure out how many days are between two dates for example or how 00:11.760 --> 00:20.320 many minutes or take a current date and add you know 10 minutes into the future or something like that. 00:20.880 --> 00:26.940 It's a pretty common thing to do especially when you're dealing with comments or blog posts or some 00:26.940 --> 00:34.310 sort of information where it makes sense to display how long ago something was created on Facebook. 00:34.310 --> 00:38.440 I think I've been over this before but Facebook will tell you this. 00:38.470 --> 00:43.850 You know you commented on this 10 minutes ago and then you come back the next day or say one day ago 00:44.100 --> 00:46.170 and then a week later I'll say a week ago. 00:46.350 --> 00:51.880 And then at some point it transitions over just to the date you will say December 5th or something. 00:52.650 --> 00:58.970 So you can accomplish that by using some of the tools I'm going to show you in this video. 00:59.010 --> 01:03.890 Now we're not going to spend a ton of time on date math because it's not the most exciting thing. 01:04.290 --> 01:09.620 Also date math sort of sounds like you know like at the end of a date. 01:09.690 --> 01:10.890 Fighting for the check. 01:11.090 --> 01:14.920 Figuring out who pays for what anyways. 01:14.940 --> 01:17.370 You know it sounds like a bad name or something. 01:17.460 --> 01:22.390 Date math though in our context there's two main functions we're going to talk about. 01:22.410 --> 01:23.610 And the first one is pretty simple. 01:23.610 --> 01:28.980 It's called Dith no underscores just date diff stands for date difference. 01:28.980 --> 01:32.740 And what it will do is it takes two dates. 01:32.820 --> 01:37.580 So here's an example want a date time which I should highlight. 01:37.680 --> 01:44.220 Pretty much everything in my as well that has a date in the name will also except date time. 01:44.330 --> 01:52.110 So OK here's a date the date time excuse me and then here's a date and when you subtract them. 01:52.270 --> 01:54.370 Turns out they're one day apart. 01:54.570 --> 01:57.770 So this always tells you the number of days. 01:58.170 --> 02:02.170 So it's not going to give you minutes or seconds or months or something gives you days. 02:02.190 --> 02:05.720 And then if you wanted to you could turn that into months or something. 02:05.820 --> 02:09.860 However it's just its primary purpose is to give you the number of days. 02:09.870 --> 02:11.580 So let's try it. 02:11.760 --> 02:14.280 We could do something like this. 02:14.520 --> 02:22.980 Let's work with our star from people let's work with that and let's figure out how many days ago each 02:22.980 --> 02:25.380 one of these words each person here was born. 02:25.710 --> 02:31.590 So it would be something like select date dif. 02:31.810 --> 02:37.570 And then we want to take it now and subtract 02:42.050 --> 02:43.890 hopefully that makes sense for taking. 02:43.890 --> 02:45.800 Now we could also do curred date. 02:45.800 --> 02:49.070 It wouldn't make a difference because we're not working with the time part of now. 02:49.080 --> 02:53.720 But I like now it's short easy to remember thing. 02:53.920 --> 02:58.240 So so like date diff now or comma birth date. 02:58.260 --> 03:05.090 So that will take current date subtract birth date find the number of days. 03:06.000 --> 03:07.400 Let's try it. 03:08.120 --> 03:08.900 OK. 03:09.840 --> 03:12.980 So let's make this a little bit clearer. 03:12.990 --> 03:22.490 Let's do so like date def And we'll also display name and birth date. 03:22.500 --> 03:23.470 Here we go. 03:24.030 --> 03:29.740 So padrón born in 1983 12000 215 days ago Larry. 03:29.770 --> 03:41.110 For the longest to go 1943 is 26000 781 days old and then toaster was just born today is zero days old. 03:41.490 --> 03:41.900 All right. 03:42.000 --> 03:43.290 So that's all there is to date. 03:43.360 --> 03:49.100 If the next thing we're going to take a look at is date add and there's a little bit more to discuss 03:49.110 --> 03:50.020 there. 03:50.070 --> 03:56.390 We just looked at the docs date and conveniently right below date death. 03:56.590 --> 04:02.490 We get another table here like we did when we had a date format. 04:02.960 --> 04:04.210 There's a bit to discuss. 04:04.370 --> 04:09.840 So what this allows us to do it is kind of the bread and butter for date arithmetic. 04:10.160 --> 04:16.820 So we specify a date so date add and there's also a date sub for subtract but we're just going to work 04:16.820 --> 04:17.960 with date ad for now. 04:18.140 --> 04:25.850 So we give it a date or date time and then a comma and then this sort of weird syntax it basically specifies 04:25.910 --> 04:27.160 what we want to add. 04:27.200 --> 04:32.700 Do we want to add 10 days one year four quarters or three quarters. 04:32.690 --> 04:38.290 How about 18 months 10 microseconds whatever it is. 04:38.480 --> 04:44.570 The way that we specify it is using that interval keyword and then we pair it with one of these units 04:45.860 --> 04:49.830 and then put it all together it looks something like this. 04:51.030 --> 04:56.260 So if we have this date time our actions take a look at this example here. 04:56.620 --> 05:00.780 Here here's a date time and if you want to add one second to it. 05:00.830 --> 05:06.240 You have to say interval and then second which is the unit and then how many seconds. 05:06.240 --> 05:12.720 So it follows that forment interval one day and then it gets kinda weird when there's things like minute 05:12.750 --> 05:13.660 second. 05:13.680 --> 05:16.200 So this will do one minute one second. 05:16.200 --> 05:20.140 Add that to this date time pretty nuanced thing. 05:20.220 --> 05:22.600 I've never had to use these ever. 05:23.070 --> 05:25.220 But good to know that they exist. 05:25.260 --> 05:26.680 So let's give it a shot. 05:26.760 --> 05:30.050 Let's work with let's do. 05:30.060 --> 05:32.590 Why not let's do months. 05:33.030 --> 05:34.490 So we'll go here. 05:34.650 --> 05:39.030 We'll do a select and let's do date time this time. 05:39.030 --> 05:42.350 So it's like birth date time from people. 05:42.510 --> 05:44.570 Just take a look at what we have. 05:44.570 --> 05:45.190 All right. 05:45.530 --> 05:53.970 And now let's try also doing a date add birth date time and we want to add one month. 05:54.060 --> 06:04.790 So we have to say interval 1 and if you can't remember can go over here a month that's pretty straightforward. 06:06.200 --> 06:07.020 OK. 06:07.580 --> 06:18.630 So as you can see here we go from 1983 11 11 293 12 11 or here 1943 12:25. 06:18.710 --> 06:20.750 Here's a significant month. 06:21.020 --> 06:32.390 We went from Christmas of 43 to January 25th 44 so we can also change it over and do things like rather 06:32.390 --> 06:33.460 than one month. 06:33.620 --> 06:36.370 What about 10 seconds. 06:38.080 --> 06:39.810 It was have to trust that works. 06:39.820 --> 06:43.180 We can look at it but I won't spend time in the video doing that. 06:43.600 --> 06:47.400 We could also do things like three quarter 06:50.400 --> 06:57.120 and you might be wondering OK that's great but if I wanted to do multiple things like I wanted to add 06:57.660 --> 07:05.280 one month and a year where I wanted to add I don't know 10 years and two seconds. 07:05.820 --> 07:09.600 Well the easiest way to do that is with two separate date ads. 07:09.660 --> 07:13.380 There's no you can't just chain them together like this. 07:13.470 --> 07:16.440 You know two second thing that doesn't work. 07:16.470 --> 07:22.170 However there's a nice shortcut that I'll show you right now which brings us to our third way of doing 07:22.170 --> 07:26.400 date arithmetic which is just with the plus and minus sign. 07:26.490 --> 07:32.940 So if we go back to the docks you can actually see that rather than using Date add or dates up which 07:33.000 --> 07:38.980 I didn't go over but it works the same way as Date ad except it subtracts things. 07:39.060 --> 07:44.610 You can just use a date and then the plus sign and then the interval expression and a date and a minus 07:44.610 --> 07:45.070 sign. 07:45.210 --> 07:50.600 So we could rewrite what we did as I go back here. 07:51.360 --> 07:54.120 This one more we added one month. 07:54.160 --> 08:03.290 All I need to do if I get rid of the date add is just do birth date time plus sign interval 1 month 08:05.590 --> 08:08.740 as you can see there it still works. 08:08.890 --> 08:13.690 Or I could do that subtract five months 08:18.500 --> 08:23.240 and this takes us back five months five months and five months 08:26.300 --> 08:27.060 right. 08:27.070 --> 08:31.480 So the only other thing that will show is that you can chain them together this way so that you could 08:31.480 --> 08:44.930 do something like take a date time and add interval 15 month plus interval 10. 08:45.020 --> 08:47.600 Let's do two hours. 08:48.700 --> 08:54.670 And if you take a look we're not only adding 15 months but if you look at the time portion we went from 08:54.670 --> 08:58.900 10 to twenty four to 14. 08:59.380 --> 09:02.140 So we're adding 10 hours successfully. 09:02.140 --> 09:03.920 So this is the end. 09:04.090 --> 09:06.760 This is my preferred way for doing date math. 09:06.760 --> 09:12.310 I will say though it's not that often that you're doing things like adding 15 months or adding 10 hours 09:12.820 --> 09:15.260 often more often than not. 09:15.410 --> 09:21.850 I'm actually using Date diff and finding how long ago things were relative to now relative to current 09:21.850 --> 09:22.590 time. 09:22.990 --> 09:23.340 Okay. 09:23.380 --> 09:26.470 So that's pretty much it for date math at this point. 09:26.620 --> 09:32.260 The thing that we're going to talk about in the next video is how we set up timestamps so a new topic. 09:32.260 --> 09:38.600 How do we automatically store when a row is updated what time it was up to do that. 09:38.890 --> 09:40.170 So we'll see that in just a sec.