SQL经典案例之不同数据库的日期函数使用细节差异

SQL经典案例之不同数据库日期函数使用细节差异

• 事务里的时间戳
• 年份的天数
• trunc
• 闰年的判断
• LAST_DAY
• GENERATE_SERIES
• 提取日期的年月日时分秒
• 月份的第一天和最后一天
• 找出一年中所有的星期 X
• 一个月中第一个和最后一个星期 X
• NEXT_DAY(date,char)
• dayofweek
• 列出一年各个季度的第一天和最后一天

事务里的时间戳

• PostgreSQL在事务里要获取实时的时间戳可以用 statement_timestamp() 函数
• MySQL/Oracle在事务里获取的时间戳是实时的

-- postgresql 
begin;
select now(),current_timestamp,statement_timestamp();

-- MySQL获取的是实时的时间戳（会变的）
begin;
select now(),current_timestamp,sysdate();

-- oracle
select sysdate,current_timestamp from dual;
复制

• MySQL的 now() 是在执行开始时值就得到的，而 sysdate() 在函数执行时动态得到的值
• PostgreSQL 在该方式中获取的都是一致的

-- mysql
select now(), sleep(3), now(), sleep(3), now();
select current_timestamp, sleep(3), current_timestamp;
select sysdate(), sleep(3), sysdate(), sleep(3), sysdate();

-- postgresql
select now(), pg_sleep(3), now();
select current_timestamp, pg_sleep(3), current_timestamp;
select statement_timestamp(), pg_sleep(3), statement_timestamp();
复制

年份的天数

计算年份有多少天：下一年第一天与这一年第一天之间相差的天数

-- oracle 
select sysdate,
	trunc(sysdate,'y') as curr_year,
	add_months(trunc(sysdate,'y'),12) as next_year,
	add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') as yedays
from dual;
-- mysql
select current_date,
	dayofyear(current_date) as dayofyear,
	adddate(current_date,-dayofyear(current_date)+1) as curr_year,
	adddate(current_date,-dayofyear(current_date)+1) + interval 1 year as next_year,
	datediff((adddate(current_date,-dayofyear(current_date)+1) + interval 1 year),adddate(current_date,-dayofyear(current_date)+1)) as yedays
from dual;
-- postgresql
select 
	current_date,
	date_trunc('year',current_date) as firstmonth,
	cast(date_trunc('year',current_date) as date) as curr_year,
	cast(date_trunc('year',current_date) as date) + interval '1 year' as next_year,
	cast(date_trunc('year',current_date) as date) + interval '1 year' - cast(date_trunc('year',current_date) as date) as yedays;
复制

trunc

用于对值进行截断

• TRUNC（NUMBER）表示截断数字，默认保留正整数位
• TRUNC（date）表示截断日期，默认保留年月日

复制

闰年的判断

可用上面年份的天数来做判断，366为闰年。这里用另一种方式，检查2月份的最后一天，如果是2月29日，则年份为闰年

LAST_DAY

使用函数 LAST_DAY 找出2月份的最后一天

-- oracle 
select sysdate,
	trunc(sysdate,'y'),
	add_months(trunc(sysdate,'y'),1),
	last_day(add_months(trunc(sysdate,'y'),1)),
	to_char(last_day(add_months(trunc(sysdate,'y'),1)),'DD') 
from dual;
-- mysql
select current_date,
	dayofyear(current_date) as dayofyear,
	date_add(current_date,interval - dayofyear(current_date) day) as lastdayofyear,
	date_add(date_add(current_date,interval - dayofyear(current_date) day),interval 1 day) as newyearday,
	date_add(date_add(date_add(current_date,interval - dayofyear(current_date) day),interval 1 day),interval 1 month) as secmonth,
	last_day(date_add(date_add(date_add(current_date,interval - dayofyear(current_date) day),interval 1 day),interval 1 month)) as secmnlastday,
	day(last_day(date_add(date_add(date_add(current_date,interval - dayofyear(current_date) day),interval 1 day),interval 1 month))) as lastday
from dual;
复制

GENERATE_SERIES

使用函数 GENERATE_SERIES 返回2月份的每一天，然后使用聚合函数MAX找出2月份的最后一天

