So next up, we're going to actually try using date and time and date time in a table.
We're not going to do anything terribly creative.
I'm just going to compare the three so you can see how they work.
So why don't we create a new table?
I think I already have.
Let's see a table called people.
Oh, I don't.
All right, Let's come up with a table called people.
Create table people.
And we're going to keep it super simple.
Each person will just have a full name we're not going to bother with first and last.
So name will be.
Well, what do you think?
Should it be Cha cha cha cha?
I kind of already showed you my opinion.
I'm going to do for Char, let's say, 50 characters or 100.
And then what I'm going to do is have a birth date and a birth time and a birth date time.
So this would be just an example of showing the three and how they compare and how we work with them.
You probably would not want to have all three of those on a people table, but we're keeping it simple.
So birth dates will be of type date, birth time.
Would be if type time.
And then I think I'll just call this date birth date for date time.
And then semicolon and I'll go ahead and run this.
And I should now have this table called people.
And I didn't bother with doing any primary key.
Probably should have had an ID in there.
I mean, if we're doing this for real and we cared about anything else except date time and date time,
it's confusing sentence date time and date time.
Then we put some not NOLs in there.
We would put some other constraints.
Primary key.
But again, we're only focusing on how these work.
So now we need to insert a new row.
We need to create a new person.
Well, how do we do that?
I'm going to do it over here.
So I have a record of it.
Obviously, we just use insert into I shouldn't say obviously, but we use insert into just like we
do for any other row.
We want to insert insert into people, we specify the order.
So let's say name, birth, date, birth time and birth date.
And then we have our values and I think our first person will insert will be named Elton.
Elton's birth date needs to be in that particular date format.
So remember how this works.
If we go to my slides, I have an example of it right here.
Year for four digits dash month, month dash d d for date or day.
If you forget though, you can also go to the docs and go to the data types reference.
Go to date, date time and timestamp types.
And then there's a little summary of date here showing you that format.
Now, in the next video, I'm going to show you how we could get the current date or the current time.
But let's just say that Elton was born back in the year 2000, so that would be 2000.
And then what month?
Let's say born on Christmas 12, 25 month month de day.
And then we have birth time.
And if you don't remember the format for that, go back, look at the docs time.
The time format looks like this.
H h HRMS.
Or potentially we could even have up to what was it, 838 hours, because it's not just a time of day,
but for us, let's say it does represent the time of the day that Elton was born.
Elton was born at, I don't know, 11 in the morning.
So let's do that.
11 colon and then zero 0 minutes, zero 0 seconds.
And then date time follows basically the same pattern.
We just smoosh those together with a space between so 2000 Dash 12.
And this is tedious to type this in, but you really wouldn't be doing this most of the time.
You can insert your own dates this way and your own date times, but a lot of the time this data is
coming from somewhere else.
It might be coming from the current time.
Like I said, I'm going to show you that in the next video.
If you insert somebody into a table, it's a new user account that they just created.
Often you just want to set the time to be the current time, but you also will be working maybe with
data coming from a form potentially or from some file that already has data in this format.
So it's not that common that you're going to have to type out a full date time, but it is something
that we can do.
So 2012 2511 colon 0000 OC.
So this should work for us.
Let's try running it.
Let's do a select star from people and we see our data is there.
It follows the correct format.
If I tried to insert something with the incorrect format, you would know.
Let's add one or two more examples because I want to have some dates and date times and times for us
to work with in the coming videos, because what I'm going to show you is how we could then take something
like this date time and select the year, select the day of the week or select the month and work with
them and manipulate them, or even do some basic math with dates or times.
So instead of Elton, let's go with I don't know, Lulu, and Lulu was born in about 1985.
So let's update this.
I just want to be consistent so that they all match here so that the year is the same as the year and
the date time.
And she was born in April.
How about April 11th at I don't know, about nine in the morning now.
I don't actually have to do zero nine and pad this.
I can just do nine and it will work.
You'll see what the end result looks like.
But let's say she was born at 945 in the morning with 10 seconds.
I don't know if they really keep track of that at the hospital, but then let's make this consistent
here.
And the date, 1945, 1985, rather, April 11th.
And then let's do one more Elton Lulu.
And I don't know how about one who is born in He's young, 20, 20.
What a not great year, but 2020 And he's born on how about August 15th?
And he was born late at night about 23, 59.
All right.
And then let's just make this consistent here so that we have the same date and time in our date time
column.
All right.
So this is just a lot of busy work, but now I want to insert these two.
Let's just run this.
Just I don't know why I keep pasting it over here.
I think it's just more immediate because I can see the results in large text.
So you can see them.
But of course you could just run this from right within SQL workbench anyway.
Now if I select star from people, we have three people and as I mentioned, that nine was padded out
to be zero nine for the time and now we have our three different people with their own birth date,
time and date time.
So we have data to work with.
The next thing we want to do is learn what we can do with this data.
But there's one other thing I want to show you, which is how to work with the current date and time.
And that's coming up next.