MySql惯用(二、日期)函数

MySql常用(二、日期)函数
5.3日期和时间函数
//注：visit-time不可用单引号或双引号等限定。select DATE_FORMAT(visit_time, '%Y-%m-%d') from t_visit_result;

MySql中的日期间隔类型DATE_ADD(date, INTERVAL expr type)
SELECT visit_time, TO_DAYS(UTC_DATE()) - TO_DAYS(visit_time) AS '间隔' FROM t_visit_resultWHERE is_delete = '0' order by visit_result_id limit 0, 5;SELECT visit_time, TIMESTAMPDIFF(DAY, DATE_FORMAT(visit_time, '%Y-%m-%d'), UTC_DATE()) AS '间隔' FROM t_visit_result WHERE is_delete = '0' order by visit_result_id limit 0, 5;SELECT visit_time, TIMESTAMPDIFF(WEEK, DATE_FORMAT(visit_time, '%Y-%m-%d'), UTC_DATE()) AS '间隔' FROM t_visit_result WHERE is_delete = '0' order by visit_result_id limit 0, 5;## visit_time : 2012-05-03 10:11:24## UTC_DATE : 2012-06-05## SQL 结果：##visit_time|间隔##2012-05-03 10:11:24 |4## 说明：##2012-05-03为周四2012-05-06为周日##2012-05-13为周日##2012-05-20为周日##2012-05-27为周日##2012-06-03为周日2012-06-05为周二##SELECT visit_time, TIMESTAMPADD(MONTH, 2, visit_time) as 'NewVisitTime' FROM t_visit_resultWHERE is_delete = '0' order by visit_result_id limit 0, 5;##TIMESTAMPADD(WEEK, 2, visit_time)2012-05-03 10:11:242012-05-17 10:11:24##TIMESTAMPADD(MONTH, 2, visit_time)2012-05-03 10:11:242012-07-03 10:11:24##TIMESTAMPADD(MONTH, -2, visit_time)2012-05-03 10:11:242012-03-03 10:11:24