oracle SQL 星期算法

偏执的太偏执、 2022-08-24 04:24 255阅读 0赞

美国人把周日当成一周的开始周六当成周结束，而我们常常需要将周一当成一周的开始，周日当成一周结束。在SQL操作中可能会遇到这类计算，例如，统计上周一到上周日的订单数量。  
计算方法如下：  
  
  
方法：  
充分利用trunc函数和next\_day函数：  
  
next\_day(date,'day') ：给出日期date和星期x（周日 x=1 周一 x=2 周二 x=3 ...周六=7）之后计算下一 **个星期**的日期。  
  
trunc 按照指定的精度进行舍入，注意这个函数是直接截断，和round函数有区别  
  
例子：  
select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)

\----------- ------------ ----------- ------------

56 -55 55 -55

计算方法：  
  
本周：  
  
周一：trunc(next\_day(sysdate - 8, 1) + 1)  
周二：trunc(next\_day(sysdate - 8, 1) + 2)  
周三：trunc(next\_day(sysdate - 8, 1) + 3)  
周四：trunc(next\_day(sysdate - 8, 1) + 4)  
周五：trunc(next\_day(sysdate - 8, 1) + 5)  
周六：trunc(next\_day(sysdate - 8, 1) + 6)  
周日：trunc(next\_day(sysdate - 8, 1) + 7)  
  
可以看出，要计算某周周一的起始日期，那么就直接使用周一到周日中任何一天的日期date替换这里的sysdate即可计算出日期所在周一和周日。  
  
  
应用 ：  
统计上周成功订单总数：

1.  select count(\*) from orders o where o.gmt\_create>=trunc(next\_day(sysdate - 8, 1)-6)
2.  and o.gmt\_create<trunc(next\_day(sysdate - 8, 1)+1)
3.  and o.status=5

补充：JAVA中得到本周一，本周日的方法如下：

1.  Calendar c = Calendar.getInstance();
2.  c.setFirstDayOfWeek(Calendar.MONDAY);// 指定周一为一周第一天
3.  c.setTime(new Date());
4.  c.set(Calendar.DAY\_OF\_WEEK, Calendar.MONDAY);// 本周周一的时间
5.  c.set(Calendar.HOUR\_OF\_DAY, 0);
6.  c.set(Calendar.MINUTE, 0);
7.  c.set(Calendar.SECOND, 0);
8.  Date monday = c.getTime();// 当前周一的日期

very easy.关键就是要设置 c.setFirstDayOfWeek(Calendar.MONDAY);  
  
  
bugfix: 之前求周一是trunc(next\_day(sysdate - 7, 1) + 1) 这个方法无法避免周日这一天计算错误(周日临界问题)。因此修改为  
trunc(next\_day(sysdate - 8, 1) + 1) ，这样就可以按照中国人的习惯 周一~周日 都算 本周。