All right.
We've got one more topic around dates and times, and it's going to seem a little confusing up front.
There is a whole other data type, very similar to date time called time stamp.
So time stamps combine a date and a time.
If we go to the docs and look at the page around time stamps, it basically looks just like a date time,
which we've already seen.
Take a look right here.
It says the time stamp data type is used for values that contain both date and time.
The date time type is used for values that contain both date and time.
So what is the difference?
There's really two pieces.
One is that a time stamp takes up less storage.
It's a smaller thing in memory, which is a trend for all of these different types we've covered, right?
Tiny end versus end versus big int or double versus float versus decimal.
In this case, time stamps are smaller, but that's because they support a smaller range of dates,
a much smaller range of dates.
Look at the minimum 1970, January 1st, 1970, and then the maximum is January 19th of 2038 at three
1407 seconds.
Whereas a date time can go from the year 1000 up to the year 999,000.
So that's an important difference.
If we just think about what we're what we might be storing.
It might narrow down the type we would use if we're storing somebody's birthday.
Well, a timestamp maybe doesn't make sense unless we know all of our users were born after 1970.
We probably would want to use a date time.
But if we were trying to store the time something was most or most recently updated at, or when a comment
was created or a user account was created, or somebody liked an Instagram post or whatever, some sort
of interaction, an event occurred, well, a timestamp will work just great for that and it takes up
less space.
So it is confusing.
There's no way around it.
They look and behave the same most of the time.
Let me show you an example of working with time stamps in the shell.
We've got all these different functions that also work with time stamps.
And technically, when we did now earlier, if I did select now, I said, this is a date time.
It's actually a time stamp, not a date time.
But they are essentially interchangeable when we're working with a date time or time stamp that overlap
in the same range, right?
This is right now today, if I express this as a date, time or time stamp, it will look exactly the
same.
So I can show you that if I did time stamp.
I can take this and turn it into a timestamp.
We're not going to notice anything.
It looks like a time stamp.
We also have time stamp and time stamp.
If we can do math with timestamps, but at the end of the day, it's just another format for storing
a date with a time.
We just have a much narrower range that we can store those dates for.
But it comes at the benefit of taking up less memory.
Confusing, yes.
Hopefully I clarified the difference.
In the next video, I'm going to show you how we can use timestamps and date time and get some auto
updating functionality, some really fancy stuff when we create a table.