MYSQL常用日期和时间函数

时间:2022-09-22 16:55:27

“今夜偏知春气暖,虫声新透绿窗纱”
有时我们可能会遇到这样的需求:当前时间是多少、下个月的今天是星期几、统计截止到当前日期前 3 天的收入总和等。这些需求就需要日期和时间函数来实现。
CURDATE()函数:返回当前日期,只包含年月日。

mysql> select CURDATE();
+------------+

| CURDATE() |
+------------+

| 2007-07-11 |
+------------+

1 row in set (0.03 sec)

CURTIME()函数:返回当前时间,只包含时分秒。

mysql> select CURTIME();
+-----------+

| CURTIME() |
+-----------+

| 14:13:46 |
+-----------+

1 row in set (0.00 sec)

NOW()函数:返回当前的日期和时间,年月日时分秒全都包含。

mysql> select NOW();
+---------------------+

| NOW() |
+---------------------+

| 2007-07-11 14:14:06 |
+---------------------+

1 row in set (0.00 sec)

UNIX_TIMESTAMP(date)函数:返回日期 date 的 UNIX 时间戳。

mysql> select UNIX_TIMESTAMP(now());
+-----------------------+

| UNIX_TIMESTAMP(now()) |
+-----------------------+

| 1184134516 |
+-----------------------+

1 row in set (0.02 sec)

FROM_UNIXTIME ( unixtime ) 函 数 : 返 回 UNIXTIME 时 间 戳 的 日 期 值 , 和UNIX_TIMESTAMP(date)互为逆操作。

mysql> select FROM_UNIXTIME(1184134516) ;
+---------------------------+

| FROM_UNIXTIME(1184134516) |
+---------------------------+

| 2007-07-11 14:15:16 |
+---------------------------+

1 row in set (0.00 sec)

WEEK(DATE)和 YEAR(DATE)函数:前者返回所给的日期是一年中的第几周,后者返回所给的日期是哪一年。

mysql> select WEEK(now()),YEAR(now());
+-------------+-------------+

| WEEK(now()) | YEAR(now()) |
+-------------+-------------+

| 27 | 2007 |
+-------------+-------------+

1 row in set (0.02 sec)

HOUR(time)和 MINUTE(time)函数:前者返回所给时间的小时,后者返回所给时间的分钟。

mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+

| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+

| 14 | 18 |
+-----------------+-------------------+

1 row in set (0.00 sec)

MONTHNAME(date)函数:返回 date 的英文月份名称。

mysql> select MONTHNAME(now());
+------------------+

| MONTHNAME(now()) |
+------------------+

| July |
+------------------+

1 row in set (0.00 sec)

DATE_FORMAT(date,fmt)函数:按字符串 fmt 格式化日期 date 值,此函数能够按指定的格式显示日期,

格式符  格式说明
%S,%s 两位数字形式的秒(00,01,...,59
%i 两位数字形式的分(00,01,...,59
%H 两位数字形式的小时,24 小时(00,01,...,23
%h,%I 两位数字形式的小时,12 小时(01,02,...,12
%k 数字形式的小时,24 小时(0,1,...,23
%l 数字形式的小时,12 小时(1,2,...,12
%T 24 小时的时间形式(hh:mm:ss)
%r 12 小时的时间形式(hh:mm:ssAM 或 hh:mm:ssPM)
%p AM 或 PM
%W 一周中每一天的名称(Sunday,Monday,...,Saturday)
%a 一周中每一天名称的缩写(Sun,Mon,...,Sat)
%d 两位数字表示月中的天数(00,01,...,31
%e 数字形式表示月中的天数(1,2...,31
%D 英文后缀表示月中的天数(1st,2nd,3rd,...
%w 以数字形式表示周中的天数 (0=Sunday,1=Monday,...,6=Saturday)
%j 以 3 位数字表示年中的天数(001,002,...,366
%U 周(0,1,52),其中 Sunday 为周中的第一天
%u 周(0,1,52),其中 Monday 为周中的第一天
%M 月名(January,February,...,December)
%b 缩写的月名(January,February,...,December)
%m 两位数字表示的月份(01,02,...,12
%c 数字表示的月份(1,2,...,12
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”

下面的例子将当前时间显示为“月,日,年”格式:

mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+

| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+

| July,11th,2007 |
+-------------------------------+

1 row in set (0.00 sec)

DATE_ADD(date,INTERVAL expr type)函数:返回与所给日期 date 相差 INTERVAL 时间段的日期。其中 INTERVAL 是间隔类型关键字,expr 是一个表达式,这个表达式对应后面的类型,type是间隔类型,MySQL 提供了 13 种间隔类型

表达式类型   描述      格式
HOUR 小时 hh
MINUTE 分 mm
SECOND 秒 ss
YEAR 年 YY
MONTH 月 MM
DAY 日 DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_ SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss

来看一个具体的例子,在这个例子中第 1 列返回了当前日期时间,第 2 列返回距离当前日期31 天后的日期时间,第 3 列返回距离当前日期一年两个月后的日期时间。

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
+---------------------+---------------------+------------------------+
1 row in set (0.01 sec)

同样也可以用负数让它返回之前的某个日期时间,如下第 1 列返回了当前日期时间,第 2列返回距离当前日期 31 天前的日期时间,第 3 列返回距离当前日期一年两个月前的日期时间。

mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+

| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+

| 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 |
+---------------------+---------------------+------------------------+

1 row in set (0.00 sec)

DATEDIFF(date1,date2)函数:用来计算两个日期之间相差的天数。
下面的例子计算出当前距离 2008 年 8 月 8 日的奥运会开幕式还有多少天:

mysql> select DATEDIFF('2008-08-08',now());
+------------------------------+

| DATEDIFF('2008-08-08',now()) |
+------------------------------+

| 328 |
+------------------------------+

1 row in set (0.01 sec)