SQL星期数问题

MySQL技术内幕 SQL编程 48页

其中讨论到一些时间日期的问题,非常有意思
创建2015年1月的日期数据.
create table test
select str_to_date('2014-12-27','%Y-%m-%d')+interval id day as "dt"
from nums where id<=42;


1.计算日期是星期几


weekday返回值0-6
0表示星期一
1表示星期二
2表示星期三
3表示星期四
4表示星期五
5表示星期六
6表示星期日
我们常用的方式应该是weekday + 1

dayofweek返回值1-7
1表示星期日
2表示星期一
...
7表示星期六
dayofweek是西方人的习惯,他们认为每周的开始是星期日,而我们一般都认为是星期一.



dayname可以根据lc_time_names的设置,返回星期的名称.


获取指定的日期是星期几,除了上面的方式.
还可以用指定日期推导的方式.
比如已经知道2014年12月29日是星期一,（1900年1月1日也是星期一）
那么采用如下的sql,可以知道2015年1月6日是星期二,1月7日是星期三.

set @a='2015-1-6';
select datediff(@a,'2014-12-29')%7+1 as dayofweek;
set @a='2015-1-7';
select datediff(@a,'2014-12-29')%7+1 as dayofweek;

2.按周分组
之前创建的实验数据.
细心可以发现,week将2014年12月29日至31日作为2014年的最后一周,
而将2015年1月1日-1月3日作为2015年的第一周.
如果有按周统计的数据,则跨年的周有可能统计为2周,导致数据的误差.
select dt,week(dt) from test;


对于周的划分,Oracle和MySQL也是截然不同,
create table test
as
select to_date('2014-12-27','yyyy-mm-dd') + rownum as dt from dual connect by level<=42;

select to_char(dt,'yyyy-mm-dd') dt,to_char(dt,'iw') iw,to_char(dt,'ww') ww from test;

其中 to_char IW和WW的算法如下

1）ww的算法为每年1月1日为第一周开始，date+6为每一周结尾

　　例如20050101为第一周的第一天，而第一周的最后一天为20050101+6=20050107

　　公式 每周第一天 ：date + 周 * 7 - 7

　　每周最后一天：date + 周 * 7 - 1

2）iw的算法为星期一至星期日算一周,(洋人的习惯,参考日历)
     他的计算方式是ISO的一个标准.
     每天有52周或者53周
    他跨年周的算法比较麻烦.
    以每天1月1日所在的周为准,若上年的日子多,则为上年的最后一周,若本年的日子多则为本年的第一周   

下面是2015年1月的数据,
2015年1月1日所在的周由
2014年12月29日-2015年1月4日组成
可以看到2014年12.29-12.31有3天
而2015年1月到4日有4天,所以本周划分为2015年的第一周


若是2012年1月1日,跨年的周由2011.12.26-2012.1.1
2011年有6天,而2012年只有1天,所以这周被划分在了2011年的最后一周,
而2012年的第一周从2012年1月2日开始.


可以看到不同的数据库针对周划分的算法都不一致,
如果有跨年按周统计的需求,也可以使用根据指定日期推导的方式.
比如1900年1月1日是星期一
我们可以计算从这天到今天周数,这是连续的,可以作为跨年按周统计的依据

select
dt,
floor(datediff(dt,'1900-01-01')/7) as week,
date_add('1900-01-01',interval floor(datediff(dt,'1900-01-01')/7)*7 day) as week_start,
date_add('1900-01-01',interval floor(datediff(dt,'1900-01-01')/7)*7+6 day) as week_end
from test;


3.计算工作日的问题

Oracle处理工作日问题参考
http://blog.itpub.net/29254281/viewspace-775663/

MySQL首先可以用数字辅助表的方式
数字辅助表参考:
http://blog.itpub.net/29254281/viewspace-1362897/

drop procedure pGetWorkDaysByNums;
delimiter $$
create procedure pGetWorkDaysByNums(s datetime,e datetime)
begin
select count(*)
as workdays
from
(
    select s+interval id-1 day dt from nums where id<=datediff(e,s)+1
) as a
where weekday(dt) not in (5,6);
end$$
delimiter ;

call pGetWorkDaysByNums('2015-01-01','2015-01-30');


还有一种方式,有更高的效率
drop procedure pGetWorkDays;
delimiter $$
create procedure pGetWorkDays(s datetime,e datetime)
begin
select
floor(days/7)*5
+days%7
-case when 6 between wd and wd+days%7-1 then 1 else 0 end
-case when 7 between wd and wd+days%7-1 then 1 else 0 end
as workdays
from
(
    select datediff(e,s)+1 as days,weekday(s)+1 as wd
) as a;
end$$
delimiter ;

call pGetWorkDays('2015-01-01','2015-01-30');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1387930/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1387930/