6.8 mysql 内置函数-日期函数
6.8.1 当前日期 – curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2018-02-12 |
+------------+
1 row in set (0.00 sec)
6.8.2 当前时间 – curtime()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:41:57 |
+-----------+
1 row in set (0.00 sec)
6.8.3 当前日期和时间 – now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-02-12 22:42:23 |
+---------------------+
1 row in set (0.00 sec)
6.8.4 unix时间戳 – unix_timestamp()
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1518504183 |
+------------------+
1 row in set (0.00 sec)
6.8.5 unix的日期值 – from_unixtime(date)
mysql> select from_unixtime(1518504183);
+---------------------------+
| from_unixtime(1518504183) |
+---------------------------+
| 2018-02-12 22:43:03 |
+---------------------------+
1 row in set (0.00 sec)
6.8.6 一年中的第几周 – week(date)
mysql> select week('2018-02-13');
+--------------------+
| week('2018-02-13') |
+--------------------+
| 6 |
+--------------------+
1 row in set (0.00 sec)
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
6.8.7 日期的年份、月份、日期 – year(date)、month(date)、day(date)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-02-12 22:47:07 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2018 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 12 |
+------------+
1 row in set (0.00 sec)
6.8.8 间隔天数 – datediff(date1,date2)
mysql> select datediff('2018-02-13','2018-02-14');
+-------------------------------------+
| datediff('2018-02-13','2018-02-14') |
+-------------------------------------+
| -1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14','2018-02-13');
+-------------------------------------+
| datediff('2018-02-14','2018-02-13') |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-14 00:00:00');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-14 00:00:00') |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-13 23:59:59');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-13 23:59:59') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-13 00:00:00');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-13 00:00:00') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-13 02:00:00');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-13 02:00:00') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-13 12:00:00');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-13 12:00:00') |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2018-02-14 12:00:00','2018-02-12 12:00:00');
+-------------------------------------------------------+
| datediff('2018-02-14 12:00:00','2018-02-12 12:00:00') |
+-------------------------------------------------------+
| 2 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
结论:该函数只是计算间隔天数,不足一天也是间隔一天