MySQL日期常用公式

1.用来计算时间差的,单位从毫秒到年：

SELECT TIMESTAMPDIFF(MICROSECOND,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(SECOND,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(MINUTE,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(HOUR,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(DAY,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(WEEK,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(MONTH,NOW(),current_day_date) FROM fu_doctor_unfree_time;
SELECT TIMESTAMPDIFF(YEAR,NOW(),current_day_date) FROM fu_doctor_unfree_time;

2.格式化时间.

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') FROM XXX;

3.字符串转时间

SELECT STR_TO_DATE('2017-05-11 11:43:26','%Y-%m-%d %H:%i:%s') FROM xxx;

4.增加递增序号列

SELECT
  @y := @y + 1 AS num
FROM ${tableName}, (SELECT @y := 0) d;

 

转载于:https://my.oschina.net/vright/blog/881860