SQL Cookbook 系列 - 日期操作

1. 确定一年是否是闰年
2. 确定一年内的天数
3. 从日期中提取时间的各部分
4. 确定某个月的第一天和最后一天
5. 确定一年内属于周内某天的所有日期
6. 确定某月内第一个和最后一个周内某天的日期
7. 创建日历
8. 列出一年中每个季度的开始日期和结束日期
9. 确定某个给定季度的开始日期和结束日期
10. 填充丢失的日期
11. 按照给定的时间单位进行查找
12. 使用日期的特殊部分比较记录
13. 识别重叠的日期范围

 

1.确定一年是否是闰年

方案：检查2月最后一天是29，确定是否是闰年

db2: with x(dy,mth) as (

select dy, month(dy) from (

select (current_date - dayofyear(current_date) days +1 days)+1 months as dy from t1

) tmp1 union all

select dy+1 days,mth from x where month(dy+1 day)=mth

)

select max(day(dy)) from x;

oracle : select to_char(last_day(add_months(tranc(sysdate,'y'),1)),'DD') from t1;

postgresql: select max(to_char(tmp2.dy+x.id,'DD')) as dy from (

select dy,to_char(dy,'MM') as mth from (

select cast(cast(date_tranc('year',current_date) as date)+ interval '1 month' as date) as dy from t1

)tmp1

)tmp2,generate_series(0,29) x(id)

where to_char(tmp2.dy+x.id,'MM') = tmp2.mth;

mysql: select day(last_day(date_add(date_add(date_add(current_date,interval -dayofyear(current_date) day),interval 1 day),interval 1 month))) dy from t1;

sqlserver: with x (dy,mth) as (

select dy,month(dy) from (

select dateadd(mm,1,(getdate()-datepart(dy,getdate()))+1) dy from t1

) tmp1 union all

select dateadd(dd,1,dy),mth from x where month(dateadd(dd,1,dy))=mth

)

select max(day(dy)) from x;

Note:估计会在存储过程中用吧

 

2.确定一年内的天数

db2: select day((curr_year+1 year)) - days(curr_year) from (

select (current_date -dayofyear(current_date) day +1 day) curr_year from t1

) x;

oracle: select add_months(trunc(sysdate,'y'),12) -tranc(sysdate,'y') from dual;

posgresql: select cast((curr_year + interval '1 year') as date) - curr_year from (

select cast(date_trunc('year',current_date) as date) as curr_year from t1

) x;

sqlserver: select datediff(d,curr_year,dateadd(yy,1,curr_year)) from (

select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year from t1

) x;

mysql: select datediff((curr_year + interval 1 year),curr_year) from (

select adddate(current_date,-dayofyear(current_date)+1) curr_year from t1

) x;

Note:我不是太确定这个在数据库中会不会用到

 

3.从日期中提取时间的各部分

db2 ： select hour(current_timestamp) hr, minute(current_timestamp) min,

second(current_timestamp) sec, day(current_timestamp) dy,

month(current_timestamp) mth, year(current_timestamp) yr from t1;

oracle: select to_number(to_char(sysdate,'hh24')) hour,

to_number(to_char(sysdate,'mi')) min,

to_number(to_char(sysdate,'ss')) sec,

to_number(to_char(sysdate,'dd')) day,

to_number(to_char(sysdate,'mm')) mth,

to_number(to_char(sysdate,'yyyy')) year from dual;

postgresql: select to_number(to_char(current_timestamp,'hh24'),'99') as hr,

to_number(to_char(current_timestamp,'mi'),'99') as min,

to_number(to_char(current_timestamp,'ss'),'99') as sec,

to_number(to_char(current_timestamp,'dd'),'99') as day,

to_number(to_char(current_timestamp,'mm'),'99') as mth,

to_number(to_char(current_timestamp,'yyyy'),'99') as yr

from t1;

mysql: select date_format(current_timestamp,'%k') hr,

date_format(current_timestamp,'%l') min,

date_format(current_timestamp,'%s') sec,

date_format(current_timestamp,'%d') dy,

date_format(current_timestamp,‘%m’) mon,

date_format(current_timestamp,'%Y') yr from t1;

sqlserver: select datepart(hour,getdate()) hr, depart(minute,getdate()) min,

datepart(second,getdate()) sec,depart(day,getdate()) dy,

datepart(month,getdate()) mon,depart(year,getdate()) yr from t1;

Note：这个操作是在where条件之前执行，要想改进效率必须先执行where条件，再执行日期的分解操作，用处还可以

 

4.确定某个月的第一天和最后一天

db2: select (current_date-day(current_date) day +1 day) firstday,

(current_date + 1 month - day(current_date) day) lastday from t1;

oracle: select tranc(sysdate,'mm') firstday,last_day(sysdate) lastday from dual;

postgres