1
00:00:00,090 --> 00:00:05,560
So next up, we're going to actually try using date and time and date time in a table.

2
00:00:05,580 --> 00:00:07,640
We're not going to do anything terribly creative.

3
00:00:07,650 --> 00:00:12,000
I'm just going to compare the three so you can see how they work.

4
00:00:12,000 --> 00:00:14,350
So why don't we create a new table?

5
00:00:14,370 --> 00:00:16,890
I think I already have.

6
00:00:17,460 --> 00:00:19,890
Let's see a table called people.

7
00:00:19,920 --> 00:00:21,080
Oh, I don't.

8
00:00:21,210 --> 00:00:23,400
All right, Let's come up with a table called people.

9
00:00:23,430 --> 00:00:24,780
Create table people.

10
00:00:25,710 --> 00:00:28,170
And we're going to keep it super simple.

11
00:00:28,200 --> 00:00:32,430
Each person will just have a full name we're not going to bother with first and last.

12
00:00:32,430 --> 00:00:33,720
So name will be.

13
00:00:33,900 --> 00:00:34,910
Well, what do you think?

14
00:00:34,920 --> 00:00:36,660
Should it be Cha cha cha cha?

15
00:00:36,660 --> 00:00:38,640
I kind of already showed you my opinion.

16
00:00:38,640 --> 00:00:42,300
I'm going to do for Char, let's say, 50 characters or 100.

17
00:00:42,990 --> 00:00:50,730
And then what I'm going to do is have a birth date and a birth time and a birth date time.

18
00:00:50,910 --> 00:00:57,780
So this would be just an example of showing the three and how they compare and how we work with them.

19
00:00:58,200 --> 00:01:03,360
You probably would not want to have all three of those on a people table, but we're keeping it simple.

20
00:01:03,360 --> 00:01:08,580
So birth dates will be of type date, birth time.

21
00:01:09,470 --> 00:01:11,300
Would be if type time.

22
00:01:11,480 --> 00:01:17,150
And then I think I'll just call this date birth date for date time.

23
00:01:18,250 --> 00:01:22,150
And then semicolon and I'll go ahead and run this.

24
00:01:22,630 --> 00:01:26,470
And I should now have this table called people.

25
00:01:26,470 --> 00:01:29,430
And I didn't bother with doing any primary key.

26
00:01:29,440 --> 00:01:30,970
Probably should have had an ID in there.

27
00:01:30,970 --> 00:01:36,160
I mean, if we're doing this for real and we cared about anything else except date time and date time,

28
00:01:36,730 --> 00:01:39,880
it's confusing sentence date time and date time.

29
00:01:40,570 --> 00:01:42,550
Then we put some not NOLs in there.

30
00:01:42,550 --> 00:01:44,530
We would put some other constraints.

31
00:01:44,680 --> 00:01:45,580
Primary key.

32
00:01:45,580 --> 00:01:48,310
But again, we're only focusing on how these work.

33
00:01:48,310 --> 00:01:50,230
So now we need to insert a new row.

34
00:01:50,230 --> 00:01:52,530
We need to create a new person.

35
00:01:52,540 --> 00:01:54,400
Well, how do we do that?

36
00:01:54,400 --> 00:01:55,390
I'm going to do it over here.

37
00:01:55,390 --> 00:01:56,710
So I have a record of it.

38
00:01:57,190 --> 00:02:02,620
Obviously, we just use insert into I shouldn't say obviously, but we use insert into just like we

39
00:02:02,620 --> 00:02:03,760
do for any other row.

40
00:02:03,760 --> 00:02:07,300
We want to insert insert into people, we specify the order.

41
00:02:07,300 --> 00:02:13,210
So let's say name, birth, date, birth time and birth date.

42
00:02:13,600 --> 00:02:21,550
And then we have our values and I think our first person will insert will be named Elton.

43
00:02:23,180 --> 00:02:27,830
Elton's birth date needs to be in that particular date format.

44
00:02:27,830 --> 00:02:29,650
So remember how this works.

45
00:02:29,660 --> 00:02:33,150
If we go to my slides, I have an example of it right here.

46
00:02:33,170 --> 00:02:39,290
Year for four digits dash month, month dash d d for date or day.

47
00:02:39,860 --> 00:02:45,770
If you forget though, you can also go to the docs and go to the data types reference.

48
00:02:45,800 --> 00:02:48,230
Go to date, date time and timestamp types.

49
00:02:48,230 --> 00:02:52,700
And then there's a little summary of date here showing you that format.

50
00:02:52,730 --> 00:02:57,230
Now, in the next video, I'm going to show you how we could get the current date or the current time.

51
00:02:57,590 --> 00:03:04,670
But let's just say that Elton was born back in the year 2000, so that would be 2000.

52
00:03:04,670 --> 00:03:05,710
And then what month?

53
00:03:05,720 --> 00:03:11,870
Let's say born on Christmas 12, 25 month month de day.

54
00:03:11,990 --> 00:03:13,820
And then we have birth time.

55
00:03:13,820 --> 00:03:19,460
And if you don't remember the format for that, go back, look at the docs time.

56
00:03:19,460 --> 00:03:21,740
The time format looks like this.

57
00:03:21,790 --> 00:03:23,630
H h HRMS.

58
00:03:23,630 --> 00:03:30,290
Or potentially we could even have up to what was it, 838 hours, because it's not just a time of day,

59
00:03:30,290 --> 00:03:35,030
but for us, let's say it does represent the time of the day that Elton was born.

60
00:03:35,150 --> 00:03:39,170
Elton was born at, I don't know, 11 in the morning.

