If we take a look at those fields, we can quickly see that there are some differences:
-
timestamp
stores time in seconds, whiledatetime
stores it in a specific format (usuallyYYYY-MM-DD HH:MM:SS
) -
timestamp
has a range of1970-01-01 00:00:01
UTC to2038-01-19 03:14:07
UTC, whiledatetime
has a range of1000-01-01 00:00:00
to9999-12-31 23:59:59
, giving you a much wider range of dates. - source -
timestamp
is stored in 4 bytes, whiledatetime
is stored in 8 bytes, which means thattimestamp
takes up less space -
timestamp
is stored in UTC, whiledatetime
is stored in the timezone of the server -
datetime
can support fractions of a second, whiletimestamp
cannot
So, which one should you use? It depends on your use case. If you need to store dates between 1970-01-01 00:00:01
UTC to 2038-01-19 03:14:07
UTC, then you should use timestamp
. This will make the date searchable really quickly.
But if you need to store dates outside of that range, then you should use datetime
. Or if you need to have a timezone other than UTC, then you should use datetime
.
No comments or questions yet...