Mysql 中按日期总括数据

-- 按年产生连续的SELECT substring(CONVERT(NVARCHAR(10), DateAdd(YEAR, number, '2016-01-01'),120),1,4) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number = DateDiff(YEAR, '2016-01-01', '2019-01-01') -- 按月产生连续的SELECT substring(CONVERT(NVARCHAR(10), DateAdd(MONTH, number, '2019-01-01'),120),1,7) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number = DateDiff(MONTH, '2018-01-01', '2019-01-01') -- 按天产生连续的SELECT CONVERT(NVARCHAR(10), DateAdd(day, number, '2019-01-01'),120) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number = DateDiff(day, '2019-01-01', '2019-01-18') -- 按小时产生连续的SELECT substring(convert(char(32),DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),120),1,16) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),CONCAT('2019-01-18',' ', '23:00'))=0-- 按分钟的就自己可以YY了......
SELECT * FROM `order` WHERE FROM_UNIXTIME(`create_time`,"%Y%m%d%H%i")='201706151639'

您可能感兴趣的文章

  • php和mysql中日期和unix时间戳的互相转换
  • 如何查看MySQL启动时间以及运行了多长时间
  • mysql中优化sql语句查询的30种方法
  • php获取时间间隔的方法总结,php显示论坛发帖时间间隔方法大全
  • Navicat for mysql 远程连接 mySql数据库提示10061,1045错误的解决办法
  • php判断日期格式是否正确的方法总结
  • js适时输出当前的时间
  • php将数组保存为文本格式的方法总结

(1)MySql查询数据库当前时间可使用命令: select…

-- 按天统计交易笔数select a.GroupDay, ISNULL(b.e, 0) 'feeCount' from ( SELECT CONVERT(NVARCHAR(10), DateAdd(day, number, @paySdate),120) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number = DateDiff(day, @paySdate, @payEdate) ) a left join (select convert(char(32),create_time,23) as d, count(*) as e from trade_log where create_time = @paySdate and create_time=@payEdate group by convert(char(32),create_time,23)) b on b.d=a.GroupDay-- 按小时统计交易笔数select a.GroupDay, ISNULL(b.e,0) 'feeCount' from ( SELECT substring(convert(char(32),DATEADD(HH,number,CONCAT(@paySdate,' ', @paySTime)),120),1,16) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT(@paySdate,' ', @paySTime)),CONCAT(@payEdate,' ', @payETime))=0 ) a left join ( select convert(char(32),create_time,23) as d, datepart(hh,create_time) as h, substring(convert(char(32),DATEADD(HH,datepart(hh,create_time),convert(char(32),create_time,23)),120),1,16) as st, count(*) as e from trade_log where create_time = @paySdate and create_time=@payEdate and convert(char(8),create_time,108)=@paySTime and convert(char(8),create_time,108)=@payETime group by convert(char(32),create_time,23),datepart(hh,create_time)) b on b.st=a.GroupDay order by GroupDay

完整的 sql 语句例如:

select now();//返回格式为:2012-06-23 11:44:42

ps:MySQL按天,按周,按月,按时间段统计

select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

以上查询命令将显示如下结果:

总结

DATE_FORMAT(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如何查询数据库当前时间。

针对sqlserver, 有几点需要给大家说清楚:

倘若仅仅是想要根据int 类型的时间戳来转化成时间,可以如下操作:

通过一个具体的用法证明结果:

掌握的知识点:

select DATE_FORMAT(FROM_UNIXTIME(create_time,"%Y%m%d"),"%Y%u")  weeks,count(caseid) count from tc_case group by weeks;

FROM_UNIXTIME(unix_timestamp,format)
返回表示 Unix
时间标记的一个字符串,根据format字符串格式化。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), 这里星期一是星期的第一天
%% 一个文字“%”。

使用示例

DATE_FORMAT(FROM_UNIXTIME(create_time,"%Y%m%d"),"%Y%u")

时间字段存放在数据库中一般的存储类型是int类型,当我们要看看当前数据的具体时间的时候该怎么办呢?视觉上我们只能看到一堆堆的数字,如果需要转化为人类可以理解的日期格式,除了使用程序处理外,MySQL本身也提供了一个将unix时间戳转为日期时间字符串类型的函数,比如存储的时间为1340422884,需要转化为“年-月-日
时:分:秒”这种的形式。

四,与业务场景进行结合

如果在数据库中,时间保存的是 int
类型的时间戳,那么需要将字符串转化为日期类型
from_unixtime()是MySQL里的时间函数,能将mysql的时间戳类型转换成日期格式
因此,如果数据库中时间报错的事 int
类型的时间戳,那么可以直接用下面的方式进行查询

所有的其他字符不做解释被复制到结果中。

二,master..spt_values是什么东西?能用来做什么呢?

SELECT FROM_UNIXTIME(1234567890,'%Y-%m-%d %H:%i:%S');//当然这个日期格式可以根据上面提供的参数自行指定的

一,写在前面的话

另外需要mysql查询今天,昨天,近7天,近30天,本月,上一月的数据,请参照:mysql查询今天,昨天,近7天,近30天,本月,上一月数据的方法