61
00:03:39,170 --> 00:03:40,340
So let's do that.

62
00:03:40,760 --> 00:03:45,950
11 colon and then zero 0 minutes, zero 0 seconds.

63
00:03:46,580 --> 00:03:49,580
And then date time follows basically the same pattern.

64
00:03:49,580 --> 00:03:54,470
We just smoosh those together with a space between so 2000 Dash 12.

65
00:03:55,650 --> 00:04:00,780
And this is tedious to type this in, but you really wouldn't be doing this most of the time.

66
00:04:01,950 --> 00:04:06,930
You can insert your own dates this way and your own date times, but a lot of the time this data is

67
00:04:06,930 --> 00:04:08,150
coming from somewhere else.

68
00:04:08,160 --> 00:04:10,560
It might be coming from the current time.

69
00:04:10,560 --> 00:04:12,860
Like I said, I'm going to show you that in the next video.

70
00:04:12,870 --> 00:04:17,430
If you insert somebody into a table, it's a new user account that they just created.

71
00:04:17,640 --> 00:04:23,910
Often you just want to set the time to be the current time, but you also will be working maybe with

72
00:04:23,910 --> 00:04:30,570
data coming from a form potentially or from some file that already has data in this format.

73
00:04:30,570 --> 00:04:35,340
So it's not that common that you're going to have to type out a full date time, but it is something

74
00:04:35,340 --> 00:04:36,220
that we can do.

75
00:04:36,240 --> 00:04:42,430
So 2012 2511 colon 0000 OC.

76
00:04:42,450 --> 00:04:44,330
So this should work for us.

77
00:04:44,340 --> 00:04:45,870
Let's try running it.

78
00:04:46,560 --> 00:04:52,620
Let's do a select star from people and we see our data is there.

79
00:04:52,680 --> 00:04:54,170
It follows the correct format.

80
00:04:54,180 --> 00:04:59,340
If I tried to insert something with the incorrect format, you would know.

81
00:04:59,340 --> 00:05:06,750
Let's add one or two more examples because I want to have some dates and date times and times for us

82
00:05:06,750 --> 00:05:12,270
to work with in the coming videos, because what I'm going to show you is how we could then take something

83
00:05:12,270 --> 00:05:20,460
like this date time and select the year, select the day of the week or select the month and work with

84
00:05:20,460 --> 00:05:24,630
them and manipulate them, or even do some basic math with dates or times.

85
00:05:24,810 --> 00:05:34,950
So instead of Elton, let's go with I don't know, Lulu, and Lulu was born in about 1985.

86
00:05:35,310 --> 00:05:36,600
So let's update this.

87
00:05:36,600 --> 00:05:41,640
I just want to be consistent so that they all match here so that the year is the same as the year and

88
00:05:41,640 --> 00:05:42,390
the date time.

89
00:05:42,390 --> 00:05:44,880
And she was born in April.

90
00:05:44,910 --> 00:05:51,180
How about April 11th at I don't know, about nine in the morning now.

91
00:05:51,180 --> 00:05:54,330
I don't actually have to do zero nine and pad this.

92
00:05:54,540 --> 00:05:57,060
I can just do nine and it will work.

93
00:05:57,060 --> 00:05:58,890
You'll see what the end result looks like.

94
00:05:59,340 --> 00:06:04,770
But let's say she was born at 945 in the morning with 10 seconds.

95
00:06:04,770 --> 00:06:09,900
I don't know if they really keep track of that at the hospital, but then let's make this consistent

96
00:06:09,900 --> 00:06:10,590
here.

97
00:06:11,560 --> 00:06:16,500
And the date, 1945, 1985, rather, April 11th.

98
00:06:16,510 --> 00:06:19,450
And then let's do one more Elton Lulu.

99
00:06:19,450 --> 00:06:28,400
And I don't know how about one who is born in He's young, 20, 20.

100
00:06:28,900 --> 00:06:39,160
What a not great year, but 2020 And he's born on how about August 15th?

101
00:06:39,280 --> 00:06:44,530
And he was born late at night about 23, 59.

102
00:06:45,010 --> 00:06:45,640
All right.

103
00:06:45,640 --> 00:06:52,210
And then let's just make this consistent here so that we have the same date and time in our date time

104
00:06:52,210 --> 00:06:52,840
column.

105
00:06:53,380 --> 00:06:53,710
All right.

106
00:06:53,710 --> 00:06:58,090
So this is just a lot of busy work, but now I want to insert these two.

107
00:06:58,120 --> 00:06:59,380
Let's just run this.

108
00:06:59,410 --> 00:07:01,540
Just I don't know why I keep pasting it over here.

109
00:07:01,540 --> 00:07:07,000
I think it's just more immediate because I can see the results in large text.

110
00:07:07,000 --> 00:07:07,990
So you can see them.

111
00:07:07,990 --> 00:07:12,070
But of course you could just run this from right within SQL workbench anyway.

112
00:07:12,100 --> 00:07:19,270
Now if I select star from people, we have three people and as I mentioned, that nine was padded out

113
00:07:19,270 --> 00:07:26,320
to be zero nine for the time and now we have our three different people with their own birth date,

114
00:07:26,320 --> 00:07:28,660
time and date time.

115
00:07:28,930 --> 00:07:31,180
So we have data to work with.

116
00:07:31,210 --> 00:07:34,480
The next thing we want to do is learn what we can do with this data.

117
00:07:34,480 --> 00:07:38,200
But there's one other thing I want to show you, which is how to work with the current date and time.

118
00:07:38,200 --> 00:07:39,340
And that's coming up next.