Time and date type transfer between Java and MySQL [original]

The time and date types of MySQL (Version: 5.1.50) are as follows:

Datetime 8 bytes XXXX-XX: XX 1000-01-01 00:00:00 to 9999-12-31 23:59:59
Timestamp 4 bytes XXXX-XX: XX 1970-01-01 00:00:01 to 2038
Date 3 bytes XXXX-XX-xx 1000-01-01 to 9999-12-31
Year 1 bytes XXXX 1901 to 2155
Time 3 bytes XX: XX-838: 59: 59 to 838: 59: 59 (to satisfy the time addition and subtraction operation)

 

 

Java (1.6) can save the time and date types of classes mainly include

Java. util. Date

Java. util. Calendar

Java. SQL. Date

Java. SQL. Time

Java. SQL. Timestamp

 

The time and date types previously queried from MySQL are stored in the Java. util. date type. This brings about a series of problems. First, this class provides too few time operation functions and generally needs to be converted to Java. util. then perform the calendar operation, even if Java is used. util. calendar is not very convenient. A very simple idea requires a lot of code to be written. java. util. the data content of date is XXXX-XX-xx
XX: XX. Sometimes time is not required, but a date is required. When the date type value from the database is placed in this class, the current time will be automatically filled in the time bit. This makes the two dates equal in the database. It is a headache to consider the time error.

 

Java provides three data types for convenient interaction with MySQL.

Java. SQL. Date

Java. SQL. Time

Java. SQL. Timestamp

They all inherit java. util. date, which is a streamlined class and suitable for interacting with databases.

 

============ Java injection database ==========

Successful Java-type MySQL type
Date Yes
Date time no
Date timestamp No
Date datetime No

Time date no
Time time Yes
Time timestamp No
Time datetime No

Timestamp date Yes
Timestamp time Yes
Timestamp timestamp Yes
Timestamp datetime Yes
=========== End Java injection database ========
General rule. If a completely contains B, A can inject data into B; otherwise, an error is returned.

 

============ Extract from the database to Java ============

Whether the MySQL type is of the Java type
Date Yes
Date time Yes -------------- use calendar elements for the missing parts
Date timestamp Yes -------------- the missing parts use the calendar Element

Time date Yes -------------- use calendar elements for the missing parts
Time time Yes
Time timestamp Yes -------------- The amount of experience that is missing

Timestamp date Yes
Timestamp time Yes
Timestamp timestamp Yes

Datetime date Yes
Datetime time Yes
Datetime timestamp Yes
============ End extracts data from the database to Java ========
No error. The missing part uses the calendar, instead of the current date and time.

 

 

 

 

 

Null to dB (null) ====> is also null
Null to dB (not null) ======> Database Error
DB (null) to Java ==========> if a single field comes out, the entire entity is null. If it comes out with other fields that are not null, entity can be instantiated, and its field is still null
DB (not null) to Java ==========> if the date is included, an error is returned. Otherwise, it is 000.
Optimal Solution, defined as null

Java. SQL Time System Computing Series

After, before
Compareto <0, equal to return = 0, greater than return> 0

Advantage: similar to databases, it can be easily transmitted (from dB to SRC or in the reverse direction) to facilitate the comparison of sizes.
Disadvantage: the lack of operation units is not suitable for time skip operations and interval operations

Conclusion: calendar has powerful skip and interval computing capabilities. You can convert the Time of the SQL series to calendar Ar as needed.
First, set the calendar as the calendar element, convert from the SQL Series time, and then switch back to the SQL Series time.
Calendar is only used for time jump conversion. Compared with computation, the SQL System is used for unified, so that the code is clearer.

 

 

 

 

How to initialize date and calendar to Greenwich Mean Time

New Date (0)
Calendar. settimeinmillis (0)

SQL Series Time

Static valueof

New XX (0) Get calendar
New XX (Year + 1900, month + 1, day, hour, minute, second, nano) is outdated, and creation is correct.

Tostring or simpledateformat