MySQL内置函数-单行函数（日期函数）

日期函数

获取当前日期，当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-09 23:21:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-06-09 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 23:21:23  |
+-----------+
1 row in set (0.00 sec)

截取时间单位（年、月、日、时、分、秒）

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          23 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            24 |
+---------------+
1 row in set (0.00 sec)

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
|            32 |
+---------------+
1 row in set (0.00 sec)

案例：

mysql> select (year(now())-year('1998-01-01 00:01:01'));
+-------------------------------------------+
| (year(now())-year('1998-01-01 00:01:01')) |
+-------------------------------------------+
|                                        22 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select (year('1998-01-01 00:01:01'));
+-------------------------------+
| (year('1998-01-01 00:01:01')) |
+-------------------------------+
|                          1998 |
+-------------------------------+
1 row in set (0.00 sec)

获取当前时间戳函数 

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2020-06-11 11:37:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2020-06-11 11:37:47 |
+---------------------+
1 row in set (0.00 sec)

 

将字符串转换成日期

mysql> select str_to_date();

mysql> select str_to_date('1-1 2020','%m-%d %y');
+------------------------------------+
| str_to_date('1-1 2020','%m-%d %y') |
+------------------------------------+
| 2020-01-01                         |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

将时间、日期转换为字符串

date_format(date,format), time_format(time,format)

mysql> select date_format('2020-06-11 11:37:47','%d-%m-%y %H:%i:%S');
+--------------------------------------------------------+
| date_format('2020-06-11 11:37:47','%d-%m-%y %H:%i:%S') |
+--------------------------------------------------------+
| 11-06-20 11:37:47                                      |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select time_format('2020-06-11 11:37:47','%d-%m-%y %H:%i:%S');
+--------------------------------------------------------+
| time_format('2020-06-11 11:37:47','%d-%m-%y %H:%i:%S') |
+--------------------------------------------------------+
| 00-00-00 11:37:47                                      |
+--------------------------------------------------------+
1 row in set (0.00 sec)

转换天数、日期

to_days是从0年开始算的

mysql> select to_days('1970-01-01');
+-----------------------+
| to_days('1970-01-01') |
+-----------------------+
|                719528 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select to_days('0001-01-01');
+-----------------------+
| to_days('0001-01-01') |
+-----------------------+
|                   366 |
+-----------------------+
1 row in set (0.00 sec)

from_days是给出天数推算是从0年开始的哪个日期

mysql> select from_days(365);
+----------------+
| from_days(365) |
+----------------+
| 0000-00-00     |
+----------------+
1 row in set (0.00 sec)

mysql> select from_days(366);
+----------------+
| from_days(366) |
+----------------+
| 0001-01-01     |
+----------------+
1 row in set (0.00 sec)

mysql> select from_days(367);
+----------------+
| from_days(367) |
+----------------+
| 0001-01-02     |
+----------------+
1 row in set (0.00 sec)

返回unix时间戳 unix_timestamp

mysql> select unix_timestamp('1970-01-01');
+------------------------------+
| unix_timestamp('1970-01-01') |
+------------------------------+
|                        18000 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:01') |
+---------------------------------------+
|                                 18001 |
+---------------------------------------+
1 row in set (0.00 sec)

from_unixtime 从给定时间戳计算年月日

mysql> select from_unixtime(1);
+---------------------+
| from_unixtime(1)    |
+---------------------+
| 1969-12-31 19:00:01 |
+---------------------+
1 row in set (0.00 sec)

 

日期格式符

格式符	作用
%Y	4位年份，例如：1998
%y	2位年份，例如：98
%m	月份，例如： 01，02,...,12
%c	月份，例如： 1，2,...,12
%d	日期，例如01，02,..,31
%H	24小时制
%h	12小时制
%i	分钟，例如：00-59
%s	秒,例如：00-59

以指定的字符串格式显示日期 date_format

mysql> select date_format(now(),'%Y-%m-%d');
+-------------------------------+
| date_format(now(),'%Y-%m-%d') |
+-------------------------------+
| 2020-06-09                    |
+-------------------------------+
1 row in set (0.00 sec)

 time_format函数转换时间

mysql> select time_format('1996-04-20 11:07:45','%h %i %s');
+-----------------------------------------------+
| time_format('1996-04-20 11:07:45','%h %i %s') |
+-----------------------------------------------+
| 11 07 45                                      |
+-----------------------------------------------+
1 row in set (0.00 sec)

 时区转换函数convert_tz

convert_tz(dt,from_tz,to_tz)

mysql> select convert_tz('2008-08-08 12:00:00', '+09:00', '+01:00');
+-------------------------------------------------------+
| convert_tz('2008-08-08 12:00:00', '+09:00', '+01:00') |
+-------------------------------------------------------+
| 2008-08-08 04:00:00                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2008-08-08 12:00:00', '+09:00', '+0:10');
+------------------------------------------------------+
| convert_tz('2008-08-08 12:00:00', '+09:00', '+0:10') |
+------------------------------------------------------+
| 2008-08-08 03:10:00                                  |
+------------------------------------------------------+
1 row in set (0.00 sec)