2038/01/19 11:14:07 MySQL

It is a bit abrupt to give such a title, but this is a boundary value that I have been thinking about recently. The question is: how to define the data type for the time field in MySQL.

In the previous MySQL database design, we designed all the time fields into the bigint type, and basically all the values inserted here are indirectly or directly from time () this function is obtained.

1. About time ()

In PHP, the time () function returns a Unix timestamp. Its official documentation
This function is interpreted as follows:

Int
Time

(Void
)

Returns the current time measured in the number of seconds since the Unix epoch (January 1 1970 00:00:00 GMT ).

Is the integer difference between the current time and the number of seconds minus the number of seconds at 00:00:00, January 1, January 1, 1970.

From the returned value of this function, this is an integer value, that is, it returns a value with a range. In PHP, there is no symbolic or unsigned definition of integer. Although its length is related to the platform, the general maximum value is 231.
-1 = 2147483647. Run the following PHP code:

Print strftime ("% Y/% d/% m", 2147483647 );

The results will be the same as those in the title. From now on, this date does not seem so long.

In PHP, most of the time processing functions have such restrictions. For example, such code may have unexpected results:

Print strftime ("% Y/% d/% m % H: % m: % s/n", 2147483648 );

// Output: 04:51:44

$ Last = strtotime ("2038/01/19 11:14:08 ");

Var_dump ($ last); // The output is bool (false)

That is to say, an error occurs when PHP-related functions are used to process the date after this date.

Back to the database design, we inserted a data that only returns int type into the bigint type field. So will this result in a waste of database space? Is there a more reasonable solution? This is what I have been thinking about recently.

Ii. timestamp type

There are a series of Date and Time types in MySQL, including datetime, date,
Timestamp, time, and year. Here, timestamp is closest to the time field to be used in our system.
A string in the format of "YYYY-MM-DD hh: mm: SS", if you want to get a numeric value, add "+ 0" after its field name in the SQL statement, such as select
Create_date + 0 from
Table. The length of the timestamp type is 4 bytes, the same as that of the int type, and their date range is completely consistent, so I think the actual number of Timestamp access
The value should be the timestamp of the date, but MySQL only converts the value when the SELECT statement is made. In addition, if the field is
Timestamp, there are some additional conversions. In "PHP and MySQL time Interchange
This article summarizes several common interchange methods:

Method 1: Use the date () and strtotime () Functions

$ Mysqltime = Date ('Y-m-d h: I: s', $ phptime );

$ Phptime = strtotime ($ mysqldate );

Method 2: use MySQL Function Conversion in the query statement: unix_timestamp (datetime => PHP timestamp) and from_unixtime (PhP timestamp => datetime ).

$ SQL = "select unix_timestamp (datetimefield) from table where ...";

$ SQL = "Update table set datetimefield = from_unixtime ($ phptime) where ..";

In fact, this article also mentions the third method, which is the conclusion I have obtained through research on this type of field-using an integer field in MySQL to save the PHP timestamp type.

Iii. retention time of fields of the int type

 

In our system, there is a table that is used to capture blog articles. Every day, many notes are added.
We have processed hundreds of millions of data records. Assume that a time field in a table with 0.1 billion records is processed as a bigint type
Each record will waste 4 bytes, which will waste 4*0.1 billion/100,000,000 M ≈ 381m space. From the perspective of database Optimization
You can try it.

Of course, from another perspective, the database needs to consider its scalability. Assume that in 2038
May January 19, will it be difficult to expand these date fields? As a matter of fact, since these tables are processed in sub-tables, at least for some data tables before this limitation day,
We do not need to consider modifying the data field type. In addition, although PHP does not have signed and unsigned plastic variables, there is a concept of signed and unsigned plastic variables in the MySQL database.
From the official MySQL documentation
, We can see this description.

Type	Bytes	Minimum value	Maximum Value
		(Signed/unsigned)	(Signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

That is to say, if the int field is set to unsigned, the value range can be increased by 1 time. For a moment, calculate the value according to time () the idea of using a function to calculate the timestamp (we should consider extending its return value range in the future ),The value of 4294967295 may be supported by 2106. I think, in this scope, we can cope with our system for the foreseeable lifetime.

PS also tried to change the system directly to 2038 yesterday. Unexpectedly, Gtalk, which never went wrong, reported an error to me, and the anti-virus software, Norton, also got together. It seems that various software may have the same restrictions on time processing.