Java MySQL Date type

The time and date types for MySQL (version: 5.1.50) are as follows:

DateTime 8bytes xxxx-xx-xx xx:xx:xx 1000-01-01 00:00:00 to 9999-12-31 23:59:59
Timestamp 4bytes xxxx-xx-xx xx:xx:xx 1970-01-01 00:00:01 to 2038
Date 3bytes xxxx-xx-xx 1000-01-01 to 9999-12-31
Year 1bytes xxxx 1901 to 2155
Time 3bytes xx:xx:xx-838:59:59 to 838:59:59 (in order to satisfy the addition and subtraction of times)

Classes in Java (1.6) that can hold time and date types are primarily

Java.util.Date

Java.util.Calendar

Java.sql.Date

Java.sql.Time

Java.sql.Timestamp

The time and date types that were previously queried from MySQL are placed in the java.util.Date type. This brings a series of problems, first of all, this class provides the time operation function is too few, generally need to convert to Java.util.Calendar again to operate; Java.util.Calendar, is not very convenient, a very simple idea, need to write a lot of code to achieve,java.util.Date data content is xxxx-xx-xx xx:xx:xx, sometimes do not need time, only need the date. When a date type that is taken from a database is placed in this class, the current time is automatically added to the time bit. This makes the original two dates in the database is equal, take out to put in this class is no longer equal, need to consider the time error, it is a headache.

Java provides three types of data that are easy to interact with MySQL

Java.sql.Date

Java.sql.Time

Java.sql.Timestamp

They are inherited java.util.Date, which is a simplification of the class, and it is good for interacting with the database.

===========java Injection Database ==========

Java type MySQL type success or not
Date 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, then a can inject data to B, otherwise the error

========== extract from database to Java ==========

MySQL type Java type or not
Date Date Yes
Date Time Yes--------------missing part using the calendar element
Date timestamp Yes--------------missing part using the calendar element

Time Date Yes--------------missing part using the calendar element
Time Time Yes
Time timestamp Yes--------------missing part using the calendar element

Timestamp Date Yes
Timestamp time Yes
Timestamp timestamp Yes

DateTime Date Yes
DateTime Time Yes
DateTime timestamp Yes
==========end extracting from database to Java=======
No errors, missing parts use the calendar, not the current date time

NULL to DB (NULL) =====> is also null
NULL to DB (NOT NULL) =======> database error
db (NULL) to java==========> if the single field is out, the entire entity is NULL, and if you come out with other fields that are not NULL, you can instantiate the entity, and the field itself is still null
DB (not NULL) to java==========> if the date is included, an error is received, otherwise 000
Optimal solution, defined as nullable

java.sql Time System Operation series

After,before
CompareTo the original is less than the parameter returns <0, equals return = 0, greater than return >0

Advantages: The same type of database, can be easily transmitted (whether from DB to src or reverse direction), easy to compare size
Cons: Lack of operational units, not suitable for time-hopping operations and interval operations

Summary: The calendar has a powerful leap and interval computing capability, and you can turn the time of the SQL series into calendar when needed.
Set Calendar to Calendar, then convert from SQL Series time, and then back to SQL series time.
The calendar is used only for time-hopping conversions, and the comparison operation uses the SQL system uniformly, so that the code will be clearer

How date and calendar are initialized to Greenwich mean

New Date (0)
Calendar.settimeinmillis (0)


SQL Series Time

Static valueOf


New XX (0) get the calendar element
New XX (year+1900, Month+1,day,hour,minute,second,nano) is obsolete and is created right

ToString or SimpleDateFormat

Java MySQL Date type