MySql and Java time type [transfer]

2011-05-03  来源:本站原创  分类:Database  人气:99 

MySql time types in the corresponding time Java type
date java.sql.Date
Datetime java.sql.Timestamp
Timestamp java.sql.Timestamp
Time java.sql.Time
Year java.sql.Date

Analyze the reference manual reference 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 records the date information that range from 1000-01-01 to 9999-12-31.
MySql according to YYYY-MM-DD approach to the display of such fields. Adding such field data, which can use a string type, you can also use the numeric type

As the only record field of type Date date information, so if you add the data includes time information, the time information will be automatically truncated.
If you want to save the time information, consider using the DateTime type.

Tested and found the following two methods can be populated with the Date type field:
By string:
insert into time_table (CreateDate) values ​​('2007-04-09 ')
According to figures:
insert into time_table (CreateDate) values ​​(20070409)

Java.sql.Date type can be used to obtain access code:
Date dtDate = rsBuffer.getDate ("CreateDate");

Test code is as follows: (where, IDBFace their own based on a simple JDBC wrapper class to accept Sql operation of 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);

/ / 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 ();
}
Results of the implementation: 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: DateTime can record the date and time information. Only records the date and the Date information. That range from: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 MySql in accordance with YYYY-MM-DD HH: MM: SS format the data, allows for strings and numbers the submission.

Such as the number of ways to submit:
insert into time_table (CreateDate) values ​​(20070409132013)

To get the type of data you can use: java.sql.Timestamp type 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);
/ / 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 ();
}
Results of the implementation: 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.
And the DateTime type is very similar to the range of 1970-01-01 -2037, the precision of 1 second /
If the Sql Timestamp type is not listed on the assignment, the column will be constructed to the current time.
NULL values ​​will be submitted to the current time, the column entry.
If the time to submit an error, the column will be filled with 0.
Timestamp than the DateTime type required storage space, and requires only 4 bytes, and DateTime requires 8 bytes.
But one thing needs special attention. Timestamp that time only the range of 1970-2037.
Timestamp must be submitted using the time to ensure that the data will not exceed this range.

Code and the DateTime class is, and I do not like to use, so a little lost.
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 recording time information only, does not contain date information.
Range -838:59:59 to 838:59:59, MySql to HH: MM: SS format the data that allows the input of 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);
/ / 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 ();
}

The results: 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 two kinds of representation, 4 and 2 of the.
The default is 4. The range of 1901-2155
2 presentation method only records 2. The range of 1970-2069
Allowed to insert 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);
/ / 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 ();
}
The results: 2007
Be noted that:
Date.getYear () method returns to 1900 after many years. Therefore, in order to show the correct time, must be added 1900.
This method has been abandoned.

Another.
One way is: do not use any of the above type to record the time.
But in the char (or vchar) way to record the time.
In doing so, insert data and display records when the conversion is certainly not any more convenient.
However, to take two important flaws.
(1) develop methods to separately verify the legitimacy of time data. For example ajidjieoa string is not a time information, but can still be properly inserted.
(2) If the conditions required to be recorded as a time range to retrieve. This will be a big trouble. Recording time with the string can not use MySql to time to provide the API. For the time frame and the database retrieval code may be stripped. This will inevitably impact on performance. For example, data from 1 million to query time range 1992-3-12 -1992-3-13 a mere 100 days of data, you may have to 1 million data is checked out, the development of new methods filter.

In addition, MySql Maoruo accuracy to 4.1 hours to only seconds.
To record a finer granularity of the time. You should consider DateTime.
Record DateTime.trick ().
This is just an idea, there is no additional problem has not been proven. /

This switched
http://www.java3z.com/cwbwebhome/article/article8/81153.html

