Excel & SQL | 日期和时间函数 |

日期和时间函数是我们日常工作中使用频率比较高的一部分。这里需要强调的两个概念就是日期和时间,日期是指年月日,时间是指时分秒。

获取当前时刻的数据

获取当前时刻的数据就是获取程序运行的那一刻与时间相关的数据,比如,年月日、时分秒等。

- 获取当前时刻的日期和时间

对于获取当前时刻的日期和时间,在Excel中和在SQL中用的都是now()函数。

在Excel中,如果要获取当前时刻的日期和时间,直接在指定单元格中输入now()即可。而在SQL中,只需在select后面写上now()即可,具体实现代码如下∶

select now();
图01

- 获取当前时刻的日期

now()函数获取的是当前时刻的日期和时间,有时候,我们可能只需要获取当前时刻的日期,并不需要获取当前时刻的时间,这个时候,在SQL中将now()函数换成curdate()函数,可以获取当前时刻的日期。

select curdate();
图02

curdate()函数用于直接获取当前时刻的日期,我们也可以先通过now()函数获取当前时刻的日期和时间,然后通过date()函数将日期和时间转化为日期,具体实现代码如下︰

select date(now());

运行上面的代码,会得到与使用curdate()函数相同的结果。

我们也可以只获取日期中的年、月、日,使用的是year()函数,具体实现代码如下︰

select year(now());
select month(now());
select day(now());

- 获取当前时刻的时间

select curtime(); 
select time(now());
select hour(now()),minute(now()),second(now()); -- 分别获取时分秒

- 获取当前时刻所属的周数

select weekforyear(now()); -- 查看这是今年的第几周
select dayofweek(now()); -- 注意:星期天是第一天

- 获取当前时刻所属的季度

除了看月、周维度,我们有时候还会看季度维度,很多公司会有季度任务、季度考核等。所以我们也需要获取当前时刻或某些时刻所属的季度。

全年一般分为四个季度,每个季度包括三个月,1~3月是第一季度、4~6月是第二季度、7~9月是第三季度、10~12月是第四季度。在SQL中,要获取某个时刻所属的季度使用的是quarter()函数。具体实现代码如下︰

select quarter(now());

日期和时间格式转换

我们知道,同一个日期和时间会有多种不同的表示方式,有时候需要在不同格式之间进行相互转换。

在SQL中,我们使用的是date_format()函数,date_format()函数的格式如下∶

date_format(datetime,format)

其中,datetime表示要转换的具体的日期和时间, format表示要转换的格式,可选的格式如下表所示。

图03
select date_format(now(),"%Y-%m-%d %H:%i:%S");
图04

除了date_format()函数,还有另一个函数extract() ,用于返回一个具体日期和时间中的单独部分,比如,年、月、日、小时、分钟等。具体形式如下︰

extract(unit from datetime)

其中,datetime表示具体的日期和时间,unit表示要从datetime中返回的单独的部分。unit的取值如下表所示。

图05
select 
	extract(year from "2019-12-25 22:47:37") as col1,
	extract(month from "2019-12-25 22:47:37") as col2,
	extract(day from "2019-12-25 22:47:37") as col3;
图06

日期和时间运算

有时候,我们也需要对日期和时间进行运算,比如,我们要获取今天之前的7天对应的日期,或者今天之后的13天对应的日期,可以翻日历,也可以数数,但是这些方法肯定都不是最简单的方法。所以需要对日期和时间进行运算。

- 向后偏移时间和日期

比如,我们要获取今天之后的x天对应的日期和时间,就相当于在今天日期和时间的基础上加x天,我们把这称为向后偏移。

在SQL中实现向后偏移我们可以使用date_add()函数,具体形式如下∶

data_add(date,interval num unit)

其中,date表示当前的日期,或者当前的日期和时间; interval是一个固定的参数;num为上面讲到的×; unit表示要加的单位,是往后移动7天、7月还是7年,可选值与extract()函数中unit的可选值是一样的。

-- year,month,day,hour,minute,second
select date(now()) as col,date_add(date(now()),interval 7 year) as col2;
图07

- 向前偏移时间和日期

在SQL中实现向后偏移我们可以使用date_add()函数,具体形式如下∶

data_sub(date,interval num unit)
-- year,month,day,hour,minute,second
select date(now()) as col,date_sub(date(now()),interval 7 year) as col2;

- 两个日期做差

除了向前偏移、向后偏移,有时候,我们还需要获取两个日期之差。

在SQL中两个日期之间做差我们使用的是datediff()函数,datediff()函数用于返回两个日期之间相差的天数,具体形式如下∶

datediff(end_date,start_date)

上面的代码表示end_date减去start_date。具体实现代码如下︰

select datediff(date_add(now(),interval 8 day),date(now()));
图08

- 两个日期之间的比较

有时候,我们也需要对两个日期进行比较,比如,把大于某个日期的订单全部筛选出来。

两个日期之间的比较和两个数字之间的比较是一样的。

select
	"2019-01-01" > "2019-01-02" as col1,
	"2019-01-01" < "2019-01-O2" as col2,
	"2019-01-01" = "2019-01-02" as col3,
	"2019-01-01" != "2019-01-02" as col4;

小结

获取当前时刻的数据
    获取当前日期和时间 select now;
    获取当前日期 select curdate(); select date(now()); select year(now()),month(now()),day(now());
    获取当前时间 select curtime(); select time(now());select hour(now()),minute(now()),second(now());
    获取当前所属周数 weekofyear(now()) dayofweek(now())
    获取当前所属季度 quarter(now());

日期和时间格式转换
    格式转换 date_format(时间,格式) select date_format(now(),"%Y-%m-%d %H:%i:%S");
    提取时间 extract(unit from 时间)
        select 
            extract(year from "2019-12-25 22:47:37") as col1,
            extract(month from "2019-12-25 22:47:37") as col2,
            extract(day from "2019-12-25 22:47:37") as col3;

日期和时间运算
    向后偏移时间和日期 date_add(时间,interval num unit)
    向后偏移时间和日期 date_sub(时间,interval num unit)
    日期做差 datediff(end_time,start_time)
    日期比较 两个日期之间的比较和两个数字之间的比较是一样的

原文链接:https://www.cnblogs.com/Rowry/p/15101770.html

作者: Hugh

Welcome to Wan's world~