本文共 2809 字,大约阅读时间需要 9 分钟。
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相差多长时间。
使用函数unix_timestamp()
如下:
select unix_timestamp("2016-08-03 04:00:00") as time;+------------+| time |+------------+| 1470168000 |+------------+1 row in set (0.00 sec)
使用函数 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_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_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() 得到两个日期的时间差我们知道,可以按照某个字段的升序或者降序进行排序,但是,如何使某一字段按照自己自定义的规则进行排序呢?
使用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千万不要写引号,这是字段,应该用命令执行符`
待续…
转载地址:http://uvvws.baihongyu.com/