-- postgresql
select max(to_char(temp_2.ymr+x.id,'DD')) as dy from (
	select ymr,to_char(ymr,'MM') as mth from (
		select cast(cast(date_trunc('year',current_date) as date) + interval '1 month' as date) as ymr
	) temp_1
) temp_2,generate_series(0,29) x(id)
where to_char(temp_2.ymr+x.id,'MM') = temp_2.mth;

-- 拆解
select 
	current_date,
	date_trunc('year',current_date) as firstmonth,
	cast(date_trunc('year',current_date) as date) + interval '1 month' as scemonth,
	cast(cast(date_trunc('year',current_date) as date) + interval '1 month' as date) as ymr;
复制

提取日期的年月日时分秒

提取日期的年月日时分秒并转为数字返回

-- Oracle
select sysdate,
	to_number(to_char(sysdate,'yyyy')) year,
	to_number(to_char(sysdate,'mm')) mth,
	to_number(to_char(sysdate,'dd')) day,
	to_number(to_char(sysdate,'hh24')) hour,
	to_number(to_char(sysdate,'mi')) min,
	to_number(to_char(sysdate,'ss')) sec
from dual;

-- MySQL
select current_timestamp,
	date_format(current_timestamp,'%Y') year,
	date_format(current_timestamp,'%m') mth,
	date_format(current_timestamp,'%d') dy,
	date_format(current_timestamp,'%k') hr,
	date_format(current_timestamp,'%i') min,
	date_format(current_timestamp,'%s') sec
from dual;

-- PostgreSQL
select current_timestamp,
	to_number(to_char(current_timestamp,'yyyy'),'9999') as year,
	to_number(to_char(current_timestamp,'mm'),'99') as mth,
	to_number(to_char(current_timestamp,'dd'),'99') as day,
	to_number(to_char(current_timestamp,'hh24'),'99') as hour,
	to_number(to_char(current_timestamp,'mi'),'99') as min,
	to_number(to_char(current_timestamp,'ss'),'99') as sec;

-- db
select current_timestamp,
	extract(year from current_timestamp) as year,
	extract(month from current_timestamp) as mth,
	extract(day from current_timestamp) as day,
	extract(hour from current_timestamp) as hour,
	extract(minute from current_timestamp) as min,
	extract(second from current_timestamp) as sec;
复制

月份的第一天和最后一天

-- oracle：使用trunc会导致时间部分丢失
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate,trunc(sysdate,'mm') firstday,last_day(sysdate) lastday from dual;

-- mysql：当前日期减去函数DAY获取的当前月份天数得到上个月的最后一天，再加上1天则为当月第一天
select current_date,
	day(current_date) day,
	-day(current_date)+1 aopday,
	date_add(current_date,interval -day(current_date)+1 day) firstday,
	last_day(current_date) lastday
from dual;

-- postgresql：date_trunc将当前日期按月截断为所属月份第一天，最后一天则加上一个月后再减去1天
select current_date,
	date_trunc('month',current_date) as trunday,
	cast(date_trunc('month',current_date) as date) as firstday,
	cast(cast(date_trunc('month',current_date) as date) + interval '1 month' - interval '1 day' as date) as lastday;
复制

找出一年中所有的星期 X

思路：返回当前年份的每一天，然后只保留那些为星期 X 的日期

-- Oracle：递归式 CONNECT BY 子句返回当年的每一天，然后使用 TO_CHAR 将星期五对应的日期留下
with x as(
	select trunc(sysdate,'y')+level-1 dy from dual
	connect by level <= add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)select * from x where to_char(dy,'dy')='fri';

-- MySQL：使用递归式CTE生成年份的每一天，然后剔除不是星期五的日期
with recursive cal(dy,yr) as(
	select dy,extract(year from dy) as yr 
	from (select adddate(adddate(current_date,interval - dayofyear(current_date) day),interval 1 day) as dy) as tmp1
	union all
	select date_add(dy,interval 1 day),yr from cal where extract(year from date_add(dy,interval 1 day)) = yr
)select dy from cal where dayofweek(dy) = 6;

