MySQL的日期和时间函数(十二)

我看到了那天的夕阳，美得如此骄艳，我便决定，追寻夕阳，拼尽余生。

上一章简单介绍了 MySQL的字符串函数(十一),如果没有看过,​​请观看上一章​​

一. MySQL的日期时间函数

MySQL中关于日期/时间的数据类型共有五个， year,time,date,datetime,timestamp 。 其中，最重要的就是data,datatime,timestamp三个。 关于日期和日期， 在Java 中存在着 java.util.Date 类来进行日期的处理， MySQL数据库中也提供了大量的函数来对日期/时间进行处理。

MySQL的日期函数有:

函数	作用	举例 now()是不确定时间	举例结果
curdate()	获取当前日期	curdate()	2019-11-27
current_date()	获取当前日期	current_date()	2019-11-27
curtime()	获取当前时间	curtime()	14:24:03
current_time	获取当前时间	current_time	14:24:03
current_timestamp()	获取当前日期和时间	current_timestamp()	2019-11-27 14:24:03
localtime()	获取当前日期和时间	localtime()	2019-11-27 14:24:03
now()	获取当前日期和时间	now()	2019-11-27 14:24:03
sysdate()	获取当前日期和时间	sysdate()	2019-11-27 14:24:03
unix_timestamp(d1)	日期转换成时间戳	unix_timestamp(now())	1574835843
from_unixtime(n)	时间戳转换成对应日期	from_unixtime(1574835843)	2019-11-27 14:24:03
year(d1)	获取年份	year(now())	2019
quarter(d1)	获取季度	quarter(now())	4
month(d1)	获取月	month(now())	11
week(d1)	获取第几周	week(now())	47
dayOfYear(d1)	获取这一天在这一年的哪一天	dayOfYear(now())	331
dayOfMonth(d1)	获取这一天在这一月的哪一天	dayOfMonth(now())	27
dayOfWeek(d1)	获取这一天在这一个星期的哪一天	dayOfWeek(now())	4
hour(d1)	获取第几小时	hour(now())	14
minute(d1)	获取分钟	minute(now())	24
second(d1)	获取秒	second(now())	3
microsecond(d1)	获取毫秒	microsecond(now())	234
extract(type from d1)	获取指定的值	extract(year from now())	2019
utc_date()	世界标准日期	utc_date()	2019-11-28
utc_time()	世界标准时间	utc_time()	01:51:23
time_to_sec(t1)	时间转换成秒	time_to_sec(now())	57276
sec_to_time(n)	秒转换成时间	sec_to_time(57276)	15:53:36
monthname(d1)	获取月英文名	monthname(now())	November
dayname(d1)	获取星期的英文名	dayname(now())	Wednesday
weekday(d1)	工作日索引	weekday(now())	2
last_day(d1)	当月最后一天	last_day(now())	2019-11-30
date(d1)	提取日期	date(now())	2019-11-27
time(d1)	提取时间	time(now())	14:24:03
addDate(d1,interval n type)	往后添加	addDate(now(),1)	2019-11-28 16:44:39
data_add(d1,interval n type)	往后添加	date_add(now(),interval 2 month)	2020-01-27 16:44:39
subDate(d1,interval n type)	往前减去	subDate(now(),1)	2019-11-26 16:50:30
date_sub(d1,interval n type)	往前减去	date_sub(now(),interval 2 month)	2019-09-27 16:50:30
datediff(d1,d2)	两个日期相差多少天	datediff(now(),‘2018-11-27’)	365
addTime(t1,t2)	时间往后相加	addTime(time(now()),‘01:02:03’)	18:10:32
subTime(t1,t2)	时间往前相减	subTime(time(now()),‘01:02:03’)	16:08:49
date_format(d1,f)	将日期转换成字符串	date_format(now(),’%Y%m%d %H%i%S’)	20191127 172713
time_format(t1,f)	将时间转换成字符串	time_format(now(),’%H%i%S’)	172839
str_to_date(str,f)	将字符串转换成日期	str_to_date(‘27/11/2019’,’%d/%m/%Y’)	2019-11-27

