比如按照五分钟的时间:
to_date(to_char(t.datatime, 'yyyy-mm-dd hh24')||':'||lpad(floor(to_char(t.datatime, 'mi')/5)*5,2,0) || ':00','yyyy-mm-dd hh24:mi:ss')
三分钟
to_date(to_char(t.datatime, 'yyyy-mm-dd hh24')||':'||lpad(floor(to_char(t.datatime, 'mi')/3)*3,2,0) || ':00','yyyy-mm-dd hh24:mi:ss')
按照一分钟:select sysdate,trunc(sysdate,'MI') from dual;
按照小时:select sysdate,trunc(sysdate,'HH') from dual;
mysql 按照分钟分组:
SELECT NOW(),FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW())/5/60)*60*5);
sqlserver:
按照分钟:select cast(floor(cast(getdate() as float)*24*60/5)*5/60/24 as smalldatetime)
按照小时:select cast(substring(CONVERT(varchar(100),GETDATE(),25),1,13)+':00:00' as datetime)
按照天:select cast(CONVERT(varchar(100),GETDATE(),23) as datetime)
另外一种:
select DATEADD(hh,datediff(hh,0,getdate()),0) -- 时分组
select DATEADD(dd,datediff(dd,0,getdate()),0) -- 天分组
select DATEADD(mm,datediff(mm,0,getdate()),0) -- 月分组