相关文章
  • MySql and Java time type [transfer] 2011-05-03

    MySql time types in the corresponding time Java type date java.sql.Date Datetime java.sql.Timestamp Timestamp java.sql.Timestamp Time java.sql.Time Year java.sql.Date Analyze the reference manual reference MySql Date: A date. The supported range is '

  • MySql and Java time type 2010-04-14

    MySql time types are Java types in the corresponding time date java.sql.Date Datetime java.sql.Timestamp Timestamp java.sql.Timestamp Time java.sql.Time Year java.sql.Date Analyze the reference manual reference MySql Date: A date. The supported range

  • mysql and java data type mapping 2010-12-01

    Numeric Integer JDBC tinyint java.lang.Integer smallint mediumint java.lang.Long int bigint java.math.BigInteger Single-precision floating point type JDBC float java.lang.Float Double-precision floating point type JDBC double java.lang.Double Other J

  • mysql orcale java data type with the control 2011-08-24

    Number two types of oracle in 1.Number 2.Number (10,2) The first corresponds to the java in shaping int long short byte While the second corresponds to the java in the floating-point float double The date corresponds to the oracle in java in java.uti

  • mysql oracle java type 2010-03-30

    Mysql Oracle Java BIGINT NUMBER (19,0) java.lang.Long BIT RAW byte [] BLOB BLOB RAW byte [] CHAR CHAR java.lang.String DATE DATE java.sql.Date DATETIME DATE java.sql.Timestamp DECIMAL FLOAT (24) java.math.BigDecimal DOUBLE FLOAT (24) java.lang.Double

  • java data type conversion (change) 2010-09-01

    Transfer from http://hi.baidu.com/loveland/blog/item/eb2d96456e64d13a8694737b.html 1 How to convert integer string String int? A. There are two ways: 1). Int i = Integer.parseInt ([String]); or i = Integer.parseInt ([String], [int radix]); 2). Int i

  • Java BigDecimal type on the amount of the algorithm 2010-09-02

    In Java, you can use the float, said float this is common sense, but the float's precision is problematic, if the amount involved, it generally can not be used float type, but the use of BigDecimal, this is a need to know the criteria. Commodities, j

  • 解析MySql与Java的时间类型 2014-11-11

    本篇文章是对MySql与Java的时间类型进行了详细的分析介绍,需要的朋友参考下 MySql的时间类型有 Java中与之对应的时间类型date java.sql.Date Datetime java.sql.Timestamp Timestamp java.sql.Timestamp Time java.sql.Time Year java.sql.Date 对其进行分析参考MySql 的reference manual Date:A date. The supported range is '

  • JAVA data type data type-HIBERNATE - Standard SQL data type correspondence table 2009-04-18

    Java data type Hibernate type of data standard SQL data types (PS: for different DB may vary) byte, java.lang.Byte byte TINYINT short, java.lang.Short short SMALLINT int, java.lang.Integer integer INGEGER long, java.lang.Long long BIGINT float, java.

  • java data type, hibernate data type, sql type of data standards for correspondence tables between 2009-08-22

    Java data type Hibernate Data Type Standard SQL data types (PS: for different DB may vary) byte, java.lang.Byte byte TINYINT short, java.lang.Short short SMALLINT int, java.lang.Integer integer INGEGER long, java.lang.Long long BIGINT float, java.lan

  • java data type conversion Xiangjie 2010-03-29

    Java data types are divided into three categories, namely, the Boolean type, character and numeric type, which are divided into numeric integer and floating-point type; relative to the data types, Java variable of type Boolean boolean; character char

  • The use of high-end Java enumerated type enum Xiangjie (reproduced) 2010-03-21

    From: http://tech.e800.com.cn/articles/2009/97/1252286198897_1.html java enumerated type of Enum finally j2se1.5 appeared. Previously we thought that it was just tasteless only dispensable. After all these years, without it, we do not have too nicely

  • java parameter type three points 2010-04-05

    java parameter type three points Starting from Java 5, Java language support for a new formulation method parameters, varargs (variable-length argument lists), the syntax is the type followed by ..., said here to accept the parameter to 0 to more tha

  • Java data type, Hibernate data types, standard sql data type correspondence table between 2010-05-13

    Hibernate API Introduction Its interface is divided into the following categories: l provide access to the database operation interface; l used to configure the Hibernate interfaces; l callback interfaces l extend Hibernate functionality of the inter

  • java data type conversion Integer String Long Float Double Date 2010-07-24

    1 How to convert integer string String int? A. There are two ways: 1). Int i = Integer.parseInt ([String]); or i = Integer.parseInt ([String], [int radix]); 2). Int i = Integer.valueOf (my_str). IntValue (); Note: The string converted to Double, Floa

  • java data type conversion 2010-12-05

    Some beginners may encounter friends JAVA JAVA data type conversion between the distress, for example, integer and float, double type conversion between integer and String conversion between types, as well as processing, display timing of the problem

  • Java generic concept (transfer) 2010-12-11

    Java1.5 is one of the new features added generics, I combine the following IBM java documentation and data sorting. BS, straightforward begin One. The concept of generic Generics (Generic type or generics) is a Java language type system of an extensi

  • mysql The used table type doesn't support FULLTEXT indexes 2011-05-19

    This error: mysql The used table type doesn't support FULLTEXT indexes That does not support the full-text index, the solution is also quite simple, open the my.ini, search default-storage-engine =, your search should be the default-storage-engine =

  • sql server 2000 with java data type mapping 2011-04-27

    SQL data types and Java data type mapping 2007-09-10 08:54 due to a database table field type and Java data types do not correspond to good, cause the program wrong, special leave to find a correspondence between its mark. The following shows the SQL

  • Use of high-end Java enumerated type enum Detailed 2011-05-04

    Understanding of the following two main uses: EnumSet stateSet = EnumSet.allOf (State.class); for (State s: stateSet) { System.out.println (s); } EnumMap stateMap = new EnumMap ( State.class); stateMap.put (State.ON, "is On"); stateMap.put (Stat