二. MySQL 日期时间函数的分类

MySQL可以将日期/时间函数大致分为以下重要的类别:

• 获取当前的日期和时间
• UNIX时间戳
• 从一个具体日期中获取相应信息
• Extract 获取具体信息
• UTC 世界时间
• 时间和秒转换
• 其他单日期简单查询
• 两个日期之间的比较
• 日期和时间的格式化

下面，会进行详细的分析解释。

三. 获取当前的日期和时间

三.一 获取当前的日期 curdate() 和current_date()

只获取当前的日期， 用的函数是 : curdate() 和 current_date() 两个函数。 会按照’YYYY-MM-dd’ 或者 'YYYYMMDD’的格式展示出来。

select curdate(),current_date();

老蝴蝶现在的时间是 2019年11月27日。

可以将上面的日期 通过加 0 转换成相应的数字。

select curdate(), curdate()+0;

也可以+1,+2 等。 curdate()+数字，就会先将 curdate() 变成 'YYYYMMDD’的形式，变成纯数字，然后与 后面的数字进行相加。

select curdate()+1,curdate()+100;

三.二 获取当前的时间 curtime() 和current_time()

只获取当前的时间，用的函数是: curtime() 和current_time() 两个函数。 会按照 ‘HH:mm:SS’ 或者 'HHmmSS’的格式展示出来。

也可以加数字 0 或者其他数字值。

select curtime(),current_time(), curtime()+0,curtime()+1,curtime()+100;

三.三 获取当前的日期和时间 current_timestamp(),localtime(),now(),sysdate()

既获取日期，也获取时间。 有四个函数， current_timestamp(), localtime(), now(), sysdate(). 会按照 ‘YYYY-MM-DD HH:mm:SS’ 或者 ‘YYYYMMDDHHmmSS’ 的格式进行相应的展示。 注意，是localtime(), 不是 localetime(). 没有那个e.

select current_timestamp(), localtime(),now(),sysdate();

四个展示的时间是完全一样的。 这四个函数通常使用 now() 函数，因为它方法名短，简短易记。

三.三.一 now()与 sysdate() 的区别

其中， now() 与 sysdate() 是有一些区别的。

1 . now() 是在方法执行开始时，就得到的值。 是固定的值。

2 . sysdate() 是在方法执行时，动态获取的。

这两个方法，平常看起来是相同的， 但牵扯到休眠线程问题时，可能就会不同。

先看now 的 .

select now(),sleep(5),now();

暂停5秒之后展示数据。

发现，前后值是一样的。

再看 sysdate()

select sysdate(),sleep(5),sysdate();

暂停5秒之后，展示数据。

发现，前面的值是34，后面的秒数是39. 中间相差5秒。 是动态获取的。

