The value range of python date and datetime (compare Mysql's datetime and timestamp)

The case of Python

Let's take a look at the date type first!

 In [1]: from datetime import date

In [2]: date.min
Out[2]: datetime.date(1, 1, 1)

In [3]: str(date.min)
Out[3]: '0001-01-01'

In [4]: str(date.max)
Out[4]: '9999-12-31'

It can be seen that the value range of ---10a83a272a5ced9fa1d5bdf177cc4646 date is 0001-01-01 to 9999-12-31

Let's look at datetime again!

 In [5]: from datetime import datetime

In [6]: str(datetime.min)
Out[6]: '0001-01-01 00:00:00'

In [7]: str(datetime.max)
Out[7]: '9999-12-31 23:59:59.999999'

You can see that the value range of ---b9bb4f03e4f4f20609c920d316b61417 datetime is 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999

Mysql situation

Let's take a look at the mysql situation, the following official documents:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31 '.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00: 00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

To sum it up:

• The value range of date: 1000-01-01 to 9999-12-31
• The value range of timestamp: 1970-01-01 00:00:01 to 2038-01-19 03:14:07
• The value range of datetime: 1000-01-01 00:00:00 to 9999-12-31 23:59:59

Reference: mysql documentation

But need to pay attention:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion . The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 5.1.10, “Server SQL Modes”.

And this part:

It means that myql will set invalid date to 0000-00-00 and invalid datetime and timestamp to 0000-00-00 00:00:00 .

For example 2022-13-35 is an invalid date , when inserting this value into mysql, mysql with strict mode enabled will report an error, and mysql without strict mode will turn to 0000-00-00 Save.

‼ ️ So when we use Python to read the time type from mysql, we must pay attention to this pit! Because 0000-00-00 there is no way to convert to Python's date type, an error will be reported! ‼ ️

---

By the way, you can see how many bytes each of Mysql's date, timestamp, and datetime occupies:

• date type 3 bytes
• timestamp type 4 bytes
• datetime type 8 bytes

Reference article: Data Type Storage Requirements

---

The time type inserted by Mysql can be 0000-00-00 The reason for this format can be referred to: MySQL Incorrect datetime value: '0000-00-00 00:00:00'

This invalid time cannot be inserted by default in Mysql8