mysql知识积累

时间日期相关函数

计算日期之差 timestampdiff(int,datetime,datetime)

mysql> select timestampdiff(day,'2016-03-08','2016-03-16') as diff;
+------+
| diff |
+------+
|    8 |
+------+
1 row in set (0.00 sec)

mysql> select timestampdiff(minute,'2016-03-08 13:05:00','2016-03-08 13:08:00'
+------+
| diff |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

timestampdiff(int,datetime,datetime)这个函数很高用，可以计算两个日期差。

int可取的类型有：
FRAC_SECOND — 时间间隔是毫秒
SECOND — 秒
MINUTE — 分
HOUR — 小时
DAY — 天
WEEK — 星期
MONTH — 月
QUARTER — 季度
YEAR — 年

相应的

datetime类型的数据，既可以是date类型的，也可以是datetime类型的。

计算两个日期相差的天数

datediff()函数

mysql> select datediff('2016-04-14',now());
+------------------------------+
| datediff('2016-04-14',now()) |
+------------------------------+
|                            2 |
+------------------------------+
1 row in set (0.00 sec)

得出now()和2016-04-14相差多长时间。

datetime转为timestamp

使用函数unix_timestamp()

如下：

select unix_timestamp("2016-08-03 04:00:00") as time;
+------------+
| time       |
+------------+
| 1470168000 |
+------------+
1 row in set (0.00 sec)

timestamp转为date等

使用函数 from_unixtime()

mysql> select from_unixtime(1344887103);
+---------------------------+
| from_unixtime(1344887103) |
+---------------------------+
| 2012-08-14 03:45:03       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1344887103,"%Y-%m-%d") as date;
+------------+
| date       |
+------------+
| 2012-08-14 |
+------------+
1 row in set (0.00 sec)

对date的格式化

使用date_format()函数

mysql> select date_format(now(),"%Y-%m-%d");
+-------------------------------+
| date_format(now(),"%Y-%m-%d") |
+-------------------------------+
| 2016-04-12                    |
+-------------------------------+
1 row in set (0.00 sec)

返回与date相差时间的日期

date_add()函数

mysql> SELECT now() as current,date_add(now(),INTERVAL 31 day) as after31days;
+---------------------+---------------------+
| current             | after31days         |
+---------------------+---------------------+
| 2016-04-12 11:16:20 | 2016-05-13 11:16:20 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT now() as current,date_add(now(),INTERVAL "1_2" year_month) as after1year2month ;
+---------------------+---------------------+
| current             | after1year2month    |
+---------------------+---------------------+
| 2016-04-12 11:17:59 | 2017-06-12 11:17:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

summit:
unix_timestamp() 转换为时间戳
from_unixtime() 转换为日期
timestamp_diff() 得到两个日期的时间差

排序相关知识点

1、自定义规则排序

我们知道，可以按照某个字段的升序或者降序进行排序，但是，如何使某一字段按照自己自定义的规则进行排序呢？
使用FEILD()函数

mysql> select * from testorder order by FIELD(`stage`,1,3,4,5,7,9);
+----+-------+
| id | stage |
+----+-------+
|  1 |     1 |
|  2 |     3 |
|  5 |     4 |
|  4 |     5 |
|  6 |     7 |
|  3 |     9 |
+----+-------+
6 rows in set (0.00 sec)

Attention：stage千万不要写引号，这是字段，应该用命令执行符`

待续…