MairDB日期(一)

  

第一部分 日期和时间函数

3.3 MySQL OF 函数：

1. NOW(): 当前日期+时间;

设 T=NOW();

----该天分别在周/月/年中的位置----

1. DAYOFWEEK(T);（1 = Sunday, 2 = Monday, ..., 7 = Saturday）

2. DAYOFMONTH(T);

3. DAYOFYEAR(T);

4. ----------------

5. WEEK(T);

6. WEEKOFYEAR(T); 等同于WEEK(T);

7. WEEKDAY(T); (0 = Monday, 1 = Tuesday, ..., 6 = Sunday)；

MariaDB [study_db]> SELECT NOW(), CURDATE(), CURTIME();

MariaDB[study_db]> SELECT DAYOFWEEK(NOW()), DAYOFMONTH(NOW()), DAYOFYEAR(NOW());

+------------------+-------------------+------------------+

| DAYOFWEEK(NOW()) | DAYOFMONTH(NOW()) | DAYOFYEAR(NOW()) |

+------------------+-------------------+------------------+

|                2 |                19 |              354 |

+------------------+-------------------+------------------+

1 row in set (0.00 sec)

 

MariaDB [study_db]> SELECT WEEK(NOW()), WEEKOFYEAR(NOW()), WEEKDAY(NOW()), YEARWEEK(NOW());

+-------------+-------------------+----------------+-----------------+

| WEEK(NOW()) | WEEKOFYEAR(NOW()) | WEEKDAY(NOW()) | YEARWEEK(NOW()) |

+-------------+-------------------+----------------+-----------------+

|          51 |                51 |              0 |          201651 |

+-------------+-------------------+----------------+-----------------+

1 row in set (0.00 sec)

3.4 计算函数

 

------加--------

1. DATE_ADD(T, interval 1 YEAR);

2. DATE_ADD(T, interval 1 SQUARTER);

4. --------减------

5. DATE_SUB(T, interval 1 YEAR);

6.

6. 加减的参数: MONTH/DAY/HOUT/SECOND/MINUTE

实例代码

MariaDB [study_db]> SELECT publication_date, DATE_ADD(publication_date, interval 1 year) FROM new_books;

+------------------+---------------------------------------------+

| publication_date | DATE_ADD(publication_date, interval 1 year) |

+------------------+---------------------------------------------+

| 2016-12-03       | 2017-12-03                                  |

| 2016-12-02       | 2017-12-02                                  |

 

3.5 MySQL 日期、时间相减函数

 

1. DATEDIFF(date1,date2)：两个日期相减date1 date2，返回天数。

2. TIMEDIFF(time1,time2): 两个日期相减time1 time2，返回time 差值。

实例

 

MariaDB [study_db]> SELECT DATEDIFF(DATE(NOW()), '2016-12-31') AS '天数差',

    -> TIMEDIFF(TIME(NOW()), '24:24:00') AS '时间差';

 

+--------+-----------+

| 天数差  | 时间差      |

+--------+-----------+

|    -12 | -09:08:10 |

+--------+-----------+

1 row in set (0.00 sec)