-- PostgreSQL
with recursive cal(dy) as(
	select current_date - (cast(extract(doy from current_date) as integer)-1)
	union all
	select dy+1 from cal where extract(year from dy) = extract(year from (dy+1))
)select dy,extract(dow from dy) from cal where cast(extract(dow from dy) as integer) = 5;
复制

一个月中第一个和最后一个星期 X

先获取当月第一天，再通过算术运算（星期日到星期六：1～7）

• Oracle首先返回上个月最后一天，再通过next_day函数获取当前月的第一个星期一
• MySQL和PostgreSQL：首先找出当前月的第一天，再找出该月的第一个星期一；通过CASE检查加上28天后是否会进入下个月来决定是加上21天还是28天来获取最后一个星期

-- Oracle：trunc获取当月第一天
select sysdate,
	trunc(sysdate,'mm'),
	next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
	next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
from dual;

-- MySQL：adddate获取当月第一天
select first_monday,
	case month(adddate(first_monday,28)) 
		when mth then adddate(first_monday,28)
				 else adddate(first_monday,21)
	end last_monday
from (
	select case sign(dayofweek(dy)-2)
				when 0 then dy
				when -1 then adddate(dy,abs(dayofweek(dy)-2))
				when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
			end first_monday,mth
	from (
		select adddate(adddate(current_date,-day(current_date)),1) dy,month(current_date) mth
	) x
) y;

-- PostgreSQL：date_trunc获取当月第一天
select first_monday,
	case to_char(first_monday+28,'mm') 
		when mth then first_monday+28
		else first_monday+21
	end as last_monday
from(
	select case sign(cast(to_char(dy,'d') as integer)-2)
		when 0 then dy
		when -1 then dy+abs(cast(to_char(dy,'d') as integer)-2)
		when 1 then (7-(cast(to_char(dy,'d') as integer)-2))+dy
		end as first_monday,mth
	from (
		select cast(date_trunc('month',current_date) as date) as dy,
			to_char(current_date,'mm') as mth
	) x
) y;
复制

NEXT_DAY(date,char)

指定时间的下一个星期几（由char指定）所在的日期
char：为1~7 或 Sunday～Monday/Mon

select sysdate,next_day(sysdate,1),next_day(sysdate,'Sunday') from dual;
复制

dayofweek

SELECT
	now(),
	dayofweek(now()) as weekdaynum,
    CASE dayofweek(now())
		WHEN 1 THEN '星期日'
		WHEN 2 THEN '星期一'
		WHEN 3 THEN '星期二'
		WHEN 4 THEN '星期三'
		WHEN 5 THEN '星期四'
		WHEN 6 THEN '星期五'
		WHEN 7 THEN '星期六'
	END as weekday
FROM DUAL;
复制

列出一年各个季度的第一天和最后一天

-- Oracle：ADD_MONTHS找出各季度第一天和最后一天，再使用rownum表示季度编号
select rownum qtr,
	add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
	add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
from dual;

select rownum qtr,
	add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
	add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
from (select * from dual
	union all
	select * from dual
	union all
	select * from dual
	union all
	select * from dual) t_union_dual 
where rownum<=4;
复制

-- MySQL：找出当前日期所属年份的第一天，使用ADDDATE找出每个季度的最后一天（前一个季度的最后一天加上3个月或后一个季度的第一天减去1天）
with recursive x(dy,cnt) as(
	select adddate(current_date,(-dayofyear(current_date))+1) dy,1
	union all
	select adddate(dy,interval 3 month),cnt+1 from x where cnt+1 <= 4
)select dy as q_start,adddate(date_add(dy,interval 3 month),-1) as q_end from x;
复制

-- PostgreSQL：找出当前日期所属年份的第一天，并使用CTE填充其他3个季度的第一天，再找出每个季度的最后一天
with recursive x(dy,cnt) as(
	select current_date - cast(extract(day from current_date)as integer)+1,1 dy
	union all
	select cast(dy + interval '3 months' as date),cnt+1 from x where cnt+1 <= 4
)select dy as q_start,cast(dy + interval '3 months' as date)-1 as q_end from x;
复制