1
00:00:00,240 --> 00:00:00,810
All right.

2
00:00:00,810 --> 00:00:06,330
So this is the last bit of new content around dates and date times and times.

3
00:00:07,140 --> 00:00:13,500
I admit that this section is a bit of a doozy or pain or I don't know, maybe not the most exciting

4
00:00:13,500 --> 00:00:13,950
thing.

5
00:00:14,490 --> 00:00:19,470
With that said, this is a really important topic, what we're going to discuss here, and it's something

6
00:00:19,470 --> 00:00:23,580
that you'll be doing all the time or probably we'll be doing all the time.

7
00:00:23,580 --> 00:00:28,260
I do it all the time, which is adding in timestamps to our tables.

8
00:00:28,740 --> 00:00:33,420
So when we say timestamps, there's two things to unpack.

9
00:00:33,420 --> 00:00:41,670
One is that that's a generic term that just refers to storing information metadata about when something

10
00:00:41,670 --> 00:00:43,050
is created or updated.

11
00:00:43,050 --> 00:00:49,320
AT That's sort of a standardized name across programming languages or across just in programming in

12
00:00:49,320 --> 00:00:55,830
general, in app development, timestamps refer to basically saving information about when you create

13
00:00:55,830 --> 00:00:59,150
a post or when you start when you insert something into a table.

14
00:00:59,160 --> 00:01:03,300
However, in my SQL timestamp is also a data type.

15
00:01:03,300 --> 00:01:08,850
So yes, I know it's the last video in this section, but I'm actually going to show you another data

16
00:01:08,850 --> 00:01:09,390
type.

17
00:01:10,160 --> 00:01:11,180
So hang in there.

18
00:01:11,660 --> 00:01:16,250
Fortunately, this data type is really simple, and there's only one case that I recommend that you

19
00:01:16,250 --> 00:01:17,060
ever use it.

20
00:01:18,320 --> 00:01:21,730
So there's a page called the Date Date, Time and time stamp types.

21
00:01:21,740 --> 00:01:22,670
We're going to ignore date.

22
00:01:22,670 --> 00:01:23,920
We've already talked about that.

23
00:01:23,930 --> 00:01:26,140
Let's compare date, time and time stamp.

24
00:01:26,150 --> 00:01:28,250
They both do the same thing.

25
00:01:28,250 --> 00:01:31,310
They store, date and time information.

26
00:01:31,310 --> 00:01:33,260
But there's two differences.

27
00:01:33,980 --> 00:01:40,010
If we read the description, date time is used for values that contain both date and time parts.

28
00:01:40,010 --> 00:01:46,220
Yes, we know that the time stamp is used for values that contain both date and time parts as well.

29
00:01:46,520 --> 00:01:48,230
So they're identical in that sense.

30
00:01:48,500 --> 00:01:53,180
But the main difference comes down to the range of times and dates they support.

31
00:01:53,780 --> 00:02:01,850
So if we look at date time, it goes back to year 1000 and all the way up to year 9999.

32
00:02:02,720 --> 00:02:08,300
Time stamp, on the other hand, only goes back to 1970 and up to 2038.

33
00:02:09,259 --> 00:02:11,540
So that's really important to note.

34
00:02:11,660 --> 00:02:17,420
If you are working with something where you're storing dates that may go back very far.

35
00:02:18,560 --> 00:02:24,350
Like I'm trying to think of a good example, but things before 1970, certainly you might have sites

36
00:02:24,350 --> 00:02:29,030
where you're storing a birth date and you might have users who are older than that and they're born

37
00:02:29,030 --> 00:02:30,590
in the sixties or the fifties.

38
00:02:31,070 --> 00:02:33,170
So time stamp would not work.

39
00:02:34,370 --> 00:02:35,900
Same thing going forward.

40
00:02:35,900 --> 00:02:42,050
If you're working with dates and date times where you need things to be further into the future past

