sql 整型数字怎么样转换成时间格式

码拜

9年 ago

本人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语句改怎么写? 在线等高手, 提前感谢!

解决方案

引用

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”

先用

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;

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