FROM_UNIXTIME 问题

nianzhang747 2011-01-18 11:43:08

create table

CREATE TABLE `test` (

   `StartTime` mediumtext

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1



SELECT 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:06



INSERT INTO  test VALUES(1295319990687),(1295320006203),(1295320016390),(1295320025906);

SELECT FROM_UNIXTIME(StartTime/1000),StartTime/1000 FROM test;

FROM_UNIXTIME(StartTime/1000)	StartTime/1000



2011-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


两者得出的结果怎么不一致呢？

...全文
364 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
可否贴出你的表结构？
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
为什么？
小小小小周 2011-01-18
  • 打赏
  • 举报
 回复
1295319990687/1000 这个应该等于 1295319990.687 ,而楼主的是1295319957.504....
小小小小周 2011-01-18
  • 打赏
  • 举报
 回复 
mysql> SELECT FROM_UNIXTIME(1295319990687/1000) a,FROM_UNIXTIME(1295320006203/10

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)
小小小小周 2011-01-18
  • 打赏
  • 举报
 回复
...那就难怪了,
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
是float类型的问题
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
System.out.println((7.22f-7.0f));
0.21999979
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
Java Code:
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.295333211671E12

Conclusion:
Only when we change the type of StartTime to Double, we can find the minimun data error through by getDouble(1).
ACMAIN_CHM 2011-01-18
  • 打赏
  • 举报
 回复
很简单，你根本不是用的你自己提供的测试用例。
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
知道了
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
我发现一点 你们注意看 float 显示的是e指数表示法
1.29532e+012
double 显示的是数值
1295319990687
nianzhang747 2011-01-18
  • 打赏
  • 举报
 回复
这难道和系统有关系？

DESC test;
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+008


ALTER 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 就可以了 这是为何？
小小小小周 2011-01-18
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 nianzhang747 的回复:]

可否贴出你的表结构？
[/Quote]

我就是用你的语句建立的.
ACMAIN_CHM 2011-01-18
  • 打赏
  • 举报
 回复 
mysql> 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 |

+-------------------------------+----------------+---------------+

4 rows in set (0.00 sec)



mysql>
ACMAIN_CHM 2011-01-18
  • 打赏
  • 举报
 回复
用的你的测试数据。
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: 0



mysql> 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>
mysql from_unixtime(_Mysql中的FROM_UNIXTIME实现按天查询
近期做了一套战绩系统，战绩要按天查询，每天赢了多少...接下来，开始编辑语句FROM_UNIXTIME函数支持转换时间戳为字符串，我们把时间戳转换为日期FROM_UNIXTIME( create_time, '%Y年%m月%d日' )然后，根据这个日期分...
sql 时间转换问题 from_unixtime() UNIX_TIMESTAMP()
[sql] view plaincopyprint? ...from_unixtime()是MySQL里的时间函数  ...date为需要处理的参数(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串...mysql>SELECT FROM_UNIXTIME( 1249488000
SQL的from_unixtime函数的用法实例
1.from_unixtime的语法及用法 （1）语法：from_unixtime(string,date_) 参数说明 （2）用法： 2.实例
mysql from_unixtime(_MySQL from_unixtime()函数
FROM_UNIXTIME(unix_timestamp)FROM_UNIXTIME(unix_timestamp,format)返回表示UNIX_TIMESTAMP参数作为'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的一个日期时间值，根据该函数是否用在字符串或数字语境中。...
unix_timestamp、from_unixtime时间戳函数的使用
最近在做一个关于Spark sql清洗数据的项目，其中需要用到正则表达式及一些函数，在这里简单总结一下unix_timestamp、from_unixtime的用法，仅供参考。 一、unix_timestamp函数用法  1、UNIX_TIMESTAMP() ：若无...
MySQL

56,940

社区成员

56,756

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章