All right.
So this is the last bit of new content around dates and date times and times.
I admit that this section is a bit of a doozy or pain or I don't know, maybe not the most exciting
thing.
With that said, this is a really important topic, what we're going to discuss here, and it's something
that you'll be doing all the time or probably we'll be doing all the time.
I do it all the time, which is adding in timestamps to our tables.
So when we say timestamps, 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.
AT That's sort of a standardized name across programming languages or across just in programming in
general, in app development, timestamps refer to basically saving information about when you create
a post or when you start when you insert something into a table.
However, in my SQL timestamp is also a data type.
So yes, I know it's the last video in this 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.
So there's a page called the Date Date, Time and time stamp types.
We're 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 and dates they support.
So if we look at date time, it goes back to year 1000 and all the way up to year 9999.
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're storing dates that may go back very far.
Like 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 sixties or the fifties.
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 time stamps 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 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 timestamp 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 SQL, is that it's just takes up less space.
I believe it's four bytes compared to eight for date time.
Or it might be it's double to use date time compared to timestamp, I believe it's four and eight.
So it's easier.
And if you're doing something every time you insert something into a row, you are updating something
that four versus eight.
It 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 they store the same type of
data.
This one is smaller time stamps take up less space, but they have a more limited range.
So use date time for everything except for what I'm about to show you.
So let's hop over to Cloud nine.
Here we are.
And I have a simple schema.
Going to create a table called comments.
Comments is very simple.
It has two fields.
The first row is called or the first field is called content.
And it's just the content of the comment.
Things like great article or I made a thousand 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 the timestamp first thing to notice it could
be date time.
I just want to make that clear.
You can do that.
It will work.
But we're using timestamp because this is basically what it's intended for and it takes up less space.
So just setting it to timestamp on its own, it's 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 timestamp.
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 timestamps.
So the first one will be lol.
What a funny article, something silly like that.
Then let's do another one.
Like
I found this offensive.
So now if we do select star from comments you can see lol.
What a funny article created that already filled in automatically today's date the time 36 seconds.
Compare that to I found this offensive.
Same date, same time until we hit the second.
It was created 10 seconds later.
So that created that.
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 gibberish.
Select them all.
And now we can do things like order, buy, create a debt and that will order them basically the way
they're ordered now.
But we can reverse that, change that to descending.
Now we have the most recent comments up top.
So that's created at using default now along with timestamp.
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, instead of calling it created at, I made a new field called Change Debt.
And the first part is the same sets.
It's a time stamp and it sets the default value to be now.
But then we get this on update, which we haven't seen before.
And frankly, this is really the only time I ever use it or I don't know, I'm trying to think of ever
use it in 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 change
debt to be the current timestamp.
So we could put something different here, we could put three, although that's a problem because it's
not a timestamp.
But if we made this an int, 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 won't.
So if we run this I called it comments two, by the way.
I'm not pointing that out because I think it's original.
But just so that you're 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 insert into comments too.
So here's the first one.
And our next one will be just lol.
LOL lol.
And now we do a select star from comments to how you can see 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 the order we added them in.
There's no other way that they would be ordered.
But let's try updating one.
So let's update this gibberish here to say something else.
It's been a while since we've done an update, but hopefully you remember.
So we're going to want to update comments to.
Sets content equal to and let's say this is not gibberish or something like that.
And then we need to specify where content is currently this mess of gibberish.
So if I update that.
Notice what we had before 20 3018.
The date is not going to change.
2038.
Now, if I do a select star, it's changed to 2030 302.
And so now if we order things by.
Would we call it change that?
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
was created, but when it was updated, you can use this set up here.
So we have timestamp default now on update.
Oh, one thing it didn't talk about current timestamp you could also put now here, the reason I didn't
is just to show you you can also use current timestamp.
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 timestamp does the exact same thing in this case.
So this is something a bit of a recipe that you'll probably return to.
It's worth knowing you'll see me use it as we go in this course and some of the other schemas that we
create.
As things get more complex, it's important.
It's useful to store that metadata about when things are created or when they're updated.
We're done.