MYSQL时间函数简单使用

最新推荐文章于 2023-07-28 13:32:33 发布

yangyinbo

最新推荐文章于 2023-07-28 13:32:33 发布

阅读量362

点赞数

文章标签： MYSQL时间函数简单使用

本文链接：https://blog.csdn.net/yangyinbo/article/details/77203408

版权

SQL三条，分别求每天的行程量，每周的，每月的

这一年内每个月的数据

 SELECT
 
 monthname(start_time) Month,
 sum( time_to_sec(  timediff(end_time, start_time) )) SecondsOfTime,
 sum( time_to_sec(  timediff(end_time, start_time) ) / 60) MinuteOfTime,
 sum(Mileage) Mileage
 FROM
 electrombile_trip_record
 where
    year(start_time) = year(now())
 group by  
 MONTHNAME(start_time)

这一季度每周的数据

SELECT
 
 weekofyear(start_time) Week,
 sum( time_to_sec(  timediff(end_time, start_time) )) SecondsOfTime,
 sum( time_to_sec(  timediff(end_time, start_time) ) / 60) MinuteOfTime,
 sum(Mileage) Mileage
 FROM
 electrombile_trip_record
 where
  
  quarter(start_time) = quarter(now())
 group by  
 weekofyear(start_time)

这一个月每天的数据

   SELECT
 date(start_time) DayDate,
 dayofmonth(start_time) Day,
    sum( time_to_sec(  timediff(end_time, start_time) )) SecondsOfTime,
    sum( time_to_sec(  timediff(end_time, start_time) ) / 60) MinuteOfTime,
    sum(Mileage) Mileage
    FROM
 electrombile_trip_record
 where
  
    month(start_time) = month(now())
    group by date(start_time),
         dayofmonth(start_time)

学习和参考：

http://blog.csdn.net/u011781769/article/details/48471039