sql 整型数字如何转换成时间格式

[Tennessee3Waltz]

我mysql数据库中原始数据是bigint(20)类型的数字, 比如:
time product_num
20150711201734 45
20150711211153 36
20150712032254 10
. ...
由于我要统计近两千多条数据中：
1. 每天的product_num的总和（天是按照20：00-第二天的19：59为一天，而不是常用的00：00-23：59），和
2. 每周的product_num的总和. 但是目前的time其实不是date能认识的时间格式.

我试了很多类似于select substring(convert(char(14), time), 1,4)+'.'+ substring(convert(char(14), time), 5,6)+'.'+substring(convert(char(14), time), 7, 8) from table, 可是报错语法了。我不知道怎么把int变成data格式，然后怎么用data自带的算法。

请教大神这个sql语句改怎么写? 在线等大神, 提前感谢!

[代码誊写工]

字符串或类似字符串的整数转换为日期时间 str_to_date(20150711201734, '%Y%m%d%H%i%s') str_to_date('2015-07-11 20:17:34', '%Y-%m-%d %H:%i:%s')

[ACMAIN_CHM]

引用

MAKETIME(hour,minute,second) Returns a time value calculated from the hour, minute, and second arguments. mysql> SELECT MAKETIME(12,15,30); -> '12:15:30'

.

[Tennessee3Waltz]

二楼和三楼, 谢谢你们的回答. 我试了你们的语句, 结果都是语法报错, 似乎mysql不认识这些语句. 即便真的能用, 能否解答这个问题: 每天的product_num的总和（天是按照20：00-第二天的19：59为一天，而不是常用的00：00-23：59）. 我实在想不出来怎么解决.

[代码誊写工]

先用

select version();

查询一下版本号，并把结果贴出来。 再把

select str_to_date(20150711201734, '%Y%m%d%H%i%s');
SELECT MAKETIME(12,15,30);

错误的提示贴出来。 至于20:00到19:59为一天，如果不是很在乎速度的话，直接用函数把时间转换为(unix_timestamp(str_to_date(20150711201734, '%Y%m%d%H%i%s')) + 4 * 3600) div 86400再group by。例如一天（这种转过来的日期有时区差异，8*3600是用来转换时区的， 4 * 3600是吧20:00以后的当成第二天）

select ((unix_timestamp(str_to_date(`time`, '%Y%m%d%H%i%s')) + 8 * 3600 + 4 * 3600) div 86400) as `day`, sum(product_num) as value from table1 group by day;

在乎的话就在数据表里多建一个套自定义日期（年、月、日、时、分、秒都有）的列并做索引，插入数据的程序里直接写入一个转换好的自定义日期到各个列并作个复合索引，将来想怎么分组就怎么分组。

[Tennessee3Waltz]

version结果是5.0.87-community-nt 不知道为什么, str_to_date这个现在可以用了. 结合你的方法, 问题已经解决了. 非常感谢. 只是你答案中有些地方不太懂, 比如为何"4 * 3600是吧20:00以后的当成第二天"? 还有为何"div 86400"?

[代码誊写工]

引用 5 楼 Tennessee3Waltz 的回复:

version结果是5.0.87-community-nt 不知道为什么, str_to_date这个现在可以用了. 结合你的方法, 问题已经解决了. 非常感谢. 只是你答案中有些地方不太懂, 比如为何"4 * 3600是吧20:00以后的当成第二天"? 还有为何"div 86400"?

3600是1小时， + 4 * 3600，就是当前时间向后推4个小时，如果现在大于20:00的，向后推4个小时就成了第二天的时间戳，否则就是今天，这就解决了“20:00到19:59为一天”需求 div 86400，就是整除天的秒数，只算出天（日期），不管几点几分。这样你才可以按天group by，只要是同一天的都sum。 当然，你也可以用日期计算函数 DATE_FORMAT(DATE_ADD(str_to_date(`time`, '%Y%m%d%H%i%s'),INTERVAL 4 HOUR), '%Y-%m-%d') 来推算。

[Tennessee3Waltz]

非常感谢!!! 结贴了.