WEBVTT 00:00.210 --> 00:00.810 All right. 00:00.840 --> 00:07.020 So this is the last bit of new content around dates and date times and times. 00:07.260 --> 00:12.200 I admit this section is a bit of a doozy or a pain or I don't know. 00:12.390 --> 00:14.590 Maybe not the most exciting thing. 00:14.670 --> 00:18.630 With that said this is a really important topic that we're going to discuss here. 00:18.890 --> 00:23.540 And it's something that you'll be doing all the time or probably will be doing all the time. 00:23.550 --> 00:28.460 I do it all the time which is adding in time stamps to our tables. 00:28.860 --> 00:33.420 So when we say time stamps there's two things to unpack. 00:33.420 --> 00:41.640 One is that that's a generic term that just refers to storing information metadata about when something 00:41.640 --> 00:48.630 is created or updated that that sort of a standardized name across programming languages or across just 00:48.630 --> 00:54.900 in programming in general and app development time stamps for to basically saving information about 00:55.230 --> 00:59.120 when you create a post or when you start when you insert something into a table. 00:59.340 --> 01:03.410 However in my as well timestamped is also a data type. 01:03.420 --> 01:09.090 So yes I know it's the last video on the section but I'm actually going to show you another data type 01:10.280 --> 01:11.360 so hang in there. 01:11.690 --> 01:16.400 Fortunately this data type is really simple and there's only one case that I recommend that you ever 01:16.400 --> 01:17.380 use it. 01:17.940 --> 01:18.290 OK. 01:18.290 --> 01:22.660 So there's a page called The date date time and time stamp types are going to ignore date. 01:22.660 --> 01:23.990 We've already talked about that. 01:23.990 --> 01:26.300 Let's compare date time and time stamp. 01:26.330 --> 01:28.150 They both do the same thing. 01:28.340 --> 01:33.370 They store date and time information but there's two differences. 01:34.010 --> 01:40.120 If we read the description date time is used for values that contain both date and time parts. 01:40.120 --> 01:46.610 Yes we know that the time stamp is used for values that contain both date and time parts as well. 01:46.610 --> 01:48.390 So they're identical in that sense. 01:48.620 --> 01:53.430 But the main difference comes down to the range of times that and date they support. 01:53.870 --> 02:00.950 So if we look at date time it goes back to year 1000 and all the way up to year nine thousand nine hundred 02:00.950 --> 02:09.380 ninety nine time stamp on the other hand only goes back to 1970 and up to 2038. 02:09.380 --> 02:15.610 So that's really important to note if you are working with something where you know you're storing dates 02:15.610 --> 02:23.120 that may go back very far like if I'm trying to think of a good example but things before 1970 Certainly 02:23.540 --> 02:28.460 you might have sites where you're storing a birth date and you might have users who are older than that 02:28.460 --> 02:30.850 and they're born in the 60s or the 50s. 02:31.130 --> 02:34.410 So time stamp would not work. 02:34.430 --> 02:40.790 Same thing going forward if you're working with dates and date times where you need things to be further 02:40.790 --> 02:48.260 into the future past 2038 then you're not going to want to use timestamps as well but for what we're 02:48.260 --> 02:55.790 talking about just saving when something is created or when it's most recently updated we don't need 02:55.790 --> 03:02.450 to worry about that being before 1970 or after 2038 because it's right now or when our app is up and 03:02.450 --> 03:03.190 running. 03:03.410 --> 03:04.680 You know now is relative. 03:04.700 --> 03:09.680 But basically it's not going to be past 2038 and we're not changing those dates all that we're going 03:09.680 --> 03:11.610 to do is store information. 03:11.900 --> 03:17.660 So time stamp is a good data type to use there and you might be wondering why use it in the first place. 03:17.660 --> 03:20.460 Why does it exist if there's already date time. 03:20.510 --> 03:26.480 The answer like so many things in my school is that it just takes up less space. 03:26.540 --> 03:31.390 I believe it's 4 bytes compared to eight for date time. 03:31.490 --> 03:32.690 Or it might be. 03:33.050 --> 03:36.600 It's a it's double to use date time compared to time stamp. 03:36.890 --> 03:38.300 I believe it's four and eight. 03:38.630 --> 03:40.060 So it's easier. 03:40.210 --> 03:45.290 And if you're doing something you know every time you insert the thing into a row you are updating something 03:45.860 --> 03:48.610 that four versus eight makes a difference. 03:48.620 --> 03:52.100 If you're working with thousands and thousands or millions of records. 03:52.310 --> 03:58.430 So now that we've got that out of the way just to summarize they use to store the same type of data. 03:58.430 --> 04:02.550 This one is smaller timestamps take up less space but they have a more limited range. 04:02.660 --> 04:06.640 So use date time for everything except for what we're about to show you. 04:07.100 --> 04:07.560 OK. 04:07.640 --> 04:10.490 So let's hop over to cloud 9 here we are. 04:10.790 --> 04:16.000 And I have a simple schema going to create a table called comments comments. 04:16.010 --> 04:16.760 It's very simple. 04:16.760 --> 04:22.730 It has two fields the first row is called the first field is called content and it's just the content 04:22.730 --> 04:23.460 of the comment. 04:23.470 --> 04:31.340 You know things like Great article or I made a thousand dollars a week working from home learn how email 04:31.340 --> 04:38.540 me or whatever spam comment there is and then this is the important part created it which is a time 04:38.540 --> 04:38.990 stamp. 04:39.050 --> 04:42.740 First thing to notice it could be Date Time. 04:42.780 --> 04:44.670 Just want to make that clear you can do that. 04:44.690 --> 04:45.430 It will work. 04:45.590 --> 04:52.640 But we're using time stamp because this is basically what it's intended for and it's takes up less space. 04:52.820 --> 04:56.360 So just setting the time stamp on its own is not that useful. 04:56.360 --> 04:57.750 This is the key part. 04:57.830 --> 05:00.240 Setting the default value to be now. 05:00.290 --> 05:06.170 So whenever it's created take that now that current time and put it in time stamp. 05:06.200 --> 05:16.500 So if we run this schema right now and we insert a few things so let's do insert into comments. 05:16.880 --> 05:23.570 All we need to insert is content and what we'll do is just insert our first one. 05:23.570 --> 05:26.060 We're going to do them separately so that they have different time stamps. 05:26.170 --> 05:29.470 So the first one will be L-O. 05:29.660 --> 05:33.500 What a funny article something silly like that. 05:33.650 --> 05:35.010 Then let's do another one. 05:36.700 --> 05:37.180 Like 05:40.530 --> 05:42.820 I found this offensive. 05:43.300 --> 05:43.890 OK. 05:44.450 --> 05:50.090 So now if we do so like star from comments you can see as well. 05:50.100 --> 05:57.480 What a funny article created at Artie Feldon automatically today's date the time 36 seconds compared 05:57.560 --> 05:58.020 that to. 05:58.020 --> 05:59.370 I found this offensive. 05:59.370 --> 06:01.960 Same date same time until we hit second. 06:02.160 --> 06:04.400 It was created 10 seconds later. 06:04.860 --> 06:06.270 So that's created at. 06:06.360 --> 06:09.780 And of course that's really useful. 06:10.140 --> 06:12.550 I would show you how to sort them. 06:12.720 --> 06:14.390 However they're already sorted here. 06:14.510 --> 06:20.110 As you can see because the order I'm putting in or putting them in is sorting them. 06:20.550 --> 06:23.180 But let me just try one more. 06:23.410 --> 06:25.190 Let's do just some jibberish. 06:27.030 --> 06:29.180 Select them all. 06:29.270 --> 06:35.110 Now you can do things like order by created at. 06:35.600 --> 06:37.600 And now we'll order them. 06:37.790 --> 06:39.190 Basically the way they're ordered now. 06:39.200 --> 06:40.670 But we can reverse that 06:43.540 --> 06:45.230 change it to descending. 06:45.230 --> 06:47.490 Now we have the most recent comments up top. 06:48.020 --> 06:50.120 So that's created at using default. 06:50.120 --> 06:56.480 Now along with time stamp but what if we also wanted to store any time a field was changed. 06:56.570 --> 06:58.380 There's an easy way to do that as well. 06:58.460 --> 06:59.700 Just scroll down. 07:00.200 --> 07:01.940 Magically hidden here. 07:02.030 --> 07:08.090 So I sort of calling it created that I made a new field called changed at and the first part is the 07:08.090 --> 07:09.440 same sets. 07:09.460 --> 07:11.830 It's a time stamp and it sets a default value to be. 07:11.980 --> 07:16.020 Now then we get this on update which you haven't seen before. 07:16.280 --> 07:19.740 And frankly this is really the only time I ever use it. 07:20.390 --> 07:23.540 I don't know trying to think if I ever use another capacity. 07:23.540 --> 07:28.630 Pretty much this is the only way I've seen it used on update current time stamp. 07:28.640 --> 07:36.740 So what this is saying is that when the row is changed so if content is changed then update changed 07:36.760 --> 07:39.060 that to be the current time stamp. 07:39.710 --> 07:44.510 So we could put something different yet we can put three although that's a problem because it's not 07:44.510 --> 07:45.250 a time stamp. 07:45.470 --> 07:50.140 But if we made this an and I could do something ridiculous like this. 07:50.150 --> 07:55.160 And every time that comments a row is updated it will change. 07:55.160 --> 07:56.890 Change that to be the number three. 07:56.990 --> 08:01.040 But there's no reason to do that whatsoever so I walked. 08:01.100 --> 08:03.460 So if we run this I called it comments too. 08:03.460 --> 08:10.500 By the way I'm not pointing that out because I think it's original but just so they are aware. 08:10.700 --> 08:12.630 It's a different table if I. 08:12.660 --> 08:16.690 Now let me just use the same comments that I've already run. 08:18.830 --> 08:21.510 And just change it to be inserted to comments to. 08:21.850 --> 08:23.220 Here's the first one. 08:24.020 --> 08:27.460 And our next one will be just. 08:27.570 --> 08:28.640 Ello ello ello. 08:28.750 --> 08:35.150 Well OK and now we do is select a star from comments to. 08:35.330 --> 08:37.350 He is the change that it's filled in. 08:37.360 --> 08:48.200 Because we created them and let's just do one more this time will be I like cats and dogs just to please 08:48.590 --> 08:49.380 everybody. 08:49.490 --> 08:56.630 Unless you're a bird person I guess or you hate animals so they're ordered by default because this is 08:56.660 --> 09:00.380 how we added them and there's no other way that they would be ordered. 09:00.380 --> 09:02.150 But let's try updating one. 09:02.590 --> 09:03.200 OK. 09:03.560 --> 09:07.330 So let's update this jibberish here to say something else. 09:07.340 --> 09:10.430 It's been a while since we've done an update but hopefully remember. 09:10.520 --> 09:22.650 So we're going to want to update comments to set content equal to and let's say this is not gibberish. 09:22.820 --> 09:24.020 Something like that. 09:24.140 --> 09:29.560 And then you need to specify where content is currently. 09:29.990 --> 09:32.330 This mess of jibberish. 09:33.080 --> 09:33.430 OK. 09:33.440 --> 09:35.240 So if I update that. 09:35.240 --> 09:42.750 Notice what you had before 20 30 18 days not going to change 2038. 09:43.100 --> 09:49.490 Now if I do a like star it's changed to 2033 0 to. 09:49.760 --> 09:51.800 And so now if we order things by 09:55.130 --> 09:58.180 what we call it changed at. 09:58.850 --> 10:01.140 You'll see we now have a new order. 10:01.300 --> 10:06.780 And so that's really important to take note of is that if you want to not only track when something 10:06.780 --> 10:12.570 is created but when it was updated you can use this set up here so we have time stamped default. 10:12.570 --> 10:17.550 Now on Update Oh one thing you didn't talk about current time stamp. 10:17.610 --> 10:18.200 You could also put. 10:18.200 --> 10:22.770 Now here the reason I didn't it's just to show you you can also use current time stamp. 10:22.800 --> 10:24.500 It's just another shortcut. 10:24.960 --> 10:30.870 I prefer to use now just because it's like I said a couple of times short simple easy to remember but 10:30.870 --> 10:33.930 current time stamp does exact same thing in this case. 10:34.370 --> 10:34.680 OK. 10:34.710 --> 10:41.340 So this is something a bit of a recipe that will probably return to is worth knowing you'll see me use 10:41.340 --> 10:45.630 it as we go in the scores and some of the other schemas that we create. 10:45.630 --> 10:47.330 Things get more complex. 10:47.490 --> 10:52.340 It's important it's useful to store that metadata about when things are created or when they're updated. 10:52.630 --> 10:54.150 OK we're done.