If you're still with me, we've got one more topic to cover around dates and times.
And this is a really cool, nice feature built into my SQL.
When we create a table, let's say we're making a captions table for social media captions like Instagram
captions, for example.
A user can go and edit the caption afterwards.
They can make the caption and put some text in there.
So let's say that's what our table is.
And the captions table has a text.
The actual caption text, which would be a var char.
Let's just call it 100 or maybe 150 or something.
Okay.
But what I also want to then do is capture the date and the time that it was created at.
So I could do created that just like that.
And then I could say that it is a timestamp or a date time, but I'm going to use timestamp because
it takes up less space.
It's just a little bit more efficient.
So I can say that it is of type timestamp, but what's even fancier is that I can give it a default
value and say the default should be current timestamp just like that and I like to capitalize it.
So that's going to bother me.
And now whenever we make a new caption or new row in this table, we insert something the created that
value will be set to the current time stamp, although it's complaining to me because I have a semicolon
there.
All right, so let's try running this and I'll show you that.
Let's do insert into captions.
And then I'm just going to insert the text I won't bother inserting created at because I want that to
have the automatic current timestamp that it was created at.
So values and then social media caption How about just me and the Girls Chillin?
Something like that.
And let's do one more.
How about.
Beautiful sunset.
Okay.
Now I'm going to select star from captions.
And what do we see created that was automatically populated?
It has the default value of the current timestamp right in there.
So 2022.
October 10th or October 5th, 2022.
And then the time here, 18 0401 and then 18 0409.
And I didn't have to touch it at all.
Now, I'm not only limited to doing this with time stamps, it also works with date time.
If I set this to be current date time and there's actually a whole page on the docs about this automatic
initialization and updating for timestamp and date time and there's a lot more to it.
You can get quite complicated in how you handle null values and missing values and all of that.
We're not going to worry about it for now.
But what's really fancy is this on update current time stamp, What this will do if I set this on any
part of a table, any column, it will update this column to be the current timestamp whenever any other
column is changed in a row.
So if I wanted to keep track of the last time somebody updated their caption, let's do captions too.
So we have both of these.
I could add an updated at field or column.
It's also a time stamp and then I'll add an on update current time stamp.
So I'm not going to give it a default value, although I guess I could have it default to the current
time as well, if that probably makes sense.
When you first create a new caption, do we set updated at as well or do we leave it blank?
It's up to us, I guess, how we want to handle that.
But what is very different between these two is whenever text changes or any other column for a given
row, this one will automatically have the current timestamp set to updated add.
So let's try running that captions too, and let's do an insert into captions to text and we'll have
our values.
I love life.
All right.
Very excited, exuberant caption here.
And if I select star from captions to.
Captions two we see updated at starts.
No, now, I could have also given it a default value of current time stamp, but I didn't.
But now if I change text before I do that just notice created at notice the time 18 655.
Okay now let's update captions to.
Remember the syntax.
It's been a while since we updated set.
Let's set the text to be.
I love life with a bunch of exclamation points like that where?
And then I have to be able to target this one.
Although actually we don't.
If I leave off the where, it's just going to update every row.
We only have one row, so that's fine.
And if I do this, let's see what happened.
Let's select star again from captions to.
Take a look.
Updated at now has a value and it's set to 1808 12 created that is still unchanged.
So any time I change any column in this table updated that will automatically have the new current time
stamp set.
So if I do it one more time, I'm going to just put more exclamation points.
I updated this row, I didn't update updated at explicitly, I only updated text, but it doesn't matter
what I change in a row.
Any column that changes.
Updated.
That will also change.
And we can see that right here.
Now it's 843 for the time instead of 812 30 seconds, 31 seconds have elapsed.
So that's really cool.
And we can do the same thing with date time.
I won't show it to you just because it would be more of the same.
But you replace this with date time.
And remember, there's a distinction.
Timestamps take up less space, but they support a much narrower, narrower range of dates.
1970 to what is it, 2038, I believe.
And date time can go all the way up to year 9999 and all the way down or back in history to year 1000.
And you can do both of these at the same time, right?
You can have a default value like you see right here and an on update.
So if you wanted to do that, you could I just put it into two so I can have a distinct created at and
then updated it.
So now I will have them separate and I'll always know when something was created at and when it was
most recently updated.
You may not need that.
This is not something you need to add on to every table, but if it makes sense, if there's a use case
for it, this is the easiest way to do that.