Parsing the time type of MySql and Java_PHP tutorial

The time type of MySQL is the corresponding time type in java
date java.sql.date Datetime java.Sql.timestamp
Timeststamp java.sql .TimeStamp
Time Java.sql.Time
year java.sql.date


Analysis of it
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 record date information, indicating the range is 1000-01-01 to 9999-12-31.
MySql displays this type of field in the YYYY-MM-DD format. To add this type of field data, you can use either the string type or the numeric type

Since the Date type field only records date information, if the added data contains time information, the time information will automatically truncated.
If you want to save time information, consider using the DateTime type.
After testing, it was found that the following two methods can be used to fill the Date type field:

By string:
insert into time_table(CreateDate) values('2007-04-09' )
By number:
insert into time_table(CreateDate) values(20070409)
Obtaining can be obtained using the java.sql.Date type
The code is :
Date dtDate =rsBuffer.getDate("CreateDate");
The test code is as follows: (Among them, IDBFace is a simple class encapsulated by itself based on JDBC, which accepts Sql to operate the database)

Copy code The code is as follows:

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());
                                                                                                                                                                     System.out.println(dtDate.toString());
Execution 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 only records date information. The representation range is: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 MySql formats data according to YYYY-MM-DD HH:MM:SS, allowing strings and numbers submission method.

For example, to submit in the form of numbers:
insert into time_table(CreateDate) values(20070409132013)
To obtain this type of data, you can use: java.sql.Timestamp type
The code is as follows:

Copy codeThe 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();
}

Execution 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 DateTime type
The range is 1970-01-01 – 2037 and the precision is 1 second/
If the Timestamp type is not specified in Sql Column assignment, the column will be constructed as the current time.
Submitting a NULL value will also cause the column to be entered at the current time.
If the time is submitted incorrectly, this column will be filled with 0.
Timestamp requires less storage space than the DateTime type, requiring only 4 bytes, while DateTime requires 8 bytes.
But there is one thing that needs special attention. Timestamp can only represent the time range from 1970 -2037.
When using Timestamp, you must ensure that the time data submitted does not exceed this range.
The code is related to the DateTime class, and I don’t like to use it, so I omitted 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, not date information.
The range is -838:59:59 to 838:59:59. MySql formats the data in HH:MM:SS, allowing input to be strings or numbers.
Code:

Copy code The code is as follows:

public void testTime() throws SQLException
       {
              IDBFace DBFace =DBFactory.createMySqlFace();
              DBFace.connect();
              //清空表
              String strDelete ="delete from time_table";
              DBFace.update(strDelete);
              //添加

              String strInsert ="insert into time_table(CreateTime) values(131211)";
              DBFace.update(strInsert);
              //获取
              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();
       }

执行结果: 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可以有2种表示方式，4位的和2位的。
默认情况是4位。其范围为1901-2155
2位的表述法只记录后2位 。其范围为1970-2069
允许以字符串或者数字的方式插入。
代码:

复制代码 代码如下:

       public void testYear() throws SQLException
       {
              IDBFace DBFace =DBFactory.createMySqlFace();
              DBFace.connect();
              //清空表
              String strDelete ="delete from time_table";
              DBFace.update(strDelete);
              //添加

              String strInsert ="insert into time_table(CreateYear) values(2007)";
              DBFace.update(strInsert);
              //获取
              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();
       }

执行结果:2007
需要说明的是:
Date.getYear()方法返回至1900年起经过了多少年。所以为了显示正确的时间，必须加上1900.
该方法已经被废弃。

另外。
有一种方法是:不使用上述任何一种类型来记录时间。
而是以char(或vchar)的方式来记录时间。
这样做在插入数据和显示记录的时候固然不用进行任何转换而比较方便。
但是要承担2个重要的缺陷。
(1) 要单独开发方法验证时间数据的合法性。例如ajidjieoa字符串不是一个时间信息，但仍然可以正常插入。
(2) 如果系统需将时间范围做为条件进行记录检索。这也会是一个*烦。用字符串记录时间将无法使用MySql为时间提供的API.对时间范围检索的代码可能与数据库剥离。这样对性能必然造成影响。例如，要从100万条数据中查询时间范围为1992-3-12 –1992-3-13日的区区100条数据，你可能不得不将100万条数据都查出来，再开发新的方法进行过滤。

另外，MySql到4.1时间精度貌若只到秒。
要记录更细的时间粒度。可以考虑构造DateTime.
记录DateTime.trick().
这只是一个想法，有没有额外的问题尚未证明。/

http://www.bkjia.com/PHPjc/327755.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/327755.htmlTechArticleMySql的时间类型有 Java中与之对应的时间类型 date java.sql.Date Datetime java.sql.Timestamp Timestamp java.sql.Timestamp Time java.sql.Time Year java.sql.Date 对其进...