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
to analyze It
Refer to MySQL reference manual
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, representing ranges from 1000-01-01 to 9999-12-31.
MYSQL makes the display of the fields in the yyyy-mm-dd manner. Add the Class field data, that is, you can use a string type, or you can use a numeric type
Because a date-type field only records dates information, if the added data contains time information, the time information is automatically truncated.
If you want to save time information, you can consider using datetime types.
After testing, you found 2 ways to populate a Date Type field:
by string:
Insert into time_table (createdate) VALUES (' 2007-04-09 ')
by Number:
Insert into time_table (createdate) VALUES (20070409)
gets that can be obtained using the java.sql.Date type
The code is:
Date dtdate =rsbuffer.getdate ("CreateDate");
The test code is as follows: (where Idbface is a simple class based on JDBC encapsulation that accepts SQL to manipulate the database)
Copy Code code as follows:
public void TestDate () throws SQLException
{
Idbface dbface =dbfactory.createmysqlface ();
Dbface.connect ();
Empty table
String strdelete = "Delete from time_table";
Dbface.update (Strdelete);
Add to
String Strinsert = "INSERT into time_table (createdate) VALUES (20070409)";
Dbface.update (Strinsert);
Get
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 results: 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 your 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 information is only logged. The range is: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 MySQL formats the data according to YYYY-MM-DD HH:MM:SS, allowing the submission of strings and numbers.
For example, to submit in a digital manner:
Insert into time_table (createdate) VALUES (20070409132013)
Get data of this type can be used: Java.sql.Timestamp type
The code is as follows:
Copy Code code as follows:
public void Testdatetime () throws SQLException
{
Idbface dbface =dbfactory.createmysqlface ();
Dbface.connect ();
Empty table
String strdelete = "Delete from time_table";
Dbface.update (Strdelete);
Add to
String Strinsert = "INSERT into time_table (createdatetime) VALUES (20070409132013)";
Dbface.update (Strinsert);
Get
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 results: 2007-04-09 13:20:13.0
TimeStamp
A timestamp. The range is ' 1970-01-01 00:00:00 ' to partway through of the year 2037. A TIMESTAMP column is useful to recording the date and time of A INSERT or UPDATE operation. The The TIMESTAMP column in a table are automatically set to the date and time of the most recent operation if Don ' t Assign it a value yourself. You can also set any TIMESTAMP column to "Current date and" by assigning it a NULL value.
Very similar to the datetime type
The range is 1970-01-01–2037 year, the accuracy is 1 seconds/
If you do not assign a value to a timestamp type column in SQL, the column is created to cause the current time.
Submitting a null value also causes the column to be entered at the current time.
If the time is submitted incorrectly, the column will be filled in 0.
Timestamp requires a smaller amount of storage space than a datetime type, requiring only 4 bytes, while a datetime requires 8 bytes.
But one thing needs special attention. Timestamp can only represent a time range of 1970-2037.
Use timestamp be sure to ensure that the time data submitted must not exceed this range.
The code and DateTime classes are, and I don't like to use them, so I skipped out.
Time :
A time. The range is ' -838:59:59 ' to ' 838:59:59 '. MySQL displays time values in ' HH:MM:SS ' format, but allows with assign values to time columns using either strings or N Umbers.
time only records temporal information and does not contain date information.
The range is -838:59:59 to 838:59:59, and MYSQL formats the data in HH:MM:SS, allowing input as a string or a number.
Code:
Copy Code code as follows:
public void Testtime () throws SQLException
{
Idbface dbface =dbfactory.createmysqlface ();
Dbface.connect ();
Empty table
String strdelete = "Delete from time_table";
Dbface.update (Strdelete);
Add to
String Strinsert = "INSERT into time_table (createtime) VALUES (131211)";
Dbface.update (Strinsert);
Get
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 and representing years from 1970 to 2069. MySQL displays year values in YYYY format, but allows with assign values to year columns using either strings or numbers . The year type was unavailable prior to MySQL 3.22.
Year can have 2 representations, 4-bit and 2-bit.
The default is 4-bit. Its range is 1901-2155
The 2-digit notation only records the latter 2 digits. Its range is 1970-2069
Allows insertion as a string or as a number.
Code:
Copy Code code as follows:
public void Testyear () throws SQLException
{
Idbface dbface =dbfactory.createmysqlface ();
Dbface.connect ();
Empty table
String strdelete = "Delete from time_table";
Dbface.update (Strdelete);
Add to
String Strinsert = "INSERT into time_table (createyear) VALUES (2007)";
Dbface.update (Strinsert);
Get
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
What needs to be stated is:
The Date.getyear () method returns to the number of years since 1900. So in order to show the right time, you have to add 1900.
The method has been discarded.
in addition.
One way to do this is to record time without using any of these types.
Instead, the time is recorded in char (or Vchar).
This is handy when inserting data and displaying records without having to do any conversion.
But there are 2 important flaws to bear.
(1) To develop methods to validate the legality of the time data separately. For example, the AJIDJIEOA string is not a time message, but can still be inserted normally.
(2) If the system needs to be the time range as a condition for record retrieval. It's going to be a big problem too. Recording time with a string will not be able to use MySQL as an API for time. The code that retrieves the time range may be stripped from the database. This has a definite effect on performance. For example, to query 1 million data for a few 100 data from a time range of 1992-3-12–1992-3-13 days, you may have to identify 1 million data and then develop new methods for filtering.
In addition, MySQL to 4.1 time precision appearance if only to seconds.
To record finer granularity of time. You can consider constructing DateTime.
Record Datetime.trick ().
It's just an idea that there are no additional questions yet to prove. /
