Resolve the time types of MySql and Java
- 2020-06-15 07:58:07
- OfStack
The time type of 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
See reference manual of MySql
Date:
A date. The supported range is '1000-01-01' to '9999-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 ranges from 1000-01-01 to 9999-12-31.
MySql displays the field as ES25en-ES26en-ES27en. Add this type of field data, either as a string or as a number
Because fields of type Date only record date information, if the added data contains time information, the time information is automatically truncated.
If you want to save time information, consider using the DateTime type.
After testing, we found the following two ways to populate the Date type field:
By string:
insert into time_table CreateDate values (' 2007-04-09 ')
According to the number:
insert into time_table(CreateDate) values(20070409)
The fetch can be obtained with type ES47en.sql.Date
The code is:
Date dtDate =rsBuffer.getDate("CreateDate");
The test code is as follows :(where IDBFace is a simple class encapsulated by JDBC and accepts Sql to operate on the database)
Implementation result: 2007-04-09
DateTime
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-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. Date, on the other hand, only records date information. The data of MySql is formatted according to ES71en-ES72en-ES73en :MM:SS, allowing submission in the form of strings and Numbers.
For example, submit in numerical form:
insert into time_table(CreateDate) values(20070409132013)
This type of data can be obtained using the type ES82en.sql.Timestamp
The code is as follows:
Implementation result: 2007-04-09 13:20:13.0
TimeStamp
A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. 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 the DateTime type
Range 1970-01-01, year 2037, accuracy: 1 second /
If a column of type Timestamp is not assigned in Sql, the column is constructed as the current time.
Submitting the NULL value also causes the column to be entered at the current time.
If the time commits incorrectly, the column will be filled in with 0.
Timestamp requires less storage than the DateTime type, requiring only four bytes compared to DateTime's eight bytes.
But there is one caveat. Timestamp can only represent a time range of 1970-2037.
Use Timestamp1 to ensure that the time data 1 is committed does not exceed this range.
The code is with the DateTime class, and I don't like using it, so I'm skipping it.
Time:
A time. The range is '-838:59:59' to '838: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 only records time information and does not include date information.
The range is -838:59:59 to 838:59:59. MySql formats the data with HH:MM:SS, allowing input as a string or number.
Code:
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, 4-bit and 2-bit.
The default is 4 bits. Its range is 1901-2155
The two-bit representation records only the last two bits. Its range is 1970-2069
Allows insertion as a string or number.
Code:
Implementation results :2007
It should be noted that:
The Date () method goes back to how many years have passed since 1900. So in order to show the correct time, you have to add 1900.
This method has been abandoned.
In addition.
One way is to record time without using any of the above types.
Instead, the time is recorded in char(or vchar).
This makes it easier to insert data and display records without any conversion.
But there are two important drawbacks to bear.
(1) Separate methods should be developed to verify the validity of time data. For example, the ajidjieoa string is not a time message, but can still be inserted normally.
(2) If the system needs to take the time range as the condition for record retrieval. This could also be a big problem. Recording time with a string will not be possible using API for time. Code for time range retrieval may be separated from the database. This will inevitably affect performance. For example, to get a single hundred entries in the range 1992-3-12, 1992-3-13, out of a million entries, you might have to get all of the 1,000,000 entries and develop a new way to filter them.
In addition, MySql to 4.1 time accuracy if only up to seconds.
Record a finer granularity of time. Consider constructing DateTime.
Record DateTime. trick ().
This is just an idea, and there are no additional questions to prove. /
date java.sql.Date
Datetime java.sql.Timestamp
Timestamp java.sql.Timestamp
Time java.sql.Time
Year java.sql.Date
Analyze it
See reference manual of MySql
Date:
A date. The supported range is '1000-01-01' to '9999-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 ranges from 1000-01-01 to 9999-12-31.
MySql displays the field as ES25en-ES26en-ES27en. Add this type of field data, either as a string or as a number
Because fields of type Date only record date information, if the added data contains time information, the time information is automatically truncated.
If you want to save time information, consider using the DateTime type.
After testing, we found the following two ways to populate the Date type field:
By string:
insert into time_table CreateDate values (' 2007-04-09 ')
According to the number:
insert into time_table(CreateDate) values(20070409)
The fetch can be obtained with type ES47en.sql.Date
The code is:
Date dtDate =rsBuffer.getDate("CreateDate");
The test code is as follows :(where IDBFace is a simple class encapsulated by JDBC and accepts Sql to operate on the database)
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);
// To 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();
}
Implementation result: 2007-04-09
DateTime
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-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. Date, on the other hand, only records date information. The data of MySql is formatted according to ES71en-ES72en-ES73en :MM:SS, allowing submission in the form of strings and Numbers.
For example, submit in numerical form:
insert into time_table(CreateDate) values(20070409132013)
This type of data can be obtained using the type ES82en.sql.Timestamp
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);
// To 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();
}
Implementation result: 2007-04-09 13:20:13.0
TimeStamp
A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. 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 the DateTime type
Range 1970-01-01, year 2037, accuracy: 1 second /
If a column of type Timestamp is not assigned in Sql, the column is constructed as the current time.
Submitting the NULL value also causes the column to be entered at the current time.
If the time commits incorrectly, the column will be filled in with 0.
Timestamp requires less storage than the DateTime type, requiring only four bytes compared to DateTime's eight bytes.
But there is one caveat. Timestamp can only represent a time range of 1970-2037.
Use Timestamp1 to ensure that the time data 1 is committed does not exceed this range.
The code is with the DateTime class, and I don't like using it, so I'm skipping it.
Time:
A time. The range is '-838:59:59' to '838: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 only records time information and does not include date information.
The range is -838:59:59 to 838:59:59. MySql formats the data with HH:MM:SS, allowing input as a string or number.
Code:
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);
// To 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, 4-bit and 2-bit.
The default is 4 bits. Its range is 1901-2155
The two-bit representation records only the last two bits. Its range is 1970-2069
Allows insertion as a string or number.
Code:
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);
// To 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();
}
Implementation results :2007
It should be noted that:
The Date () method goes back to how many years have passed since 1900. So in order to show the correct time, you have to add 1900.
This method has been abandoned.
In addition.
One way is to record time without using any of the above types.
Instead, the time is recorded in char(or vchar).
This makes it easier to insert data and display records without any conversion.
But there are two important drawbacks to bear.
(1) Separate methods should be developed to verify the validity of time data. For example, the ajidjieoa string is not a time message, but can still be inserted normally.
(2) If the system needs to take the time range as the condition for record retrieval. This could also be a big problem. Recording time with a string will not be possible using API for time. Code for time range retrieval may be separated from the database. This will inevitably affect performance. For example, to get a single hundred entries in the range 1992-3-12, 1992-3-13, out of a million entries, you might have to get all of the 1,000,000 entries and develop a new way to filter them.
In addition, MySql to 4.1 time accuracy if only up to seconds.
Record a finer granularity of time. Consider constructing DateTime.
Record DateTime. trick ().
This is just an idea, and there are no additional questions to prove. /