Mysql日期时间函数

1.获取当前时刻时间

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

1.1返回当前时刻的日期和时间

返回当前时刻的日期和时间在ESql中用的是now()函数，直接在select后面写上now()函数即可，具体代码如下：

select now()

运行上面代码就会得到你程序运行这一刻所处的年月日、时分秒的信息，比如：2019-12-25 22:47:37。

1.2获取当前时刻的日期

前面的now()函数获取的是当前时刻的日期和时间，我们有的时候可能只需要当前时刻的日期部分，并不需要时间部分，这个时候在在Sql中将now()函数换成curdate()函数，就是获取当前时刻的日期部分，具体代码如下：

select curdate()

通过运行上面的代码，我们得到了当前时刻的日期部分：2019-12-25

curdate()函数是直接获取当前时刻的日期部分，我们也可以先通过now()函数获取当前时刻的日期时间，然后再通过date()函数将日期时间转化为日期部分，具体代码如下：

select date(now())

通过运行上面的代码，会得到与curdate()函数相同的结果。

我们也可以只获取日期中的年，使用的是year()函数，具体代码如下：

select year(now())

通过运行上面的代码，最后得到的结果为2019。

我们也可以只获取日期中的月，使用的是month()函数，具体代码如下：

select month(now())

通过运行上面的代码，最后得到的结果为12。

我们也可以只获取日期中的日，使用的是day()函数，具体代码如下：

select day(now())

通过运行上面的代码，最后得到的结果为25。

1.3获取当前时刻的时间

我们除了有只获取当前时刻的日期的需求外，我们还有只获取当前时刻的时间需求。如果我们想只获取当前时刻的时间，只需要把只获取当前时刻日期的curdate()函数换成curtime()函数即可，具体代码如下：

select curtime()

通过运行上面的代码，就可以获取当前时刻的时间部分：22:47:37。

我们也可以先通过now()函数获取当前时刻的日期时间，然后再通过time()函数将日期时间转化为时间部分，具体代码如下：

select time(now())

通过运行上面的代码，会得到与curdate()函数相同的结果。

我们也可以只获取时间中的小时，使用的是hour()函数，具体代码如下：

select hour(now())

通过运行上面的代码，最后得到的结果为22。

我们也可以只获取时间中的分钟，使用的是minute()函数，具体代码如下：

select minute(now())

通过运行上面的代码，最后得到的结果为47。

我们也可以只获取时间中的秒，使用的是second()函数，具体代码如下：

select second(now())

通过运行上面的代码，最后得到的结果为37。

1.4获取当前时刻的周数

上面我们讲了如何获取当前时刻的日期时间、日期、时间这三部分。这一节我们再看下如何获取当前时刻所属的周数。我们一般会将全年分为52周(365天/7)，有的时候也可能是53周，如果我们想看下当前时刻是全年中的第几周，可以使用weekofyear()函数，具体代码如下：

select weekofyear(now())

通过运行上面的代码，最后得到的结果为52。

除了获取当前是全年的第几周以外，我们还需要获取当天是一周内的周几。在Sql中使用的dayofweek()函数，具体代码如下：

select dayofweek(now())

通过运行上面的代码，最后得到结果为5，2019年12月25日应该是周四哈，为什么结果是5呢，这是因为该函数中一周是从周日开始的，也就是周日对应的是1，周一对应的是2，以此类推，所以周四对应的是5。

1.4获取某时刻距离公元0年的天数

select weekofyear(now())

通过运行上面的代码，最后得到的结果为52。

除了获取当前是全年的第几周以外，我们还需要获取当天是一周内的周几。在Sql中使用的dayofweek()函数，具体代码如下：

select dayofweek(now())

通过运行上面的代码，最后得到结果为5，2019年12月25日应该是周四哈，为什么结果是5呢，这是因为该函数中一周是从周日开始的，也就是周日对应的是1，周一对应的是2，以此类推，所以周四对应的是5。

2.日期时间格式转换

我们知道同一个日期时间会有多种不同的表示方式，有的时候需要在不同格式之间相互转换。在Sql中我们用的是date_format()函数，date_format函数格式如下：

date_format(datetime,format)

datetime表示要被转换的具体的日期时间，format表示要转换成的格式，可选的格式如下：

主题	格式	描述
年	%Y	4位数的年
月	%b	月份对应的英文缩写
月	%M	月份对应的英文全称
月	%m	01-12的月
月	%c	1-12的月份数值
日	%d	01-31的某月里面的第几天
日	%e	1-31的某月里面的第几天
日	%D	用th后缀表示某月中的第几天
日	%j	001-366的一年中的第几天
周	%a	星期几对应的英文缩写
周	%W	星期几对应的英文全称
时	%H	00-23的小时
时	%h	01-12的小时
分	%i	00-59的分钟
秒	%S	秒(00-59)
秒	%f	微秒
时分秒	%T	返回当前的时分秒, 24-小时 (hh:mm:ss)

select date_format("2019-12-25 22:47:37","%Y-%m-%d")

通过运行上面的代码，就会返回4位数的年、01-12的月、01-31的天，三者之间且用-分隔开来，即2019-12-25。

