SQL基础操作_7_时间运算
7.8 时间运算
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=73697.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 |
... | ... | ... | ... | ... | ... |