mysql日期格式与字符串格式的转化
来源:互联网 发布:t-sql教程pdf 编辑:程序博客网 时间:2024/05/14 00:13
直接贴代码吧,例子在代码里,本例经过测试。
select * from (select str_to_date(concat(substr(ctiWorkEventTbl.hourValue, 1, 4), '-',substr(ctiWorkEventTbl.hourValue, 5, 2),'-',substr(ctiWorkEventTbl.hourValue, 7, 2)), '%Y-%m-%d') strdate , concat(substr(ctiWorkEventTbl.hourValue, 1, 4), '-',substr(ctiWorkEventTbl.hourValue, 5, 2),'-',substr(ctiWorkEventTbl.hourValue, 7, 2)),ctiWorkEventTbl.hourValue,ctiWorkEventTbl.agentCnt,ctiCallstaTbl.totalCallCnt/ctiWorkEventTbl.agentCnt as perAgtCallCnt /*坐席平均呼叫次数*/,ctiCallstaTbl.totalCallCnt ,ctiCallstaTbl.totalOkCallCnt,ctiCallstaTbl.totalOkCallCnt/ctiWorkEventTbl.agentCnt as perAgtOkCallCnt /*平均通话次数*/,ctiCallstaTbl.totalBadCallCnt,ctiCallstaTbl.badToAgtCnt,ctiCallstaTbl.userAbandonCnt,ctiCallstaTbl.callDuration,ctiCallstaTbl.perCallDuration,ctiCallstaTbl.ringCnt,ctiCallstaTbl.ringDuration,ctiCallstaTbl.perRingDuration,ctiWorkEventTbl.refer2OtherCnt,ctiWorkEventTbl.transfer2OtherCnt,ctiCallstaTbl.agentDisconnectCnt,ctiCallstaTbl.userDisconnectCnt,ctiCallstaTbl.otherGrpCallInCnt,ctiCallstaTbl.perOtherGrpCallInCnt,ctiCallstaTbl.otherGrpCallInDuration,ctiCallstaTbl.maxCallDurationfrom(select substr(starttime, 1, 8) as hourValue , concat(substr(starttime, 1, 4), '-',substr(starttime, 5, 2),'-',substr(starttime, 7, 2)) starttimestr,sum(if(calltype = 3 && direction = 1, 1, 0)) as totalCallCnt /*总呼叫次数*/,sum(if(calltype = 3 && direction = 1 && status = 1, 1, 0)) as totalOkCallCnt/*通话次数*/,sum(if(calltype = 3 && direction = 1, 1, 0)) as totalCallInCnt/*呼入次数*/,sum(if(calltype = 3 && direction = 1 && status = 2 , 1, 0)) as totalBadCallCnt/*总失败次数*/,sum(if(calltype = 3 && detail = 0007 , 1, 0)) as badToAgtCnt/*坐席未接起*/,sum(if(calltype = 3 && (detail = 0008 || detail = 0106 || detail = 0107), 1, 0)) as userAbandonCnt /*用户放弃*/,sum(if(calltype = 3 && detail = 0009 , 1, 0)) as agentNoAnswerCnt/*坐席无应答*/,sum(if(calltype = 3 && direction = 1 && status = 1, duration, 0)) as callDuration/*通话时长*/,sum(if(calltype = 3 && direction = 1 && status = 1, duration, 0))/sum(if(calltype = 3 && direction = 1 && status = 1, 1, 0)) as perCallDuration/*平均通话时长*/,sum(if(calltype = 3 && (direction = 1 || direction = 2 ) && ringseconds > 0, 1, 0)) as ringCnt/*振铃次数*/,sum(if(calltype = 3 && (direction = 1 || direction = 2 ) && ringseconds > 0, ringseconds, 0)) as ringDuration/*振铃时长*/,sum(if(calltype = 3 && (direction = 1 || direction = 2 ) && ringseconds > 0, ringseconds, 0))/sum(if(calltype = 3 && (direction = 1 || direction = 2 ) && ringseconds > 0, 1, 0)) as perRingDuration/*平均振铃时长*/,sum(if((calltype = 3 && direction = 1 && disconnection = 2) || (calltype = 3 && direction = 2 && disconnection = 1) , 1, 0)) as agentDisconnectCnt/*坐席挂机*/,sum(if((calltype = 3 && direction = 1 && disconnection = 1) || (calltype = 3 && direction = 2 && disconnection = 2) , 1, 0)) as userDisconnectCnt/*用户挂机*/,sum(if(calltype = 3 && direction = 1 && substr(taskid, 9, 6) != substr(department, 15, 6), 1, 0)) as otherGrpCallInCnt /*非本组代答*/,sum(if(calltype = 3 && direction = 1 && substr(taskid, 9, 6) != substr(department, 15, 6), 1, 0)) / sum(if(calltype = 3 && direction = 1, 1, 0)) as perOtherGrpCallInCnt /*非本组代答率*/,sum(if(calltype = 3 && direction = 1 && substr(taskid, 9, 6) != substr(department, 15, 6), duration, 0)) as otherGrpCallInDuration /*非本组代答时长*/,max(if(calltype = 3 && direction = 1 && status = 1, duration, 0)) as maxCallDuration/*最长通话时长*/from cti_callstatgroup by substr(starttime, 1, 8)) ctiCallstaTblleft join ( select count(distinct(agentid))as agentCnt ,substr(etime, 1, 8) as hourValue ,sum(if(event = 5, 1, 0)) as refer2OtherCnt/*咨询次数*/,sum(if(event = 6 || event = 7 , 1, 0)) as transfer2OtherCnt/*转移次数*/from cti_workeventgroup by substr(etime, 1, 8)) ctiWorkEventTbl on ctiCallstaTbl.hourValue = ctiWorkEventTbl.hourValue ) d where d.strdate > date_format('2012-05-03','%y-%m-%d')
总结:
字符串转日期:
str_to_date(concat(substr(ctiWorkEventTbl.hourValue, 1, 4), '-',substr(ctiWorkEventTbl.hourValue, 5, 2),'-',substr(ctiWorkEventTbl.hourValue, 7, 2)), '%Y-%m-%d') strdate
简化就是 str_to_date(‘2012-03-06’,'%Y-%m-%d') strdate
日期格式的格式化:
date_format('2012-05-03','%y-%m-%d')
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DATE_FORMAT(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中: %M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。
- mysql日期格式与字符串格式的转化
- MySQL 日期格式的转化
- 简单的字符串日期格式转化
- 日期格式的转化
- 日期格式的转化
- 日期格式的转化
- 字符串日期转化为java日期格式
- 按照要求的输入输出格式进行日期字符串的转化
- SQL日期格式的转化
- jsp sql server 2000的时间日期格式与字符串相转化
- Java将字符串转化为时间格式,与实现日期的计算
- java如何实现日期格式和字符串之间的转化
- Js Date日期格式和字符串的相互转化
- R语言与格式、日期格式、格式转化
- Java日期格式与字符串的转换
- 将日期格式转化成字符串
- Java日期格式--字符串与Date格式的转换
- Java中字符串格式与日期格式的转换
- ADB server didn't ACK 错误解决方法
- table control的修改/排序/删除功能实现实例
- Android ListView Animation 4种动画效果(贴上了GIF图)
- 处理asp.net出现A potentially dangerous Request.Form value was detected from the client
- 恢复Outlook 2010/2007已被删除邮件的方法
- mysql日期格式与字符串格式的转化
- epoll_create, epoll_ctl和epoll_wait
- hanganalyze和systemstate dump
- 怎么做出具有凹陷效果的字体
- Android-StageFright之数据流的封装和AwesomePlayer流程
- linux下QT安装
- 由LCA引发的问题--RMQ,Tarjan,并查集等
- JSTL(fn函数)
- selector修改字体颜色