这里需要注意下1和01的区别，本质上都是表示的1，但是展示上会有些不太一样，比如下面代码中，我们的原日期是2019-1-1，但是返回的结果是2019-01-01的。

select date_format("2019-1-1 22:47:37","%Y-%m-%d")
select date_format("2019-12-25 22:47:37","%H:%i:%S")

通过运行上面的代码就会返回00-23的小时、00-59的分、00-59的秒，三者之间用:分隔开来，即22:47:37。

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

extract(unit from datetime)

datetime表示具体的日期时间，unit表示要从datetime中返回的单独的部分。unit值可以是下列的值：

unit	说明
year	年
month	月
day	日
hour	小时
minute	分钟
second	秒
week	周数，全年第几周

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

通过运行上面的代码，就会分别获取到datetime中的年月日，具体结果如下：

col1	col2	col3
2019	12	25

3.日期时间运算

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

3.1向后偏移时间

比如我们要获取今天之后的x天对应的日期，就是相当于在今天日期的基础上加x天，我们把这叫做向后偏移，这个时候就可以使用date_add()函数，具体形式如下：

date_add(date,interval num unit)

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

select 
    "2019-01-01" as col1
    ,date_add("2019-01-01",interval 7 year) as col2
    ,date_add("2019-01-01",interval 7 month) as col3
    ,date_add("2019-01-01",interval 7 day) as col4

通过运行上面的代码，就会返回2019-01-01往后7年、7月、7天对应的日期，具体结果如下：

col1	col2	col3	col4
2019-01-01	2026-01-01	2019-08-01	2019-01-08

select 
    "2019-01-01 01:01:01" as col1
    ,date_add("2019-01-01 01:01:01",interval 7 hour) as col2
    ,date_add("2019-01-01 01:01:01",interval 7 minute) as col3
    ,date_add("2019-01-01 01:01:01",interval 7 second) as col4

通过运行上面的代码，就会返回2019-01-01 01:01:01往后7小时、7分钟、7秒对应的日期，具体结果如下：

col1	col2	col3	col4
2019-01-01 01:01:01	2019-01-01 08:01:01	2019-01-01 01:08:01	2019-01-01 01:01:08

4.2向前偏移时间

有向后偏移，就会有向前偏移。比如我们要获取今天之前的若干天，就是相当于是在当前日期的基础上减去x天，这个时候我们使用的是date_sub()函数，date_sub与date_add的函数形式是一样的。把上面代码中的date_add换成date_sub就表示向前偏移。

select 
    "2019-01-01" as col1
    ,date_sub("2019-01-01",interval 7 year) as col2
    ,date_sub("2019-01-01",interval 7 month) as col3
    ,date_sub("2019-01-01",interval 7 day) as col4

通过运行上面的代码，就会返回2019-01-01往前7年、7月、7天对应的日期，具体结果如下：

col1	col2	col3	col4
2019-01-01	2012-01-01	2018-06-01	2018-12-25

向前偏移指定的时间，我们除了使用date_sub以外，我们还可以继续使用date_add，只不过把加的具体num值换成负数就行，比如7换成-7即可，具体实现代码如下：

select 
    "2019-01-01" as col1
    ,date_add("2019-01-01",interval -7 year) as col2
    ,date_add("2019-01-01",interval -7 month) as col3
    ,date_add("2019-01-01",interval -7 day) as col4

通过运行上面的结果与使用date_sub得出来的结果是一致的。

4.3两日期做差

上面讲完了向前偏移、向后偏移，我们有的时候还需要获取两日期之差，使用的datediff()函数，datediff用于返回两日期之间相差的天数，函数形式如下：

datediff(end_date,start_date)

我们是用end_date去减start_date的。

select datediff("2019-01-07","2019-01-01")

通过运行上面的代码，会返回2019-01-07与2019-01-01之间的天数差，结果为6。

TIMESTAMPDIFF() 是 MySQL 中用来计算两个日期或时间之间的差值的函数。该函数返回两个日期/时间之间的差值，可以指定差值的单位（秒、分钟、小时、天等）。TIMESTAMPDIFF() 函数的语法如下：

TIMESTAMPDIFF(unit, start_date, end_date)

参数说明：

• unit：表示差值的单位，可以是以下值之一：MICROSECOND（微秒）、SECOND（秒）、MINUTE（分）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）、QUARTER（季度）或 YEAR（年）。
• start_date：表示时间段的起始时间。
• end_date：表示时间段的结束时间。
  例如，我们可以使用 TIMESTAMPDIFF() 函数计算两个日期之间的天数差异，如下所示：

SELECT TIMESTAMPDIFF(DAY, '2021-01-01', '2021-01-10')

以上 SQL 查询将返回 9，表示从 2021 年 1 月 1 日到 2021 年 1 月 10 日一共相隔了 9 天。

4.4TO_DAYS(date)函数

to_days函数：返回从0000年（公元1年）至当前日期的总天数。

利用to_days函数查询今天的数据：

select * from 表名 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1

7天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m' ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1

查询本季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

查询上季度数据

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

查询上年数据

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询当前月份的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();