What is the value range of timestamp in MYSQL?

This article is about what the timestamp range is in MYSQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

TIMESTAMP and DATETIME have different value ranges and different simultaneous storage.

TIMESTAMP takes up four bytes and ranges from 1970-01-01 00:00:00 to 2038-01-19 3:14:07

So why is this value range? 4 bytes is obviously not enough to store the date format. 4 bytes to the power of 2^32

Then we can analyze timestamp is actually a C language INT type with signed bits.

Calculated in Oracle format as

SQL> select to_date('1970-01-01 00:00:00','yyyy-mm-dd hh34:mi:ss')+(power(2,31)-1)/(60*60*24) from dual;

TO_DATE('1970-01-0100:00:00','

------------------------------

2038/1/19 3:14:07

2^31 is because the sign bit is missing one bit. So it's 31, and minus one because it starts at 0 instead of 1, like 2 to the eighth is 256, but the actual number is 0 to 255(FF).

And you can think of TIMESTAMP as actually an additive process, and it's stored internally in the form of a signed bit of type int for seconds.

Thank you for reading! About "MYSQL timestamp value range is what" this article is shared here, I hope the above content can be of some help to everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!