Handling dates before 1970 in MySQL?

You need to use a date type to handle dates before 1970 because dates store values ​​from 1000 to 9999. The date type can be used when you only need to deal with the date part and not for time purposes.

MySQL gives data in the following format. The format is as follows -

‘YYYY-MM-DD’

The starting date range is as follows -

1000-01-01

The ending date range is as follows -

9999-12-31

To understand what we have discussed above, let us create two tables. The query to create the first table is as follows -

mysql> create table DateDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> DateBefore1970 date
   -> );
Query OK, 0 rows affected (0.71 sec)

Now you can insert some records in the table using insert command. Whenever you use a date beyond the end of "9999-12-31" you get a date of "0000-00-00".

Let's insert a record greater than "9999-12-31"'. The query is as follows -

mysql> insert into DateDemo(DateBefore1970) values('1000-10-20');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DateDemo(DateBefore1970) values('1940-12-31');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DateDemo(DateBefore1970) values('1500-01-25');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DateDemo(DateBefore1970) values('1900-04-14');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DateDemo(DateBefore1970) values('1710-11-15');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DateDemo(DateBefore1970) values('9999-12-31');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DateDemo(DateBefore1970) values('10000-12-31');
Query OK, 1 row affected, 1 warning (0.11 sec)

Now you can use select statement to display all the records in the table. The query is as follows -

mysql> select *from DateDemo;

The following is the output -

+----+----------------+
| Id | DateBefore1970 |
+----+----------------+
|  1 | 1000-10-20     |
|  2 | 1940-12-31     |
|  3 | 1500-01-25     |
|  4 | 1900-04-14     |
|  5 | 1710-11-15     |
|  6 | 9999-12-31     |
|  7 | 0000-00-00     |
+----+----------------+
7 rows in set (0.00 sec)

The above is the detailed content of Handling dates before 1970 in MySQL?. For more information, please follow other related articles on the PHP Chinese website!