41
00:02:42,050 --> 00:02:46,400
2038, then you're not going to want to use time stamps as well.

42
00:02:46,670 --> 00:02:54,170
But for what we're talking about, just saving when something is created or when it's most recently

43
00:02:54,170 --> 00:03:01,640
updated, we don't need to worry about that being before 1970 or after 2038 because it's right now or

44
00:03:01,640 --> 00:03:07,610
when our app is up and running now is relative, but basically it's not going to be past 2038 and we're

45
00:03:07,610 --> 00:03:09,020
not changing those dates.

46
00:03:09,020 --> 00:03:11,570
All that we're going to do is store information.

47
00:03:11,840 --> 00:03:17,690
So timestamp is a good data type to use there and you might be wondering why use it in the first place.

48
00:03:17,690 --> 00:03:20,320
Why does it exist if there's already date time?

49
00:03:20,330 --> 00:03:26,120
The answer, like so many things in my SQL, is that it's just takes up less space.

50
00:03:26,480 --> 00:03:31,010
I believe it's four bytes compared to eight for date time.

51
00:03:31,460 --> 00:03:38,270
Or it might be it's double to use date time compared to timestamp, I believe it's four and eight.

52
00:03:38,570 --> 00:03:40,100
So it's easier.

53
00:03:40,100 --> 00:03:45,560
And if you're doing something every time you insert something into a row, you are updating something

54
00:03:45,680 --> 00:03:47,360
that four versus eight.

55
00:03:47,780 --> 00:03:51,530
It makes a difference if you're working with thousands and thousands or millions of records.

56
00:03:52,190 --> 00:03:57,410
So now that we've got that out of the way, just to summarize, they use they store the same type of

57
00:03:57,410 --> 00:03:57,950
data.

58
00:03:58,280 --> 00:04:02,540
This one is smaller time stamps take up less space, but they have a more limited range.

59
00:04:02,540 --> 00:04:06,470
So use date time for everything except for what I'm about to show you.

60
00:04:07,550 --> 00:04:09,260
So let's hop over to Cloud nine.

61
00:04:09,710 --> 00:04:10,430
Here we are.

62
00:04:10,640 --> 00:04:12,470
And I have a simple schema.

63
00:04:12,500 --> 00:04:15,080
Going to create a table called comments.

64
00:04:15,530 --> 00:04:16,730
Comments is very simple.

65
00:04:16,730 --> 00:04:18,230
It has two fields.

66
00:04:18,230 --> 00:04:21,079
The first row is called or the first field is called content.

67
00:04:21,380 --> 00:04:23,420
And it's just the content of the comment.

68
00:04:24,050 --> 00:04:32,570
Things like great article or I made a thousand a week working from home, learn how, email me or whatever

69
00:04:32,570 --> 00:04:33,950
spam comment there is.

70
00:04:34,370 --> 00:04:41,210
And then this is the important part created it which is the timestamp first thing to notice it could

71
00:04:41,210 --> 00:04:42,410
be date time.

72
00:04:42,730 --> 00:04:43,670
I just want to make that clear.

73
00:04:43,670 --> 00:04:44,660
You can do that.

74
00:04:44,660 --> 00:04:45,470
It will work.

75
00:04:45,470 --> 00:04:51,410
But we're using timestamp because this is basically what it's intended for and it takes up less space.

76
00:04:52,730 --> 00:04:56,180
So just setting it to timestamp on its own, it's not that useful.

77
00:04:56,210 --> 00:04:57,680
This is the key part.

78
00:04:57,860 --> 00:05:00,260
Setting the default value to be now.

79
00:05:00,260 --> 00:05:05,390
So whenever it's created, take that now that current time and put it in timestamp.

80
00:05:06,080 --> 00:05:16,250
So if we run this schema right now and we insert a few things, so let's do insert into comments.

81
00:05:16,850 --> 00:05:23,560
All we need to insert is content and what we'll do is just insert our first one.

82
00:05:23,570 --> 00:05:26,090
We're going to do them separately so that they have different timestamps.

