MySQL函數-日期和時間函數

時間函數對應mysql版本5.7,低版本未測

1、擷取目前日期的函數，傳回格式"YYYY-MM-DD"

mysql> select curdate(),current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2019-10-30 | 2019-10-30     |
+------------+----------------+
1 row in set (0.05 sec)
           

2、擷取目前日期的函數，傳回格式"YYYY-MM-DD HH:MM:SS"

mysql> select now(),sysdate(),current_timestamp(),localtime();
+---------------------+---------------------+---------------------+---------------------+
| now()               | sysdate()           | current_timestamp() | localtime()         |
+---------------------+---------------------+---------------------+---------------------+
| 2019-10-30 14:03:46 | 2019-10-30 14:03:46 | 2019-10-30 14:03:46 | 2019-10-30 14:03:46 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.11 sec)
           

3、UNIX時間戳函數

mysql> select unix_timestamp(),unix_timestamp(now()),now();
+------------------+-----------------------+---------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               |
+------------------+-----------------------+---------------------+
|       1572418048 |            1572418048 | 2019-10-30 14:47:28 |
+------------------+-----------------------+---------------------+
1 row in set (0.11 sec)
           

from_unixtime與unix_timestamp為互反函數

mysql> select from_unixtime(1572418048);
+---------------------------+
| from_unixtime(1572418048) |
+---------------------------+
| 2019-10-30 14:47:28       |
+---------------------------+
1 row in set (0.10 sec)
           

4、傳回UTC日期和時間的函數

mysql> select utc_date(),utc_time();
+------------+------------+
| utc_date() | utc_time() |
+------------+------------+
| 2019-10-30 | 06:56:44   |
+------------+------------+
1 row in set (0.09 sec)
           

5、擷取月份的函數

MONTH(date)函數傳回對應的月份，MONTHNAME(date)傳回月份對應的英文

mysql> select month('2019-10-30'),monthname('2019-10-30');
+---------------------+-------------------------+
| month('2019-10-30') | monthname('2019-10-30') |
+---------------------+-------------------------+
|                  10 | October                 |
+---------------------+-------------------------+
1 row in set (0.09 sec)
           

6、擷取星期的函數

dayname(d)函數傳回d對應的工作日的英文名稱

dayofweek(d)傳回日期所在周的索引

mysql> select dayname('2019-10-30'),dayofweek('2019-10-30');
+-----------------------+-------------------------+
| dayname('2019-10-30') | dayofweek('2019-10-30') |
+-----------------------+-------------------------+
| Wednesday             |                       4 |
+-----------------------+-------------------------+
1 row in set (0.10 sec)
           

7、擷取星期數的函數

week(d,default_week_format)預設值是0，指定一周的第一天為周日，1則指定一周的第一天為周一

weekofyear(d)某天位于一年中的第幾周，相當于week(d,3)

mysql> select week('2019-10-30'),weekofyear('2019-10-30');
+--------------------+--------------------------+
| week('2019-10-30') | weekofyear('2019-10-30') |
+--------------------+--------------------------+
|                 43 |                       44 |
+--------------------+--------------------------+
1 row in set (0.13 sec)

mysql> select week('2019-10-30',1),weekofyear('2019-10-30');
+----------------------+--------------------------+
| week('2019-10-30',1) | weekofyear('2019-10-30') |
+----------------------+--------------------------+
|                   44 |                       44 |
+----------------------+--------------------------+
1 row in set (0.09 sec)
           

8、擷取天數的函數

dayofyear(d) 一年中的第幾天

dayofmonth(d)一個月中的第幾天

mysql> select dayofmonth('2019-10-30'),dayofyear('2019-10-30');
+--------------------------+-------------------------+
| dayofmonth('2019-10-30') | dayofyear('2019-10-30') |
+--------------------------+-------------------------+
|                       30 |                     303 |
+--------------------------+-------------------------+
1 row in set (0.09 sec)
           

9、擷取年份、季度、小時、分鐘和秒鐘的函數

