1
00:00:00,120 --> 00:00:04,590
If you're still with me, we've got one more topic to cover around dates and times.

2
00:00:04,800 --> 00:00:09,210
And this is a really cool, nice feature built into my SQL.

3
00:00:09,360 --> 00:00:18,180
When we create a table, let's say we're making a captions table for social media captions like Instagram

4
00:00:18,180 --> 00:00:19,320
captions, for example.

5
00:00:19,320 --> 00:00:21,390
A user can go and edit the caption afterwards.

6
00:00:21,390 --> 00:00:24,420
They can make the caption and put some text in there.

7
00:00:24,780 --> 00:00:27,540
So let's say that's what our table is.

8
00:00:27,540 --> 00:00:31,200
And the captions table has a text.

9
00:00:31,960 --> 00:00:34,950
The actual caption text, which would be a var char.

10
00:00:34,960 --> 00:00:38,770
Let's just call it 100 or maybe 150 or something.

11
00:00:39,020 --> 00:00:39,660
Okay.

12
00:00:39,670 --> 00:00:45,490
But what I also want to then do is capture the date and the time that it was created at.

13
00:00:45,520 --> 00:00:48,490
So I could do created that just like that.

14
00:00:48,550 --> 00:00:54,160
And then I could say that it is a timestamp or a date time, but I'm going to use timestamp because

15
00:00:54,160 --> 00:00:55,630
it takes up less space.

16
00:00:56,350 --> 00:00:58,240
It's just a little bit more efficient.

17
00:00:58,240 --> 00:01:04,629
So I can say that it is of type timestamp, but what's even fancier is that I can give it a default

18
00:01:04,629 --> 00:01:12,040
value and say the default should be current timestamp just like that and I like to capitalize it.

19
00:01:12,040 --> 00:01:13,450
So that's going to bother me.

20
00:01:15,360 --> 00:01:23,590
And now whenever we make a new caption or new row in this table, we insert something the created that

21
00:01:23,640 --> 00:01:29,280
value will be set to the current time stamp, although it's complaining to me because I have a semicolon

22
00:01:29,280 --> 00:01:29,670
there.

23
00:01:29,820 --> 00:01:32,460
All right, so let's try running this and I'll show you that.

24
00:01:33,630 --> 00:01:36,270
Let's do insert into captions.

25
00:01:37,260 --> 00:01:42,030
And then I'm just going to insert the text I won't bother inserting created at because I want that to

26
00:01:42,030 --> 00:01:46,020
have the automatic current timestamp that it was created at.

27
00:01:46,560 --> 00:01:53,580
So values and then social media caption How about just me and the Girls Chillin?

28
00:01:54,390 --> 00:01:55,890
Something like that.

29
00:01:56,250 --> 00:01:57,990
And let's do one more.

30
00:01:58,470 --> 00:01:59,250
How about.

31
00:02:01,220 --> 00:02:03,260
Beautiful sunset.

32
00:02:04,050 --> 00:02:04,650
Okay.

33
00:02:04,860 --> 00:02:07,920
Now I'm going to select star from captions.

34
00:02:09,060 --> 00:02:13,160
And what do we see created that was automatically populated?

35
00:02:13,170 --> 00:02:17,760
It has the default value of the current timestamp right in there.

36
00:02:18,120 --> 00:02:19,350
So 2022.

37
00:02:19,800 --> 00:02:22,290
October 10th or October 5th, 2022.

38
00:02:22,290 --> 00:02:26,760
And then the time here, 18 0401 and then 18 0409.

39
00:02:27,180 --> 00:02:29,550
And I didn't have to touch it at all.

40
00:02:29,850 --> 00:02:33,780
Now, I'm not only limited to doing this with time stamps, it also works with date time.

41
00:02:33,810 --> 00:02:39,450
If I set this to be current date time and there's actually a whole page on the docs about this automatic

42
00:02:39,450 --> 00:02:44,850
initialization and updating for timestamp and date time and there's a lot more to it.

43
00:02:44,850 --> 00:02:50,750
You can get quite complicated in how you handle null values and missing values and all of that.

44
00:02:50,760 --> 00:02:52,370
We're not going to worry about it for now.

45
00:02:52,380 --> 00:03:00,060
But what's really fancy is this on update current time stamp, What this will do if I set this on any

46
00:03:00,060 --> 00:03:07,260
part of a table, any column, it will update this column to be the current timestamp whenever any other

47
00:03:07,260 --> 00:03:09,300
column is changed in a row.

48
00:03:09,930 --> 00:03:17,580
So if I wanted to keep track of the last time somebody updated their caption, let's do captions too.

49
00:03:17,610 --> 00:03:19,110
So we have both of these.

50
00:03:20,340 --> 00:03:24,520
I could add an updated at field or column.

51
00:03:24,540 --> 00:03:31,020
It's also a time stamp and then I'll add an on update current time stamp.

52
00:03:31,350 --> 00:03:36,390
So I'm not going to give it a default value, although I guess I could have it default to the current