也可以与 curdate(), curtime(） 一样，加数字。 但没有什么重要意义，后面就不展示这些了。

select now(),now()+0,now()+1,now()+100;

以下中的 now() 如果没有特殊说明，均指的是 ‘2019-11-27’ 这一天。

四. UNIX 时间戳

可以获取相应的时间戳， 是 GMT (格林尼冶时间)， 从1970年 1月1日，00:00:00 开始秒数。

四.一 将日期转换成时间戳 unix_timestamp(date)

传递一个日期，展示出来距离1970年的时间戳。 用的是 UNIX_timestamp(date) 函数。 其中， date 参数可以省略不写，如果不写的话，表示默认是当前的时间。 date 传入值时，只要传入符合格式的日期字符串即可，可以为 date,datetime,timestamp或者数字。

select now(),unix_timestamp(),unix_timestamp(now()),unix_timestamp('1995-02-07 11:23:59');

一个是 1574835843, 95年那个是 792127439

四.二 将时间戳转换成日期 from_unixtime(数字或数字型字符串)

将时间戳转换成相应的日期, 用 from_unixtime(数字字符串) 函数。 用刚才的那两个时间戳数字。 与 unix_timestamp(date) 函数互为反函数。

select from_unixtime('1574835843'),from_unixtime(792127439);

五. 从一个日期时间中获取相应的年，月，日，时，分，秒信息

用一个 now() 当前日期中获取。

五.一 获取年 year(date)

select year(now());

五.二 获取季度 quarter(date)

select quarter(now());

取值范围是 1~4. 其中，MySQL中: 1~3月是第一季度， 4~6是第二季度， 7~9是第三季度， 10~12是第四季度。

五.三 获取月 month(date)

select month(now());

取值范围是 1~12 。

五.四 获取周 week(date)

当前日期，是第几周。

select week(now()),week('2020-01-01'),week('2020-01-06');

取值范围是 0~52, 从0 开始的。 一年最多是53个星期天。

五.五 获取天 dayofyear(date), dayofmonth(date)，dayofweek(date)

获取天的话，有三种意思。 一种是 在这一年中的天，一种是在这月中的天，一种是在这个星期的天。
通常指的是在这一月中的天。

五.五.一 获取在这一年中的天 dayofyear(date)

select dayofyear(now());

取值范围是 1~366.

五.五.二 获取在这一月中的天 dayofmonth(date)

select dayofmonth(now());

取值范围是 1~31.

五.五.三 获取在这一周中的天 dayofweek(date)

select dayofweek(now());

其中，星期日为第一天，显示1. 今天27号，是星期三，显示4.

取值范围是 1~7.

五.六 获取小时 hour(date)

select now(),hour(now());

取值范围是 0~23 .

五.七 获取分钟 minute(date)

select now(),minute(now());

取值范围为 0~59

五.八 获取秒 second(date)

select now(), second(now());

取值范围为 0~59.

五.九 获取毫秒 microsecond(date)

now() 看不出毫秒数，自己构建 字符串的日期。 秒与毫秒之间，用. 隔开

select microsecond('2019-11-27 15:09:08.234');

取值范围为 000000~999999

六. 获取日期的指定值 Extract(type from date)

在 章节五 部分，可以通过 函数 如 year(), month() 等来获取解析 某个日期的年，月，日，时，分，秒等信息。 MySQL 也提供了 extract() 函数来统一获取指定值。 其中，type就是指定类型。

六.一 extract 方法中 type的指定类型有哪些

类型名	解释
YEAR	年
QUARTER	季度
MONTH	月
WEEK	年中的第几周
DAY	月中的第几天
HOUR	小时
MINUTE	分钟
SECOND	秒
MICROSECOND	微秒
YEAR_MONTH	获取年和月
DAY_HOUR	获取天和小时
DAY_MINUTE	获取天和分钟
DAY_SECOND	获取天和秒
DAY_MICROSECOND	获取天和微秒
HOUR_MINUTE	获取小时和分钟
HOUR_SECOND	获取小时和秒
HOUR_MICROSECOND	获取小时和毫秒
MINUTE_SECOND	获取分钟和秒
MINUTE_MICROSECOND	获取分钟和毫秒
SECOND_MICROSECOND	获取秒和毫秒

六.二 extract(type from date)的举例

获取年，获取月，获取日，获取年和月，获取天和小时

select now(),extract(year from now()) as '年',extract(month from now()) as '月',
  extract(year_month from now()) as '年月',extract(day_hour from now()) as '天小时';

但type 中并没有年和日 这样的拼法。

只能拼接 type 提供的类型。 所以，拼接不成 年月日， 月日这样的。

建议，对于复杂的类型，使用 函数获取后进行拼接。简单的，直接用 extract(type from date)

七. UTC 世界标准时间

与当前所处的时区无关。 显示的是世界时间。

七.一 当前的日期 UTC_DATE()

会按照’YYYY-MM-dd’ 或者 'YYYYMMDD’的格式展示出来. 可以加数字 0,1,100.

select now(), utc_date();

七.二 当前的时间 UTC_TIME()

会按照 ‘HH:mm:SS’ 或者 'HHmmSS’的格式展示出来。 可以加数字，0，1，100

select now(),utc_time();

老蝴蝶忘记截屏了，在第二天早上28号，重新生成一下。

展示的是

时间并不是当前的时间，而是世界时间。

八. 时间和秒转换函数

可以将 时间转换成对应的秒数， 也可以将秒数转换成相应的时间。

时间转换成秒数公式:

总秒数= 小时 * 3600+分钟 * 60+秒。

秒数转换成时间公式:

小时 hour= 总秒数/3600

分钟 minute=(总秒数-3600 * hour)/60

秒 second= 总秒数-（3600 * hour+60 * minute）

就是这个公式计算，只是mysql提供了相应的函数。

八.一 时间转换成秒 time_to_sec(time)

将时间按照上面的公式，转换成相应的秒。

select now(),time_to_sec(now());

最终秒数为 57276

其中， 153600+5460+36=57276

八.二 秒转换成时间 sec_to_time(秒数)

与 time_to_sec(time) 互为反函数。

select sec_to_time(57276);

九 其他单日期简单查询

除了上面的基本操作之外，单个日期还有一些其他的操作。

九.一 展示月名 monthname(date)

将月名 默认按照英文进行展示。

select monthname(now());

November 为11月的英文单词。

九.二 展示星期的名 dayname(date)

将星期几 以英文的形式展示出来。

select dayname(now());

Wednesday 为星期三的英文单词。

九.三 展示工作日索引 weekday(d)

展示的也是 这一天，是这一周的第几个。

select weekday(now());

与 dayofweek() 类似 。但dayofweek() 是星期日表示1， 星期三表示4. 范围是 1~7.

而 weekday() 是星期一 表示0， 星期三表示 2. 范围是0~6.

推荐使用 dayofweek() 函数。

九.四 查询当前月的最后一天 last_day(date)

可以查询出，某个月的最后一天。

select last_day(now()),last_day('2019-02-03'),last_day('2000-02-04');

注意，只有 last_day(date) 函数，没有 first_day(date) 函数。 要查询当月的第一天，
可以 先查询出年，再查询出当前月，最后与 ‘01’ 进行拼接来表示。

九.五 提取日期 date(d1)

将日期 时间中的日期部分提取出来。

select date('1995-02-07 11:23:59'),date('1995-02-07');

九.六 提取时间 time(d1)

将日期 时间中的时间部分提取出来

select time('1995-02-07 11:23:59'),time('11:23:59');

九.七 改变 系统变量 lc_time_names

查询 monthname(), dayname() 时显示的是英文， 如果想让其显示中文呢, 要改变 lc_time_names的默认值。

默认是 en_US, 显示英文。

1 . 查询 变量 ‘lc_time_names’

show variables like 'lc_time_names';

2 .展示 monthname 和dayname

select monthname(now()),dayname(now());

3 . 改变变量 ‘lc_time_names’ 的值为 ‘zh_CN’ 中文。

set lc_time_names='zh_CN';

4 . 再次查询 monthname 和dayname

select monthname(now()),dayname(now());

发现，展示成相应的中文了。

5 . 不要忘记，再改回去噢

set lc_time_names='en_US';

十. 两个日期之间的比较函数

常见的有，一个日期，加上多少天，加上多少月，即往后，变成新的一天是什么。 一个日期，减去多少天，减去多少月，即往前，变成新的一天是什么。 也有比较一下，两个日期之间相差多少天。 也有时间，往前，往后的。

十.一 往后 添加参数 date_add(d1, interval n type) 和 addDate(d1,interval n type)

是往后，添加多少天，多少月的意思。 其中，date_add() 与addDate() 函数 是一样的,

只是 addDate() 可以不写类型，直接填充n, 变成 addDate(d1,n)，不写时，默认填充的是天 Day，也可以填写负值。

而 date_add(） 却不能省略，必须完整填写 (interval n type) ，且是正值。

推荐使用 date_add() 函数。

其中，后面的那个 type 的可取值范围 就是 extract() 函数中的 type 类型，即 六.一 中的那个表格。
但 用日期时， 一般用 Year,Month,Day 三个。

对于复杂的日期变换，可以进行嵌套 date_add（） 函数。

如查询 当前日期往后一天的， 当前日期往后两个月的， 当前日期往后一月还有三天的

select now() as '1',addDate(now(),1) as '2',date_add(now(),interval 1 day) as '3',
  date_add(now(),interval 2 month) as '4', date_add(date_add(now(),interval 1 month),interval 3 day) as '5';

当前是 11-27日， 添加1天是 11-28日， 添加两个月是 2020-01-27,

添加一个月零3天是， 12-30。

十.二 往前 减去参数 date_sub(d1,interval n type) 和subDate(d1,interval n type)

是往前 减去多少天，多少月的意思。 其中，date_sub() 与subDate() 函数是一样的。

只是 subDate() 可以不写类型，直接填充民， 变成 subDate(d1,n),不写时，默认填充的是天 Day，也可以填写负值。

而 date_sub() 却不能省略，必须完整填写 (interval n type)，且是正值。

推荐使用 date_sub() 函数。

对于复杂的日期变换，可以进行嵌套 date_sub() 函数。

如查询 当前日期往前一天的， 当前日期往前两个月的， 当前日期往前一月又三天的

select now() as '1',subDate(now(),1) as '2',date_sub(now(),interval 1 day) as '3',
  date_sub(now(),interval 2 month) as '4', date_sub(date_sub(now(),interval 1 month),interval 3 day) as '5';

当前是 11-27日， 减去1天是 11-26日， 减去两个月是 2019-09-27

减去一个月零3天是， 2019-10-24。

十.三 两个日期之间相差的整天数 DateDiff(d1,d2)

可以比较出两个日期相差的天数。 diff 为 Difference 的简写。

select DateDiff(now(),date_add(now(),interval 20 day)),DateDiff(now(),'2018-11-27');

如果d1>d2, 返回正数。 d1<d2, 返回负数。

十.四 时间相加的 addTime(t1,t2)

对时间的操作。 type 通常用 Hour,Minute, Second 三个类型。

select now(), addTime(time(now()),'0:01:20'),addTime(time(now()),'01:02:03');

当前是 17：08：29， 添加1分钟20秒为 17:09:49,

添加 1小时2分钟3秒，为 18：10：32 秒

十.五 时间相差 subTime(t1,t2)

select now(), subTime(time(now()),'0:01:20'),subTime(time(now()),'01:02:03');

当前是 17:10:52， 减去1分钟20秒为 17:09:32,

减去 1小时2分钟3秒，为 16:08:49

十一 日期和时间的格式化

类似于 java 中的 java.text.DateFormatter 类。

十一.一 格式化的类型

只列举业务开发中几个常见的说明符号。 注意，大小写是有区别的。

说明符	解释
%Y	4位数表示年份
%m	数字形式的月份
%d	该月日期
%H	2位数表示24小时
%i	数字形式表示分钟
%S	2位数表示秒
%f	微秒

十一. 二 将日期转换字符串 date_format(d,f)

将当前日期转换成中文的形式。

select now(),date_format(now(),'%Y%m%d %H%i%S'),date_format(now(),'%Y年%m月%d日 %H小时%i分钟%S秒');

十一.三 将时间转换成字符串 time_format(t,f)

将当前日期转换成中文的形式

select now(),time_format(now(),'%H%i%S'),time_format(now(),'%H小时%i分钟%S秒');

十一.四 将字符串转换成日期 str_to_date(str,f)

将日期形式的字符串按照其能匹配的格式转换成相应的日期。

select str_to_date('27/11/2019','%d/%m/%Y') as '1',str_to_date('173223','%H%i%S') as '2',
  str_to_date('2019年11月27日','%Y年%m月%d日') as '3',time(str_to_date('17时32分23秒','%H时%i分%S秒')) as '4',
  str_to_date('27/11/2019 173223','%d/%m/%Y %H%i%S') as '5',
  str_to_date('2019年11月27日17时32分23秒','%Y年%m月%d日%H时%i分%S秒') as '6';

单独时间格式化的话，显示为null. 所以，str_to_date() 时把日期加上。

谢谢!!!