mysql> select year('2019-10-30 14:47:28'),quarter('2019-10-30 14:47:28'),minute('2019-10-30 14:47:28'),second('2019-10-30 14:47:28');
+-----------------------------+--------------------------------+-------------------------------+-------------------------------+
| year('2019-10-30 14:47:28') | quarter('2019-10-30 14:47:28') | minute('2019-10-30 14:47:28') | second('2019-10-30 14:47:28') |
+-----------------------------+--------------------------------+-------------------------------+-------------------------------+
|                        2019 |                              4 |                            47 |                            28 |
+-----------------------------+--------------------------------+-------------------------------+-------------------------------+
1 row in set (0.11 sec)
           

10、時間和秒鐘轉換的函數

time_to_sec(time)

sec_to_time(second)

mysql> select time_to_sec('14:47:28'),sec_to_time(53248);
+-------------------------+--------------------+
| time_to_sec('14:47:28') | sec_to_time(53248) |
+-------------------------+--------------------+
|                   53248 | 14:47:28           |
+-------------------------+--------------------+
1 row in set (0.10 sec)
           

11、計算日期和時間的函數

date_add(date,interval expr type)

adddate(date,interval expr type)

date_sub(date,interval expr type)

subdate(date,interval expr type)

addtime(date,expr)

subtime(date,expr)

datediff() 兩個日期的間隔天數

計算日期和時間的格式

type值	預期的expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOUR.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS:MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

 舉例：使用三個type值 day,second,year_month 其他類比

mysql> select date_sub('2019-10-30',interval 10 day),date_sub('2019-10-30 14:47:28',interval 10 second),date_add('2019-10-30 14:47:28',interval '1_1' year_month);
+----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+
| date_sub('2019-10-30',interval 10 day) | date_sub('2019-10-30 14:47:28',interval 10 second) | date_add('2019-10-30 14:47:28',interval '1_1' year_month) |
+----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+
| 2019-10-20                             | 2019-10-30 14:47:18                                | 2020-11-30 14:47:28                                       |
+----------------------------------------+----------------------------------------------------+-----------------------------------------------------------+
           

 addtime

mysql> select addtime('2019-10-30 14:47:28','1:1:1'),addtime('14:47:28','1:1:1');
+----------------------------------------+-----------------------------+
| addtime('2019-10-30 14:47:28','1:1:1') | addtime('14:47:28','1:1:1') |
+----------------------------------------+-----------------------------+
| 2019-10-30 15:48:29                    | 15:48:29                    |
+----------------------------------------+-----------------------------+
1 row in set (0.09 sec)
           

datediff

mysql> select datediff('2019-10-30 14:47:28','2019-09-23 10:27:18');
+-------------------------------------------------------+
| datediff('2019-10-30 14:47:28','2019-09-23 10:27:18') |
+-------------------------------------------------------+
|                                                    37 |
+-------------------------------------------------------+
1 row in set (0.10 sec)
           

12、将日期時間格式化的函數

date_format(date,format)

DATE_FORMAT時間日期格式

說明符	說明
%a	工作日的縮寫名稱（Sun...Sat）
%b	月份的縮寫名稱（Jan...Dec）
%c	月份、數字形式
%D	帶有英語字尾的該月日期
%d	該月日期，數字形式
%e	該月日期，數字形式
%f	微妙
%H	以2位數表示24小時
%h	以2位數表示12小時
%i	分鐘，數字形式
%j	一年中的天數
%k	以24小時表示時間
%l	以12小時表示時間
%M	月份名稱，英文
%m	月份名稱，數字形式
%p	上午AM 或者下午PM
%r	時間12小時制
%S,%s	以數字形式表示秒
%W	工作日名稱，中文
%w	一周的工作日，0表示周日
%Y	四位數表示年份
%y	兩位數表示年份
%%	‘%’文字字元

mysql> select date_format('2019-10-30 14:47:28','%a %b %c %d %M %m %Y %y');
+--------------------------------------------------------------+
| date_format('2019-10-30 14:47:28','%a %b %c %d %M %m %Y %y') |
+--------------------------------------------------------------+
| Wed Oct 10 30 October 10 2019 19                             |
+--------------------------------------------------------------+
1 row in set (0.08 sec)