取小时的时间段的sql怎么写呢?

dglqcpic

select count(*) from table
where hour(时间) between '22:00:00' and '06:00:00'
group by 1

我要取某张表发生在晚上10到早上6点的记录数,能这样写吗?

liaosnet

这个是跨天操作了~~
写法取决于的你的数据库设计~~

liaosnet

如果你的hour只有小时跟分钟的话，可能还需要加上日期。。..

大梦

这样写不行吧!
为什么不直接搞
(between '22:00:00' and '23:59:59')
or
(between '00:00:00' and '06:00:00')

liaosnet

哈哈~~错误了吧~~那时间不是当天的22-24及第二天的0-6了嘛..

dglqcpic

我的时间格式是:
2004-12-13 03:10:17

dglqcpic

select count(*)  from policy
where hour(start_da) between '23:00:00' and '24:00:00'
group by 1

系统提示这个错误
674: Routine (hour) can not be resolved.

这个hour 不能群组

dglqcpic

select count(*)  from policy
where hour(start_da) between '01' and '10'
group by 1

这样也不行,同上报错

liaosnet

原帖由 dglqcpic 于 2008-6-6 10:55 发表
select count(*)  from policy
where hour(start_da) between '23:00:00' and '24:00:00'
group by 1

系统提示这个错误
674: Routine (hour) can not be resolved.

这个hour 不能群组

我记得没有hour这个routine
还是老老实实在写全吧..2004-12-13 03:10:17  
select count(*) from policy
where start_da bewteen to_date((to_char(current - 1 units day),"%Y-%m%-%d")||" 22:00","%Y-%m-%d %H:%M") and to_date((to_char(current,"%Y-%m%-%d")||" 06:00","%Y-%m-%d %H:%M")
group by 1;

修正下点小错误..:mrgreen:

[ 本帖最后由 liaosnet 于 2008-6-6 12:23 编辑 ]

dglqcpic

谢谢,我试试