This article provides a detailed analysis of the time types of MySql and Java. For more information, see
MySql has the corresponding time type in Java.
Date java. SQL. Date
Datetime java. SQL. Timestamp
Timestamp java. SQL. Timestamp
Time java. SQL. Time
Year java. SQL. Date
Analyze It
Refer to MySql reference manual
Date:
A date. the supported range is '2017-01-01 'to '2017-12-31 '. mySQL displays DATE values in 'yyyy-MM-DD 'format, but allows you to assign values to DATE columns using either strings or numbers.
Only date information is recorded, indicating that the range is 1000-01-01 to 9999-12-31.
MySql displays this type of field as YYYY-MM-DD. You can use the string type or numeric type to add the field data.
Because a Date field only records the Date information, if the added data contains the time information, the time information will be automatically truncated.
If you want to save the time information, you can use the DateTime type.
After testing, we found that the following two methods can be used to fill the Date field:
Press string:
Insert into time_table (CreateDate) values ('2017-04-09 ')
By number:
Insert into time_table (CreateDate) values (20070409)
You can use java. SQL. Date to obtain the data.
Code:
Date dtDate = rsBuffer. getDate ("CreateDate ");
The test code is as follows: (IDBFace is a simple class encapsulated based on JDBC. it accepts SQL statements to operate databases)
The code is as follows:
Public void testDate () throws SQLException
{
IDBFace DBFace = DBFactory. createMySqlFace ();
DBFace. connect ();
// Clear the table
String strDelete = "delete from time_table ";
DBFace. update (strDelete );
// Add
String strInsert = "insert into time_table (CreateDate) values (20070409 )";
DBFace. update (strInsert );
// Obtain
String strSelect = "select * from time_table ";
ResultSet rsBuffer = DBFace. select (strSelect );
While (rsBuffer. next ())
{
Date dtDate = rsBuffer. getDate ("CreateDate ");
System. out. println (dtDate. toString ());
}
DBFace. close ();
}
Execution result:
DateTime
A date and time combination. the supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. mySQL displays DATETIME values in 'yyyy-MM-DD HH: MM: SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.
The main difference between DateTime and Date is that DateTime can record Date and time information. While Date only records Date information. The data is formatted by YYYY-MM-DD HH: MM: SS for MySql in the range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59, allowing submission by string and number.
For example, submit a job in numbers:
Insert into time_table (CreateDate) values (20070409132013)
You can use java. SQL. Timestamp to obtain data of this type.
The code is as follows:
The code is as follows:
Public void testDateTime () throws SQLException
{
IDBFace DBFace = DBFactory. createMySqlFace ();
DBFace. connect ();
// Clear the table
String strDelete = "delete from time_table ";
DBFace. update (strDelete );
// Add
String strInsert = "insert into time_table (CreateDateTime) values (20070409132013 )";
DBFace. update (strInsert );
// Obtain
String strSelect = "select * from time_table ";
ResultSet rsBuffer = DBFace. select (strSelect );
While (rsBuffer. next ())
{
Timestamp tsBuffer = rsBuffer. getTimestamp ("CreateDateTime ");
System. out. println (tsBuffer. toString ());
}
DBFace. close ();
}
Execution result: 13:20:13. 0
TimeStamp
A timestamp. the range is '2017-01-01 00:00:00 'to partway through the year 1970. a timestamp column is useful for recording the date and time of an INSERT or UPDATE operation. the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. you can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.
Very similar to DateTime type
The value range is 2037, and the precision is 1 second/
If no value is assigned to a Timestamp column in SQL, the column is constructed as the current time.
If you submit a NULL value, this column will also be input at the current time.
If a time submission error occurs, this column is filled with 0.
Timestamp requires less storage space than the DateTime type. it only needs 4 bytes, while DateTime requires 8 bytes.
But pay special attention to this. Timestamp can only indicate the time range is 1970-2037.
To use Timestamp, make sure that the submitted time data does not exceed this range.
The code is similar to the DateTime class, and I do not like it, so I omitted it.
Time:
A time. the range is '-838: 59: 59' to '2014: 59: 59 '. mySQL displays TIME values in 'hh: MM: SS' format, but allows you to assign values to TIME columns using either strings or numbers.
Time records only the Time information, not the date information.
The value range is-838: 59: 59 to 838: 59: 59. MySql uses HH: MM: SS to format the data and allows the input to be a string or number.
Code:
The code is as follows:
Public void testTime () throws SQLException
{
IDBFace DBFace = DBFactory. createMySqlFace ();
DBFace. connect ();
// Clear the table
String strDelete = "delete from time_table ";
DBFace. update (strDelete );
// Add
String strInsert = "insert into time_table (CreateTime) values (131211 )";
DBFace. update (strInsert );
// Obtain
String strSelect = "select * from time_table ";
ResultSet rsBuffer = DBFace. select (strSelect );
While (rsBuffer. next ())
{
Time tmBuffer = rsBuffer. getTime ("CreateTime ");
System. out. println (tmBuffer. toString ());
}
DBFace. close ();
}
Execution result: 13:12:11
Year
A year in two-digit or four-digit format. the default is four-digit format. in four-digit format, the allowable values are 1901 to 2155, and 0000. in two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. mySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. the YEAR type is unavailable prior to MySQL 3.22.
Year can be expressed in two ways, four-digit and two-digit.
The default value is 4 bits. The range is 1901-2155.
The expression of two digits only records the last two digits. The range is 1970-2069.
It can be inserted as a string or number.
Code:
The code is as follows:
Public void testYear () throws SQLException
{
IDBFace DBFace = DBFactory. createMySqlFace ();
DBFace. connect ();
// Clear the table
String strDelete = "delete from time_table ";
DBFace. update (strDelete );
// Add
String strInsert = "insert into time_table (CreateYear) values (2007 )";
DBFace. update (strInsert );
// Obtain
String strSelect = "select * from time_table ";
ResultSet rsBuffer = DBFace. select (strSelect );
While (rsBuffer. next ())
{
Date dtBuffer = rsBuffer. getDate ("CreateYear ");
System. out. println (dtBuffer. getYear () + 1900 );
}
DBFace. close ();
}
Execution result: 2007
Note:
The Date. getYear () method returns the number of years since January 1, 1900. Therefore, 1900 must be added to display the correct time.
This method has been deprecated.
In addition.
One way is to record the time without any of the above types.
Instead, the time is recorded in char (or vchar) mode.
In this way, it is convenient to insert data and display records without any conversion.
But it has two important defects.
(1) Independent development methods should be used to verify the validity of the time data. For example, the ajidjieoa string is not a time information, but can still be inserted normally.
(2) if the system requires that the time range be used as a condition for record retrieval. This is also a big headache. You cannot use an API provided by MySql to record time with strings. the code for retrieving time ranges may be stripped from the database. This will inevitably affect the performance. For example, if you want to query 1 million pieces of data in a partition on the 1992-3-12-1992-3-13 day from 100 pieces of data, you may have to retrieve 1 million pieces of data and then develop a new method for filtering.
In addition, if the time precision of MySql to 4.1 is only seconds.
To record more detailed time granularity. You can consider constructing DateTime.
Record DateTime. trick ().
This is just an idea, and there are no additional questions that have not yet been proved. /