83
00:05:26,090 --> 00:05:29,000
So the first one will be lol.

84
00:05:29,600 --> 00:05:33,110
What a funny article, something silly like that.

85
00:05:33,530 --> 00:05:35,060
Then let's do another one.

86
00:05:36,670 --> 00:05:37,450
Like

87
00:05:40,510 --> 00:05:42,610
I found this offensive.

88
00:05:44,470 --> 00:05:50,100
So now if we do select star from comments you can see lol.

89
00:05:50,110 --> 00:05:57,050
What a funny article created that already filled in automatically today's date the time 36 seconds.

90
00:05:57,070 --> 00:05:59,270
Compare that to I found this offensive.

91
00:05:59,290 --> 00:06:01,990
Same date, same time until we hit the second.

92
00:06:02,020 --> 00:06:04,030
It was created 10 seconds later.

93
00:06:04,780 --> 00:06:06,280
So that created that.

94
00:06:06,280 --> 00:06:08,920
And of course, that's really useful.

95
00:06:09,970 --> 00:06:12,190
I would show you how to sort them.

96
00:06:12,670 --> 00:06:17,950
However, they're already sorted here, as you can see, because the order I'm putting in or putting

97
00:06:17,950 --> 00:06:19,240
them in is sorting them.

98
00:06:20,410 --> 00:06:22,090
But let me just try one more.

99
00:06:23,440 --> 00:06:25,150
Let's do just some gibberish.

100
00:06:27,080 --> 00:06:28,070
Select them all.

101
00:06:29,240 --> 00:06:38,510
And now we can do things like order, buy, create a debt and that will order them basically the way

102
00:06:38,510 --> 00:06:39,200
they're ordered now.

103
00:06:39,200 --> 00:06:44,990
But we can reverse that, change that to descending.

104
00:06:45,020 --> 00:06:47,300
Now we have the most recent comments up top.

105
00:06:47,930 --> 00:06:51,950
So that's created at using default now along with timestamp.

106
00:06:51,950 --> 00:06:55,640
But what if we also wanted to store any time a field was changed?

107
00:06:56,510 --> 00:06:58,220
There's an easy way to do that as well.

108
00:06:58,340 --> 00:07:01,370
Just scroll down magically hidden here.

109
00:07:01,880 --> 00:07:06,350
So I, instead of calling it created at, I made a new field called Change Debt.

110
00:07:06,650 --> 00:07:09,500
And the first part is the same sets.

111
00:07:09,500 --> 00:07:12,170
It's a time stamp and it sets the default value to be now.

112
00:07:12,320 --> 00:07:15,920
But then we get this on update, which we haven't seen before.

113
00:07:16,100 --> 00:07:21,710
And frankly, this is really the only time I ever use it or I don't know, I'm trying to think of ever

114
00:07:21,710 --> 00:07:24,050
use it in another capacity pretty much.

115
00:07:24,050 --> 00:07:28,580
This is the only way I've seen it used on update current time stamp.

116
00:07:28,580 --> 00:07:36,740
So what this is saying is that when the row is changed, so if content is changed, then update change

117
00:07:36,740 --> 00:07:38,660
debt to be the current timestamp.

118
00:07:39,590 --> 00:07:44,420
So we could put something different here, we could put three, although that's a problem because it's

119
00:07:44,420 --> 00:07:45,320
not a timestamp.

120
00:07:45,320 --> 00:07:50,180
But if we made this an int, I could do something ridiculous like this.

121
00:07:50,180 --> 00:07:56,870
And every time that comments a row is updated it will change change that to be the number three.

122
00:07:56,870 --> 00:07:59,060
But there's no reason to do that whatsoever.

123
00:07:59,060 --> 00:07:59,990
So I won't.

124
00:08:01,010 --> 00:08:04,220
So if we run this I called it comments two, by the way.

125
00:08:06,050 --> 00:08:08,300
I'm not pointing that out because I think it's original.