53
00:03:36,390 --> 00:03:39,240
time as well, if that probably makes sense.

54
00:03:39,240 --> 00:03:44,680
When you first create a new caption, do we set updated at as well or do we leave it blank?

55
00:03:44,700 --> 00:03:47,010
It's up to us, I guess, how we want to handle that.

56
00:03:47,010 --> 00:03:53,610
But what is very different between these two is whenever text changes or any other column for a given

57
00:03:53,610 --> 00:03:59,010
row, this one will automatically have the current timestamp set to updated add.

58
00:03:59,220 --> 00:04:08,490
So let's try running that captions too, and let's do an insert into captions to text and we'll have

59
00:04:08,490 --> 00:04:09,450
our values.

60
00:04:09,810 --> 00:04:12,540
I love life.

61
00:04:12,960 --> 00:04:13,860
All right.

62
00:04:14,220 --> 00:04:16,800
Very excited, exuberant caption here.

63
00:04:17,040 --> 00:04:19,680
And if I select star from captions to.

64
00:04:21,120 --> 00:04:24,650
Captions two we see updated at starts.

65
00:04:24,660 --> 00:04:30,150
No, now, I could have also given it a default value of current time stamp, but I didn't.

66
00:04:30,150 --> 00:04:38,550
But now if I change text before I do that just notice created at notice the time 18 655.

67
00:04:38,820 --> 00:04:42,030
Okay now let's update captions to.

68
00:04:44,000 --> 00:04:44,890
Remember the syntax.

69
00:04:44,900 --> 00:04:47,220
It's been a while since we updated set.

70
00:04:47,240 --> 00:04:49,130
Let's set the text to be.

71
00:04:49,640 --> 00:04:54,830
I love life with a bunch of exclamation points like that where?

72
00:04:55,460 --> 00:04:58,250
And then I have to be able to target this one.

73
00:04:58,250 --> 00:04:59,750
Although actually we don't.

74
00:04:59,750 --> 00:05:02,720
If I leave off the where, it's just going to update every row.

75
00:05:02,750 --> 00:05:04,730
We only have one row, so that's fine.

76
00:05:04,850 --> 00:05:07,610
And if I do this, let's see what happened.

77
00:05:07,760 --> 00:05:11,540
Let's select star again from captions to.

78
00:05:13,540 --> 00:05:14,270
Take a look.

79
00:05:14,290 --> 00:05:21,790
Updated at now has a value and it's set to 1808 12 created that is still unchanged.

80
00:05:21,790 --> 00:05:28,960
So any time I change any column in this table updated that will automatically have the new current time

81
00:05:28,960 --> 00:05:29,830
stamp set.

82
00:05:30,250 --> 00:05:35,130
So if I do it one more time, I'm going to just put more exclamation points.

83
00:05:35,140 --> 00:05:42,730
I updated this row, I didn't update updated at explicitly, I only updated text, but it doesn't matter

84
00:05:42,730 --> 00:05:44,110
what I change in a row.

85
00:05:44,110 --> 00:05:45,730
Any column that changes.

86
00:05:45,760 --> 00:05:46,430
Updated.

87
00:05:46,430 --> 00:05:48,340
That will also change.

88
00:05:49,180 --> 00:05:51,250
And we can see that right here.

89
00:05:51,280 --> 00:05:57,910
Now it's 843 for the time instead of 812 30 seconds, 31 seconds have elapsed.

90
00:05:58,180 --> 00:05:59,410
So that's really cool.

91
00:05:59,830 --> 00:06:01,270
And we can do the same thing with date time.

92
00:06:01,270 --> 00:06:04,360
I won't show it to you just because it would be more of the same.

93
00:06:04,360 --> 00:06:07,090
But you replace this with date time.

94
00:06:07,690 --> 00:06:09,620
And remember, there's a distinction.

95
00:06:09,640 --> 00:06:15,880
Timestamps take up less space, but they support a much narrower, narrower range of dates.

96
00:06:15,880 --> 00:06:19,570
1970 to what is it, 2038, I believe.

97
00:06:19,990 --> 00:06:27,880
And date time can go all the way up to year 9999 and all the way down or back in history to year 1000.

98
00:06:28,360 --> 00:06:31,340
And you can do both of these at the same time, right?

99
00:06:31,360 --> 00:06:35,350
You can have a default value like you see right here and an on update.

100
00:06:35,530 --> 00:06:42,070
So if you wanted to do that, you could I just put it into two so I can have a distinct created at and

101
00:06:42,070 --> 00:06:43,510
then updated it.

102
00:06:43,510 --> 00:06:48,940
So now I will have them separate and I'll always know when something was created at and when it was

103
00:06:48,940 --> 00:06:50,380
most recently updated.

104
00:06:50,620 --> 00:06:51,670
You may not need that.

105
00:06:51,670 --> 00:06:56,530
This is not something you need to add on to every table, but if it makes sense, if there's a use case

106
00:06:56,530 --> 00:06:58,660
for it, this is the easiest way to do that.