日期函数SQL

1.日期、时间、时间戳的区别
日期： 年-月-日 如：‘1980-12-08’ 范围从0001-1-1至9999-12-31

时间： 小时数:分钟数:秒数 如：‘23:59:59’

时间戳： 年-月-日 小时数:分钟数:秒数（.微妙数） 如： ‘1980-12-08 23:59:59.59’

2.时间戳和日期相互转换
(1)把日期转换为时间戳

mysql> SELECT UNIX_TIMESTAMP('1993-09-30');
+------------------------------+
| UNIX_TIMESTAMP('1993-09-30') |
+------------------------------+
|                    749318400 |
+------------------------------+
1 row in set (0.01 sec)

（2）把时间戳转换为日期

FROM_UNIXTIME

数据表中 invest_time 存储的是时间戳，如 1429063399

mysql> SELECT FROM_UNIXTIME(749318400,'%Y年%m月%d日');
+-----------------------------------------+
| FROM_UNIXTIME(749318400,'%Y年%m月%d日') |
+-----------------------------------------+
| 1993年09月30日                          |
+-----------------------------------------+
1 row in set (0.00 sec)

3.DATEDIFF和TIMESTAMPDIFF的区别
DATEDIFF是表示两个日期之间的天数差异，即使你输入带有时间的日期，也只会计算天数。
TIMESTAMPDIFF表示时间戳之间的差异。

（1）DATEDIFF
定义：返回两个日期之间的天数
语法：

DATEDIFF(DATA1,DATA2)

假设credit表中有贷款或者还款的startdate 和 enddate，那么我们可以计算贷款时间在90天以上的

SELECT * FROM credit 
WHERE DATEDIFF(enddate,startdate) =90；

计算两个日期之间的天数，也可以不加AS NUM

mysql> SELECT DATEDIFF('2020-09-30','1993-03-30') AS NUM;
+-------+
| NUM   |
+-------+
| 10046 |
+-------+
1 row in set (0.00 sec)

测试DATEDIFF

mysql> SELECT DATEDIFF('2020-09-30 08:00:00','1993-03-30') AS NUM;
+-------+
| NUM   |
+-------+
| 10046 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2020-09-30 08:00:00','1993-03-30 01:00:00') AS NUM;
+-------+
| NUM   |
+-------+
| 10046 |
+-------+
1 row in set (0.00 sec)

mysql>

可见不管是否添加时间，都只返回天数之差。

(2)TIMESTAMPDIFF
定义：计算两个日期相差的秒数、分钟数、小时数、天数、周数、季度数、月数、年数，当前日期增加或者减少一天、一周等等。

语法：

SELECT TIMESTAMP(类型，开始时间，结束时间)

相差的秒数

mysql> SELECT TIMESTAMPDIFF(SECOND,'1993-09-30 OO:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') );
+-------------------------------------------------------------------------------------+
| TIMESTAMPDIFF(SECOND,'1993-09-30 OO:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') ) |
+-------------------------------------------------------------------------------------+
|                                                                           877366335 |
+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

相差的分钟数

mysql> SELECT TIMESTAMPDIFF(MINUTE,'1993-09-30 OO:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') );
+-------------------------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'1993-09-30 OO:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') ) |
+-------------------------------------------------------------------------------------+
|                                                                            14622773 |
+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

FRAC_SECOND 毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年

（3）其他常用时间函数
当前日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-07-19 17:28:34 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-07-19 |
+------------+
1 row in set (0.00 sec)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 17:30:19  |
+-----------+
1 row in set (0.01 sec)

DATE_SUB(date,INTERVAL expr type)
从日期减去指定的时间间隔

mysql> select date_sub(now(),interval 1 day);
+--------------------------------+
| date_sub(now(),interval 1 day) |
+--------------------------------+
| 2021-07-18 17:55:44            |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub(curdate(),interval 1 day);
+------------------------------------+
| date_sub(curdate(),interval 1 day) |
+------------------------------------+
| 2021-07-18                         |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub(curtime(),interval 1 day);
+------------------------------------+
| date_sub(curtime(),interval 1 day) |
+------------------------------------+
| -06:03:17                          |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

从日期增加指定的时间间隔，就把1改为-1.
FRAC_SECOND 毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年

extract()函数：
定义和用法
EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法
EXTRACT(unit FROM date)

mysql> select extract(year from '1993-09-30 12:30:20') as year,
    -> extract(month from '1993-09-30 12:30:20') as month,
    -> extract(day from '1993-09-30 12:30:20') as day,
    -> extract(hour from '1993-09-30 12:30:20') as hour,
    -> extract(minute from '1993-09-30 12:30:20') as minute;
+------+-------+------+------+--------+
| year | month | day  | hour | minute |
+------+-------+------+------+--------+
| 1993 |     9 |   30 |   12 |     30 |
+------+-------+------+------+--------+
1 row in set (0.00 sec)

date_add()函数，和上面的date_sub()用法一样

定义和用法
DATE_ADD() 函数向日期添加指定的时间间隔。

语法
DATE_ADD(date,INTERVAL expr type)

MySQL DATE_FORMAT() 函数
SQL Dates MySQL Date 函数
定义和用法
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法
DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。