SQL基础操作_7_时间运算

目录

​​7.8 时间运算​​

​​7.8.1 对时间列进行加减​​

​​7.8.2 计算两个时间列的差值​​

​​7.8.3 计算两个时间列工作日差值​​

​​7.8.4 计算时间列所在的周的序号​​

​​7.8.5 计算时间列所在年的周序号​​

---

7.8 时间运算

注:数据集和表结构见 ​​SQL基础操作_1_检索数据​​

7.8.1 对时间列进行加减

需求：对EMP表里员工编号为7369的hiredate按照天、月、年各加.

解决方法:通过DATEADD函数来完成.

SQL Server:

SELECT empno,hiredate,DATEADD(DAY,1,hiredate) next_Day,
DATEADD(MONTH,1,hiredate) next_Month,
DATEADD(YEAR,1,hiredate) next_Year
FROM emp
WHERE empno = 7369

执行结果：

empno	hiredate	next_Day	next_Month	next_Month
7369	1980-12-17	1980-12-18	1981-01-17	1981-12-17

MySQL:

SELECT empno,hiredate,DATE_ADD(hiredate,INTERVAL1DAY) AS next_Day,
DATE_ADD(hiredate,INTERVAL1MONTH) AS next_Day,
DATE_ADD(hiredate,INTERVAL1 YEAR) AS next_Day
FROM emp
WHERE empno=7369

7.8.2 计算两个时间列的差值

需求：对EMP表里员工KING和SMITH的hiredate入职时间差，这里单位是分钟、小时、天、周、月、年.

解决方法:通过DATEDIFF函数来完成.

Mysql：

SELECT B.ename b_Ename,B.HIREDATEb_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate,
TIMESTAMPDIFF(MINUTE,A.HIREDATE,B.HIREDATE)minute_diff,
TIMESTAMPDIFF(HOUR,A.HIREDATE,B.HIREDATE)hour_diff,
DATEDIFF(B.HIREDATE,A.HIREDATE) day_diff,
TIMESTAMPDIFF(WEEK,A.HIREDATE,B.HIREDATE)week_diff,TIMESTAMPDIFF(MONTH,A.HIREDATE,B.HIREDATE)month_diff,
TIMESTAMPDIFF(YEAR,A.HIREDATE,B.HIREDATE) year_diff
FROM emp A,emp B
WHERE A.ename ='SMITH'AND B.ename ='KING'

执行结果：

b_Ename	b_hiredate	a_Ename	a_hiredate	minute_diff	hour_diff	day_diff	week_diff	month_diff	year_diff
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	482400	8040	335	47	11	0

SQL Server：

SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,DATEDIFF(MINUTE,A.HIREDATE,B.HIREDATE) minute_Diff
,DATEDIFF(HOUR,A.HIREDATE,B.HIREDATE) hour_Diff
,DATEDIFF(DAY,A.HIREDATE,B.HIREDATE) day_Diff
,DATEDIFF(WEEK,A.HIREDATE,B.HIREDATE) week_Diff
,DATEDIFF(MONTH,A.HIREDATE,B.HIREDATE) month_Diff
,DATEDIFF(YEAR,A.HIREDATE,B.HIREDATE) year_Diff
FROM emp A,emp B
WHERE A.ename = 'SMITH' AND B.ename = 'KING'

执行结果:

b_Ename	b_hiredate	a_Ename	a_hiredate	minute_Diff	hour_Diff	day_Diff	week_Diff	month_Diff	year_Diff
KING	1981-11-17	SMITH	1980-12-17	482400	8040	335	48	11	1

7.8.3 计算两个时间列工作日差值

需求：对EMP表里员工KING和SMITH的hiredate入职时间差，这里单位是天且是工作日时间，即周末不计算在内.

解决方法:通过DATEDIFF函数来完成.

SQL Serer：

SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,SUM(CASE WHEN DATENAME(DW,DATEADD(DAY,seq.pos,A.HIREDATE)) IN('星期六','星期日') THEN 0 ELSE 1 END) AS workday_Num
FROM emp A,emp B,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P') seq
WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <= DATEDIFF(DAY,A.HIREDATE,B.HIREDATE)
GROUP BY B.ename ,B.HIREDATE,A.ename , A.HIREDATE

执行结果：

b_Ename	b_hiredate	a_Ename	a_hiredate	workday_Num
KING	1981-11-17	SMITH	1980-12-17	240

Mysql：

SELECT B.ename b_Ename,B.HIREDATEb_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate,
SUM(CASE WHEN DATE_FORMAT(DATE_ADD(A.hiredate,INTERVAL pos DAY),'%w') IN (0,6) THEN 0 ELSE 1 END) AS workday_Num
FROM emp A,emp B,(SELECT i-1 AS pos FROM tb_incr) seq
WHERE A.ename ='SMITH'AND B.ename ='KING'AND seq.pos <=DATEDIFF(B.HIREDATE,A.HIREDATE)
GROUPBY B.ename,B.HIREDATE,A.ename , A.HIREDATE

执行结果：

b_Ename	b_hiredate	a_Ename	a_hiredate	workday_Num
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	240

