mysql 实现时统计 hql

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>   select   DATE_FORMAT('1997-10-04   22:23:00',   '%W   %M   %Y'); 
                  ->   'Saturday   October   1997' 
mysql>   select   DATE_FORMAT('1997-10-04   22:23:00',   '%H:%i:%s'); 
                  ->   '22:23:00' 
mysql>   select   DATE_FORMAT('1997-10-04   22:23:00', 
                                                      '%D   %y   %a   %d   %m   %b   %j'); 
                  ->   '4th   97   Sat   04   10   Oct   277' 
mysql>   select   DATE_FORMAT('1997-10-04   22:23:00', 
                                                      '%H   %k   %I   %r   %T   %S   %w'); 
                  ->   '22   22   10   10:23:00   PM   22:23:00   00   6' 


, '%Y %m'

Sql代码 
FROM CUSTOMERS as customers 
FROM CUSTOMERS as customers节 10.02 Where子句
(a) 比较表达式Sql代码 
FROM CUSTOMERS as customers WHERE customers.id=1 
FROM CUSTOMERS as customers WHERE customers.amount between 1 and 10 
FROM CUSTOMERS as customers WHERE customers.amount >100 
FROM CUSTOMERS as customers WHERE customers.email in ('foo@bar','bar@foo') 
FROM CUSTOMERS as customers WHERE customers.email IS NULL 
FROM CUSTOMERS as customers WHERE customers.email IS NOT NULL 
FROM CUSTOMERS as customers WHERE customers.email LIKE '@%' 
FROM CUSTOMERS as customers WHERE customers.id=1
FROM CUSTOMERS as customers WHERE customers.amount between 1 and 10
FROM CUSTOMERS as customers WHERE customers.amount >100
FROM CUSTOMERS as customers WHERE customers.email in ('foo@bar','bar@foo')
FROM CUSTOMERS as customers WHERE customers.email IS NULL
FROM CUSTOMERS as customers WHERE customers.email IS NOT NULL
FROM CUSTOMERS as customers WHERE customers.email LIKE '@%'HQL 操作符 

HQL 常用操作符 
描述 

.
导航路径表达式操作符 

+,-
一元正负号 

*,/
乘除法 

+,-
加减法 

=,<>,<,>,>=,<=,[NOT]BETWEEN[NOT] LIKE,[NOT] IN,IS [NOT] NULL,IS [NOT] EMPTY
二元比较操作符 



NOT,AND,OR
逻辑操作符 

HQL操作符
(b) HQL常用函数
Sql代码
FROM User u where lower(u.email) = '[e-mail]' --小写 
FROM User u where lower(u.email) = '[e-mail]' --大写 
FROM User u where concat(u.firstname,u.lastname) like 'G% K%' --拼接字符串 
FROM User u where size(u.bids) > 3 --集合大小 
FROM User u where lower(u.email) = '[e-mail]' --小写
FROM User u where lower(u.email) = '[e-mail]' --大写
FROM User u where concat(u.firstname,u.lastname) like 'G% K%' --拼接字符串
FROM User u where size(u.bids) > 3 --集合大小HQL 函数 

HQL 常用操作符 
描述 

UPPER(s),LOWER(s)
大小写 

CONCAT(s1,s2)
连接字符串 

SUBSTRING(s,offset,length)
取子串 

TRIM()
去空格 

LENGTH(s)
长度 

LOCATE(search,s,offset)
搜索位置 

ABS(n),SQRT(n),MOD(dividend,divisor)
绝对值，开方，除法 

SIZE
大小写 

BIT_LENGTH(s)
位数 

CUTTENT_DATE()
当前时间 

CURRENT_TIME()

CURRENT_TIMESTAMP()

SECOND(d)
获取时间的不同部分 

MINUTE(d)

HOUR(d)

DAY(d)

MONTH(d)

YEAR(d)

CAST(t as TYPE)
类型转换 

HQL函数
节 10.03 Order表达式
Sql代码
FROM CUSTOMERS as customers WHERE customers.id=1 
ORDER BY customers.id FROM CUSTOMERS as customers 
ORDER BY customers.lastname asc,firstname asc 
FROM CUSTOMERS as customers WHERE customers.id=1 
ORDER BY customers.id FROM CUSTOMERS as customers 
ORDER BY customers.lastname asc,firstname asc
节 10.04 投影查询
Sql代码 
SELECT user.user

name,user.password FROM UserInfo user 
SELECT user.username,user.password FROM UserInfo user
注：1、在使用Select子句查询时，要给表对象起一个别名。
2、查询出的结果集是一个Object[]数组。

实现代码：

select count(*),p.createDate,DATE_FORMAT(p.createDate, '%Y-%m-%d %H :00:00') from user p group by DATE_FORMAT(p.createDate, '%W   %M   %Y %H') order by date(p.createDate) asc

运行结果：