All right.
So this is the last bit of new content around dates and date times and times.
I admit this section is a bit of a doozy or a pain or I don't know.
Maybe not the most exciting thing.
With that said this is a really important topic that we're going to discuss here.
And it's something that you'll be doing all the time or probably will be doing all the time.
I do it all the time which is adding in time stamps to our tables.
So when we say time stamps there's two things to unpack.
One is that that's a generic term that just refers to storing information metadata about when something
is created or updated that that sort of a standardized name across programming languages or across just
in programming in general and app development time stamps for to basically saving information about
when you create a post or when you start when you insert something into a table.
However in my as well timestamped is also a data type.
So yes I know it's the last video on the section but I'm actually going to show you another data type
so hang in there.
Fortunately this data type is really simple and there's only one case that I recommend that you ever
use it.
OK.
So there's a page called The date date time and time stamp types are going to ignore date.
We've already talked about that.
Let's compare date time and time stamp.
They both do the same thing.
They store date and time information but there's two differences.
If we read the description date time is used for values that contain both date and time parts.
Yes we know that the time stamp is used for values that contain both date and time parts as well.
So they're identical in that sense.
But the main difference comes down to the range of times that and date they support.
So if we look at date time it goes back to year 1000 and all the way up to year nine thousand nine hundred
ninety nine time stamp on the other hand only goes back to 1970 and up to 2038.
So that's really important to note if you are working with something where you know you're storing dates
that may go back very far like if I'm trying to think of a good example but things before 1970 Certainly
you might have sites where you're storing a birth date and you might have users who are older than that
and they're born in the 60s or the 50s.
So time stamp would not work.
Same thing going forward if you're working with dates and date times where you need things to be further
into the future past 2038 then you're not going to want to use timestamps as well but for what we're
talking about just saving when something is created or when it's most recently updated we don't need
to worry about that being before 1970 or after 2038 because it's right now or when our app is up and
running.
You know now is relative.
But basically it's not going to be past 2038 and we're not changing those dates all that we're going
to do is store information.
So time stamp is a good data type to use there and you might be wondering why use it in the first place.
Why does it exist if there's already date time.
The answer like so many things in my school is that it just takes up less space.
I believe it's 4 bytes compared to eight for date time.
Or it might be.
It's a it's double to use date time compared to time stamp.
I believe it's four and eight.
So it's easier.
And if you're doing something you know every time you insert the thing into a row you are updating something
that four versus eight makes a difference.
If you're working with thousands and thousands or millions of records.
So now that we've got that out of the way just to summarize they use to store the same type of data.
This one is smaller timestamps take up less space but they have a more limited range.
So use date time for everything except for what we're about to show you.
OK.
So let's hop over to cloud 9 here we are.
And I have a simple schema going to create a table called comments comments.
It's very simple.
It has two fields the first row is called the first field is called content and it's just the content
of the comment.
You know things like Great article or I made a thousand dollars a week working from home learn how email
me or whatever spam comment there is and then this is the important part created it which is a time
stamp.
First thing to notice it could be Date Time.
Just want to make that clear you can do that.
It will work.
But we're using time stamp because this is basically what it's intended for and it's takes up less space.
So just setting the time stamp on its own is not that useful.
This is the key part.
Setting the default value to be now.
So whenever it's created take that now that current time and put it in time stamp.
So if we run this schema right now and we insert a few things so let's do insert into comments.
All we need to insert is content and what we'll do is just insert our first one.
We're going to do them separately so that they have different time stamps.
So the first one will be L-O.
What a funny article something silly like that.
Then let's do another one.
Like
I found this offensive.
OK.
So now if we do so like star from comments you can see as well.
What a funny article created at Artie Feldon automatically today's date the time 36 seconds compared
that to.
I found this offensive.
Same date same time until we hit second.
It was created 10 seconds later.
So that's created at.
And of course that's really useful.
I would show you how to sort them.
However they're already sorted here.
As you can see because the order I'm putting in or putting them in is sorting them.
But let me just try one more.
Let's do just some jibberish.
Select them all.
Now you can do things like order by created at.
And now we'll order them.
Basically the way they're ordered now.
But we can reverse that
change it to descending.
Now we have the most recent comments up top.
So that's created at using default.
Now along with time stamp but what if we also wanted to store any time a field was changed.
There's an easy way to do that as well.
Just scroll down.
Magically hidden here.
So I sort of calling it created that I made a new field called changed at and the first part is the
same sets.
It's a time stamp and it sets a default value to be.
Now then we get this on update which you haven't seen before.
And frankly this is really the only time I ever use it.
I don't know trying to think if I ever use another capacity.
Pretty much this is the only way I've seen it used on update current time stamp.
So what this is saying is that when the row is changed so if content is changed then update changed
that to be the current time stamp.
So we could put something different yet we can put three although that's a problem because it's not
a time stamp.
But if we made this an and I could do something ridiculous like this.
And every time that comments a row is updated it will change.
Change that to be the number three.
But there's no reason to do that whatsoever so I walked.
So if we run this I called it comments too.
By the way I'm not pointing that out because I think it's original but just so they are aware.
It's a different table if I.
Now let me just use the same comments that I've already run.
And just change it to be inserted to comments to.
Here's the first one.
And our next one will be just.
Ello ello ello.
Well OK and now we do is select a star from comments to.
He is the change that it's filled in.
Because we created them and let's just do one more this time will be I like cats and dogs just to please
everybody.
Unless you're a bird person I guess or you hate animals so they're ordered by default because this is
how we added them and there's no other way that they would be ordered.
But let's try updating one.
OK.
So let's update this jibberish here to say something else.
It's been a while since we've done an update but hopefully remember.
So we're going to want to update comments to set content equal to and let's say this is not gibberish.
Something like that.
And then you need to specify where content is currently.
This mess of jibberish.
OK.
So if I update that.
Notice what you had before 20 30 18 days not going to change 2038.
Now if I do a like star it's changed to 2033 0 to.
And so now if we order things by
what we call it changed at.
You'll see we now have a new order.
And so that's really important to take note of is that if you want to not only track when something
is created but when it was updated you can use this set up here so we have time stamped default.
Now on Update Oh one thing you didn't talk about current time stamp.
You could also put.
Now here the reason I didn't it's just to show you you can also use current time stamp.
It's just another shortcut.
I prefer to use now just because it's like I said a couple of times short simple easy to remember but
current time stamp does exact same thing in this case.
OK.
So this is something a bit of a recipe that will probably return to is worth knowing you'll see me use
it as we go in the scores and some of the other schemas that we create.
Things get more complex.
It's important it's useful to store that metadata about when things are created or when they're updated.
OK we're done.