126
00:08:08,300 --> 00:08:11,810
But just so that you're aware, it's a different table.

127
00:08:11,960 --> 00:08:12,330
If I.

128
00:08:12,350 --> 00:08:16,070
Now, let me just use the same comments that I've already run.

129
00:08:18,790 --> 00:08:21,160
And just change it to be insert into comments too.

130
00:08:21,790 --> 00:08:22,930
So here's the first one.

131
00:08:23,920 --> 00:08:28,060
And our next one will be just lol.

132
00:08:28,060 --> 00:08:29,150
LOL lol.

133
00:08:30,100 --> 00:08:38,169
And now we do a select star from comments to how you can see change that it's filled in because we created

134
00:08:38,169 --> 00:08:38,650
them.

135
00:08:40,490 --> 00:08:42,350
And let's just do one more.

136
00:08:42,890 --> 00:08:44,400
This time will be.

137
00:08:44,420 --> 00:08:49,480
I like cats and dogs just to please everybody.

138
00:08:49,490 --> 00:08:52,520
Unless you're a bird person, I guess, or you hate animals.

139
00:08:53,660 --> 00:08:57,940
So they're ordered by default because this is the order we added them in.

140
00:08:57,950 --> 00:08:59,750
There's no other way that they would be ordered.

141
00:09:00,230 --> 00:09:02,000
But let's try updating one.

142
00:09:03,410 --> 00:09:07,280
So let's update this gibberish here to say something else.

143
00:09:07,310 --> 00:09:10,430
It's been a while since we've done an update, but hopefully you remember.

144
00:09:10,430 --> 00:09:13,610
So we're going to want to update comments to.

145
00:09:14,760 --> 00:09:23,940
Sets content equal to and let's say this is not gibberish or something like that.

146
00:09:23,940 --> 00:09:31,680
And then we need to specify where content is currently this mess of gibberish.

147
00:09:33,450 --> 00:09:34,800
So if I update that.

148
00:09:35,130 --> 00:09:40,040
Notice what we had before 20 3018.

149
00:09:40,050 --> 00:09:41,340
The date is not going to change.

150
00:09:41,460 --> 00:09:42,270
2038.

151
00:09:42,900 --> 00:09:48,180
Now, if I do a select star, it's changed to 2030 302.

152
00:09:49,710 --> 00:09:52,110
And so now if we order things by.

153
00:09:55,110 --> 00:09:57,300
Would we call it change that?

154
00:09:58,800 --> 00:09:59,370
You'll see.

155
00:09:59,370 --> 00:10:00,570
We now have a new order.

156
00:10:01,280 --> 00:10:06,810
And so that's really important to take note of is that if you want to not only track when something

157
00:10:06,810 --> 00:10:10,800
was created, but when it was updated, you can use this set up here.

158
00:10:10,800 --> 00:10:14,700
So we have timestamp default now on update.

159
00:10:14,700 --> 00:10:19,770
Oh, one thing it didn't talk about current timestamp you could also put now here, the reason I didn't

160
00:10:19,770 --> 00:10:22,530
is just to show you you can also use current timestamp.

161
00:10:22,620 --> 00:10:29,280
It's just another shortcut I prefer to use now just because it's like I said a couple of times, short,

162
00:10:29,280 --> 00:10:33,660
simple, easy to remember, but current timestamp does the exact same thing in this case.

163
00:10:34,680 --> 00:10:39,000
So this is something a bit of a recipe that you'll probably return to.

164
00:10:39,570 --> 00:10:45,150
It's worth knowing you'll see me use it as we go in this course and some of the other schemas that we

165
00:10:45,150 --> 00:10:45,630
create.

166
00:10:45,630 --> 00:10:48,360
As things get more complex, it's important.

167
00:10:48,360 --> 00:10:52,290
It's useful to store that metadata about when things are created or when they're updated.

168
00:10:53,700 --> 00:10:54,200
We're done.