create tableCREATE TABLE `test` (
`StartTime` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1SELECT FROM_UNIXTIME(1295319990687/1000) a,FROM_UNIXTIME(1295320006203/1000) b,FROM_UNIXTIME(1295320016390/1000) c, FROM_UNIXTIME(1295320025906/1000) d;
a b c d
2011-01-18 11:06:31 2011-01-18 11:06:46 2011-01-18 11:06:56 2011-01-18 11:07:06INSERT INTO test VALUES(1295319990687),(1295320006203),(1295320016390),(1295320025906);
SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000 FROM test;
FROM_UNIXTIME(StartTime/1000) StartTime/10002011-01-18 11:05:58 1295319957.504
2011-01-18 11:05:58 1295319957.504
2011-01-18 11:05:58 1295319957.504
2011-01-18 11:08:09 1295320088.576两者得出的结果怎么不一致呢？
00) b,FROM_UNIXTIME(1295320016390/1000) c, FROM_UNIXTIME(1295320025906/1000) d;
+---------------------+---------------------+---------------------+-------------
--------+
| a | b | c | d
|
+---------------------+---------------------+---------------------+-------------
--------+
| 2011-01-18 11:06:31 | 2011-01-18 11:06:46 | 2011-01-18 11:06:56 | 2011-01-18 1
1:07:06 |
+---------------------+---------------------+---------------------+-------------
--------+
1 row in set (0.00 sec)mysql> SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000 FROM test;
+-------------------------------+----------------+
| FROM_UNIXTIME(StartTime/1000) | StartTime/1000 |
+-------------------------------+----------------+
| 2011-01-18 11:06:31 | 1295319990.687 |
| 2011-01-18 11:06:46 | 1295320006.203 |
| 2011-01-18 11:06:56 | 1295320016.39 |
| 2011-01-18 11:07:06 | 1295320025.906 |
+-------------------------------+----------------+
4 rows in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1295319990687/1000) a,FROM_UNIXTIME(1295320006203/10
00) b,FROM_UNIXTIME(1295320016390/1000) c, FROM_UNIXTIME(1295320025906/1000) d \
G
*************************** 1. row ***************************
a: 2011-01-18 11:06:31
b: 2011-01-18 11:06:46
c: 2011-01-18 11:06:56
d: 2011-01-18 11:07:06
1 row in set (0.01 sec)mysql> INSERT INTO test VALUES(1295319990687),(1295320006203),(1295320016390),(
1295320025906);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from test;
+---------------+
| StartTime |
+---------------+
| 1295319990687 |
| 1295320006203 |
| 1295320016390 |
| 1295320025906 |
+---------------+
4 rows in set (0.03 sec)mysql> SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000 FROM test;
+-------------------------------+----------------+
| FROM_UNIXTIME(StartTime/1000) | StartTime/1000 |
+-------------------------------+----------------+
| 2011-01-18 11:06:31 | 1295319990.687 |
| 2011-01-18 11:06:46 | 1295320006.203 |
| 2011-01-18 11:06:56 | 1295320016.39 |
| 2011-01-18 11:07:06 | 1295320025.906 |
+-------------------------------+----------------+
4 rows in set (0.00 sec)mysql>
+-------------------------------+----------------+---------------+
| FROM_UNIXTIME(StartTime/1000) | StartTime/1000 | StartTime |
+-------------------------------+----------------+---------------+
| 2011-01-18 11:06:31 | 1295319990.687 | 1295319990687 |
| 2011-01-18 11:06:46 | 1295320006.203 | 1295320006203 |
| 2011-01-18 11:06:56 | 1295320016.39 | 1295320016390 |
| 2011-01-18 11:07:06 | 1295320025.906 | 1295320025906 |
+-------------------------------+----------------+---------------+
4 rows in set (0.00 sec)mysql>
ALTER TABLE test MODIFY COLUMN StartTime FLOAT ;
DELETE FROM test;
INSERT INTO test VALUES(1295319990687),(1295320006203),(1295320016390),(1295320025906),(123456789);
SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000,StartTime FROM test;
FROM_UNIXTIME(StartTime/1000) StartTime/1000 StartTime
2011-01-18 11:05:58 1295319957.504 1.29532e+012
2011-01-18 11:05:58 1295319957.504 1.29532e+012
2011-01-18 11:05:58 1295319957.504 1.29532e+012
2011-01-18 11:08:09 1295320088.576 1.29532e+012
1970-01-02 18:17:37 123456.792 1.23457e+008ALTER TABLE test MODIFY COLUMN StartTime DOUBLE ;
DELETE FROM test;
INSERT INTO test VALUES(1295319990687),(1295320006203),(1295320016390),(1295320025906),(123456789);
SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000,StartTime FROM test;
FROM_UNIXTIME(StartTime/1000) StartTime/1000 StartTime
2011-01-18 11:06:31 1295319990.687 1295319990687
2011-01-18 11:06:46 1295320006.203 1295320006203
2011-01-18 11:06:56 1295320016.39 1295320016390
2011-01-18 11:07:06 1295320025.906 1295320025906
1970-01-02 18:17:37 123456.789 123456789我改成double 就可以了 这是为何？
1.29532e+012
double 显示的是数值
1295319990687
Date date = new Date();
conn = DriverManager.getConnection("jdbc:mysql://localhost/test", proper);
String querySql = "select StartTime from test";
String insertSql = "INSERT INTO test VALUES(" + date.getTime() + ")";
String alterDoubleSql = "ALTER TABLE test MODIFY COLUMN StartTime Double";
String alterFloatSql = "ALTER TABLE test MODIFY COLUMN StartTime FLOAT";
String deleteSql = "Delete from test"; insertprepareStatement = conn.prepareStatement(insertSql);
queryPrepareStatement = conn.prepareStatement(querySql);
alterDoublePrepareStatement = conn.prepareStatement(alterDoubleSql);
alterFloatPrepareStatement = conn.prepareStatement(alterFloatSql);
deleteStatement = conn.prepareStatement(deleteSql); alterFloatPrepareStatement.executeUpdate();
deleteStatement.executeUpdate();
insertprepareStatement.executeUpdate();
ResultSet floatResult = queryPrepareStatement.executeQuery();
while (floatResult.next()) {
System.out.println("When StartTime's Type is Float,Insert Value is " + date.getTime());
System.out.println("Get Float" + "\t" + "Get Double");
System.out.println(floatResult.getFloat(1) + "\t" + floatResult.getDouble(1));
}
deleteStatement.executeUpdate();
alterDoublePrepareStatement.executeUpdate();
System.out.println();
insertprepareStatement.executeUpdate();
ResultSet doubleResult = queryPrepareStatement.executeQuery();
while (doubleResult.next()) {
System.out.println("When StartTime's Type is Double,Insert Value is " + date.getTime());
System.out.println("Get Float" + "\t" + "Get Double");
System.out.println(doubleResult.getFloat(1) + "\t" + doubleResult.getDouble(1));
}The execution result is
When StartTime's Type is Float,Insert Value is 1295333211671
Get Float Get Double1
1.29533005E12 1.29533E12 When StartTime's Type is Double,Insert Value is 1295333211671
Get Float Get Double
1.2953332E12 1.295333211671E12Conclusion:
Only when we change the type of StartTime to Double, we can find the minimun data error through by getDouble(1).
0.21999979