7.8.4 计算时间列所在的周的序号

需求：对EMP表里员工KING和SMITH的hiredate入职时间差，基于生成的日期算每天所在的周序号.

解决方法:通过DATE_FORMAT函数来完成.

SQL Server:

SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,DATEADD(DAY,seq.pos,A.HIREDATE) AS date_Seq
,CASE DATEPART(DW,DATEADD(DAY,seq.pos,A.HIREDATE)) WHEN 1 THEN '星期日'
   WHEN 2 THEN '星期一'
   WHEN 3 THEN '星期二'
   WHEN 4 THEN '星期三'
   WHEN 5 THEN '星期四'
   WHEN 6 THEN '星期五'
   WHEN 7 THEN '星期六' END ASweekno
FROM emp A,emp B,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P') seq
WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <= DATEDIFF(DAY,A.HIREDATE,B.HIREDATE)

-- 或者借助datename函数
SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,DATEADD(DAY,seq.pos,A.HIREDATE) AS date_Seq
,DATENAME(DW,DATEADD(DAY,seq.pos,A.HIREDATE)) AS weekno
FROM emp A,emp B,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P') seq
WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <= DATEDIFF(DAY,A.HIREDATE,B.HIREDATE)

执行结果:

b_Ename	b_hiredate	a_Ename	a_hiredate	date_Seq	weekno
KING	1981-11-17	SMITH	1980-12-17	1980-12-17	星期三
KING	1981-11-17	SMITH	1980-12-17	1980-12-18	星期四
KING	1981-11-17	SMITH	1980-12-17	1980-12-19	星期五
KING	1981-11-17	SMITH	1980-12-17	1980-12-20	星期六
KING	1981-11-17	SMITH	1980-12-17	1980-12-21	星期日
KING	1981-11-17	SMITH	1980-12-17	1980-12-22	星期一
KING	1981-11-17	SMITH	1980-12-17	1980-12-23	星期二
…	…	…	…	…	…

注:函数DATEPART(DW,DateValue)返回周序号,如果是星期日则是返回 1,是星期六则是7.

Mysql:

SELECT B.ename b_Ename,B.HIREDATEb_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate,
CASE DATE_FORMAT(DATE_ADD(A.hiredate,INTERVAL pos DAY),'%w') WHEN 0 THEN '周日'
WHEN 1 THEN '星期一'
WHEN 2 THEN '星期二'
WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四'
WHEN 5 THEN '星期五'
WHEN 6 THEN '星期六' END AS weekno
FROM emp A,emp B,(SELECT i-1AS pos FROM tb_incr) seq
WHERE A.ename ='SMITH'AND B.ename ='KING'AND seq.pos <=DATEDIFF(B.HIREDATE,A.HIREDATE)

结果同上.

7.8.5 计算时间列所在年的周序号

需求：对EMP表里员工KING和SMITH的hiredate入职时间差，基于生成的日期算每天所在的周，相对于年.

解决方法:通过DATE_FORMAT函数来完成.

Mysql：

SELECT B.ename b_Ename,B.HIREDATEb_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,DATE_ADD(A.hiredate,INTERVAL pos DAY) date_Seq
,DATE_FORMAT(DATE_ADD(A.hiredate,INTERVAL pos DAY),'%U') AS weekno_Year
FROM emp A,emp B,(SELECT i-1AS pos FROM tb_incr) seq
WHERE A.ename ='SMITH'AND B.ename ='KING'AND seq.pos <=DATEDIFF(B.HIREDATE,A.HIREDATE)

执行结果:

b_Ename	b_hiredate	a_Ename	a_hiredate	date_Seq	weekno_Year
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/17 0:00:00	50
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/18 0:00:00	50
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/19 0:00:00	50
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/20 0:00:00	50
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/21 0:00:00	51
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/22 0:00:00	51
KING	1981/11/17 0:00:00	SMITH	1980/12/17 0:00:00	1980/12/23 0:00:00	51

SQL Server:

SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate
,DATEADD(DAY,seq.pos,A.HIREDATE) AS date_Seq
,DATEPART(WK,DATEADD(DAY,seq.pos,A.HIREDATE)) AS weekno_Year
FROM emp A,emp B,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P') seq
WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <= DATEDIFF(DAY,A.HIREDATE,B.HIREDATE)

执行结果:

b_Ename	b_hiredate	a_Ename	a_hiredate	date_Seq	weekno_Year
KING	1981-11-17	SMITH	1980-12-17	1980-12-17	51
KING	1981-11-17	SMITH	1980-12-17	1980-12-18	51
KING	1981-11-17	SMITH	1980-12-17	1980-12-19	51
KING	1981-11-17	SMITH	1980-12-17	1980-12-20	51
KING	1981-11-17	SMITH	1980-12-17	1980-12-21	52
KING	1981-11-17	SMITH	1980-12-17	1980-12-22	52
KING	1981-11-17	SMITH	1980-12-17	1980-12-23	52
KING	1981-11-17	SMITH	1980-12-17	1980-12-24	52
...	...	...	...	...	...