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

Tennessee3Waltz 2015-09-04 11:56:40
我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语句改怎么写? 在线等大神, 提前感谢!
...全文
3379 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tennessee3Waltz 2015-09-14
  • 打赏
  • 举报
回复
非常感谢!!! 结贴了.
代码誊写工 2015-09-11
  • 打赏
  • 举报
回复
引用 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 2015-09-11
  • 打赏
  • 举报
回复
version结果是5.0.87-community-nt 不知道为什么, str_to_date这个现在可以用了. 结合你的方法, 问题已经解决了. 非常感谢. 只是你答案中有些地方不太懂, 比如为何"4 * 3600是吧20:00以后的当成第二天"? 还有为何"div 86400"?
代码誊写工 2015-09-06
  • 打赏
  • 举报
回复
先用
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 2015-09-06
  • 打赏
  • 举报
回复
二楼和三楼, 谢谢你们的回答. 我试了你们的语句, 结果都是语法报错, 似乎mysql不认识这些语句. 即便真的能用, 能否解答这个问题: 每天的product_num的总和(天是按照20:00-第二天的19:59为一天,而不是常用的00:00-23:59). 我实在想不出来怎么解决.
ACMAIN_CHM 2015-09-04
  • 打赏
  • 举报
回复
引用
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'
.
代码誊写工 2015-09-04
  • 打赏
  • 举报
回复
字符串或类似字符串的整数转换为日期时间 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')
收集整理的SQL Server自定义函数,原文地址:http://blog.csdn.net/maco_wang 1.去除字符串中的html标记及标记中的内容 2 2. 去除字符串中连续的分割符 4 3.求第一个字符串中第二个串的个数 6 4.综合模糊查询 6 5.将十进制转成十六进制 8 6.求两个字符串中相同的汉字及字母的个数 10 7.生成n位随机字符串 11 8.取出字符串中的汉字、字母或是数字 14 9.根据字符分割字符串的三种写法 16 10.将数字转换千分位分隔形式 18 11.取汉字首字母的两个函数 20 12.根据身份证得到生日函数 23 13.根据身份证计算性别函数 24 14.将身份证的15位号码升级为18位 25 15.通过身份证获得户籍 27 16.多个数据项的字符串取指定位置字符 28 17.中缀算术转后缀算术表达式并计算的函数 29 18.人民币小写金额转大写 32 19.向左填充指定字符串 36 20.将整型数字转换为大写汉字 39 21.检查给定串是否存在于由区间及点集的结合内 39 22.根据日期返回星座 41 23.计算两个日期之间的工作日 43 24.根据年月生成日历函数 44 25.从第一个汉字开始分割字符串 47 26.过滤掉字符串中重复的字符 47 27.根据日期得到星期的函数 48 28.根据年度判断是否是闰年 49 29.完善SQL农历转换函数 50 30.自定义函数实现位操作 58 31.求某段时间内星期几的天数 61 32.根据进舍位或四舍五入来求值 63 33.字符串转成16进制函数 64 34.去掉字段中多个带区号电话号码前面的区号 65 35.SQL2000/2005字符串拆分为列表通用函数 67 36.求字符串中汉字的个数 69 37.根据年月得到当月的天数 71 38.全角半角转换函数 71
《数据库系统概论》 实验报告 班级学号_____ ___ 姓 名_____ ______ 指导老师______ _ __ 目 录 实验一 数据库服务器的连接及数据库的建立 1 实验二 简单SQL查询及数据库多表查询 3 实验三 视图、索引、存储过程和触发器的使用 9 实验四 E-R模型与关系模型的转换 13 实验一 数据库服务器的连接及数据库的建立 1.1实验名称:数据库服务器的连接及数据库的建立 1.2实验类型:验证型 1.3实验学时:3h 1.4实验目的: 了解连接数据库服务器的身份验证模式,熟悉样例数据库。掌握DBMS中利用界面进行 建库建表操作。 1.5实验准备: 1.5.1 数据模型 1.5.2 数据库系统的三级模式结构 1.5.3 DBMS的功能 1.6实验内容 1.6.1熟悉网络环境和数据库环境 1.向输入表中的记录:(每个表最少5条记录,其中一条是与自己相关的真实记录) 1.7思考题: 1. 从用户角度看,数据库系统都有哪些体系结构? 2. 数据库管理系统通常由哪几部分组成? 3. 如何用SQL语句完成上述的工作? 1.8 实验心得(不少于50字) 通过本次实验我学会了数据库的连接,开始我以windows身份登录,但是登录不进去,后来 一SQL service身份登录后成功建立了数据库,然后再建立了要求的表,我发现建立表一定要注意 某些表的要求,尤其是数据类型,是文本型还是数字或者是字符型 实验二 简单SQL查询及数据库多表查询 2.1实验名称:简单SQL查询及数据库多表查询 2.2实验类型:验证型 2.3实验学时:3h 2.4实验目的: 通过本章的学习,用户将能够: 1.掌握向表中添加数据的方法 2.掌握如何快速的把一个表中的多行数据插入到另一个表中 3.掌握更新表中多行数据的方法。 4.学会如何删除表中的一行数据 5.学会如何删除表中的所有行 6.掌握SELECT语句的基本用法 7.使用WHERE子句进行有条件的查询 8.掌握使用IN, NOT IN,BETWEEN来缩小查询范围的方法 9.利用LIKE 子句实现模糊查询 ,利用ORDER子句为结果排序 10.学会用SQL SERVER 的统计函数进行统计计算 11.掌握用GROUP BY 子句实现分组查询的方法。 12.掌握多表查询的技术和嵌套查询的方法。 2.5实验准备: 1、 常用的统计函数: sum() 返回一个数字或计算列的总和 avg() 对一个数字列或计算求列平均值 min() 返回一个数字或一个数字表达式的最小值 max() 返回一个数字或一个数字表达式的最大值 count() 返回满足SELECT语句中指定条件的记录的值 count(*) 返回找到的行数 注意:对于以下类型的数据values值不用使用引号 1、整型数据类型:bit,int,smallint,tinyint 2、货币数据类型:money,smallmoney 3、数字数据类型:decimal,numeric 4、浮点类型数据:real,float 5、简单的日期/时间类型数据:datetime,smalldatetime 对于以下数据类型就要对values值加单引号 1、字符数据类型:char,varchar,text 2、复杂的日期/时间类型数据:datetime,smalldatetime 2、常用SQL语句语法 添加语句: INSERT [INTO] table_or_view [(column_list)] data_values 删除语句: DELETE [ FROM ] table_name WHERE search_condition 更新语句: UPDATE table_name SET column_name = { expression " DEFAULT " NULL }[ ,...n ] [ FROM table_name [ ,...n ] ] WHERE searchcondition 查询语句: SELECT select_list FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC " DESC ] ] 2.6实验内容: 2.6.1 操作一:INSERT(插入) 1、首先检查表结构:(截图,一个就好) 图2-1 输入检查表结构的语句 1. 单条语句插入样例:(截图,向自己的表插入一个记录,前后对比。写出语句) 图2-2 各表插入语句样例 2.6.2 操作二:UPDATE(更新) 4、执行以下更新 在class表中,no_

56,675

社区成员

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

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